I am trying to write a stored procedure that only returns data for the past 3, 6, 9 months or year. It needs to be full months only (if pulling 8/15; most recent would be July)
So far I have been able to find out how to find out the 1st and last day of the previous month.
SELECT DATEADD(m,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()), 0))as 'First day of the month',
DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) as 'Last day of the month'
The field that will need to be checked for is TransactionDateKey (ie 20161222). I think I should use a CAST on that field to only check for the month and day (ie 201612) but I could be wrong. Below is a rough mock-up of what I think I should be basing my query on but without the hard-coded dates
select FirstName + ' ' + LastName as 'Specialist',
empID as 'empID',
count(fact.spKey) as 'Count',
CAST(LEFT(CAST(TransactionDateKey AS VARCHAR(100)), 6) AS INT) as 'Month'
from Final.DimSpecialist s
inner join Final.FactTreatmentDay fact
on fact.spKey = s.spKey
where TransactionDateKey between 20161201 and 20161231
group by FirstName + ' ' + LastName,
empID,
CAST(LEFT(CAST(TransactionDateKey AS VARCHAR(100)), 6) AS INT)
How would I go about declaring a single parameter, @MonthRange, and using it in the WHERE clause to only return data for the past 3 months, 6 months, 9 months or a year? I think the parameter will need to be placed where -1 is in 'First day of the month'. I think there should also be a case statement to check for the each of the 4 possible parameter values, but I am not sure where it should go. Any advice would be much appreciated.
I believe there are some interesting ways to date calculations using a date dimension table where you have a yearmo field, I have chosen to provide an answer that closely matches what you already have an is applicable if you don't have such a table :-)
declare @months_to_report int; --procedure parameter
--Validation
if @months_to_report not in (3, 6, 9, 12) raiserror('Months to report must be one of 3, 6, 9, or 12.', 18, 1);
--Variables
declare @first date, @start date, @end date, @start_key int, @end_key int;
set @first = DATEADD(m,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()), 0)); --first day of previous month
set @end = DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)); --last day of previous month
set @start = DATEADD(M, 1 - @months_to_report, @first); --move back n-1 months to account for including previous month
set @start_key = Year(@start) * 10000 + Month(@start) * 100 + Day(@start); --start date as keys
set @end_key = Year(@end) * 10000 + Month(@end) * 100 + Day(@end); --end date as keys
select @first as 'First day of the month', @start as 'First day of reporting period', @end as 'Last day of the month/reporting period', @start_key as 'Start key', @end_key as 'End key';
This code assumes that the parameter being passed is @months_to_report. I prefer to validate parameters in stored procedures. The end select is only for debug purposes. You would modify your provided example code to follow this and supplement @start_key and @end_key for your hard-coded dates.