I have a need to see a trend of data for "month to date" So I want to pull the 1st x number of days of each month for the last 14 months, and the n aggregate this.
My data has approximately 10k data points each day
So far I have only been able to figure out how to do this by writing a double while loop - the outer loop counting down the months and the inner loop selecting each day and aggregating the data for the day - then storing it in a temp table.
Once I have stepped through each month and each day I then selet the data from the temp table and aggregate this to give me monthly data summed for each contract type.
For various reasons I need to run my select 4 times for each day.
This all ends up meaning my SQL takes around 120 secs to run. This is sub-optimal - as I am hoping to have this used by SSRS to pull a report on demand when a user wants to see it. Making them wait 2 whole minutes? Not Desirable - especially considering primary target audience is the exec team
Here is my SQL (NB I've changed a couple of table/ column names )
Declare @ReportDate as DATE ,@month as DATE ,@dayCounter as INT ,@monthCounter as INT ,@reportDateCurrentMonth as DATE SET @ReportDate = sysdatetime() SET @month = @ReportDate SET @dayCounter = DATEPART(DAY,@ReportDate) SET @monthCounter = '0' SET @reportDateCurrentMonth = DATEADD(month,@monthCounter,@ReportDate) CREATE TABLE #DailyVolumes ( contract_name varchar(50) ,Volume INT ,date_registered DATETIME ) WHILE @month > DATEADD(month,-15,@ReportDate) BEGIN WHILE @dayCounter > '0' BEGIN INSERT INTO #DailyVolumes SELECT CONCAT('PREFIX-',DWRccn.contract_name) AS contract_name ,count(distinct(CONCAT(rtrim(p.xxxx_id), '-', rtrim(r.lis_req_id)))) AS Volume ,a.date_registered FROM accession a Left join value1 P on a.value1_id = p.value1_id left join [DataWarehouseReporting].[dbo].[DIM_contract_code_name] DWRccn on a.contract_code = DWRccn.contract_code where [date_registered] = @reportDateCurrentMonth AND a.lis_code = 'S' AND visit_type IN ('I', 'E') GROUP BY CONCAT('PREFIX-',DWRccn.contract_name) ,a.date_registered -- Set new values on Daily counter & Date to grab SET @dayCounter = @dayCounter - '1' SET @reportDateCurrentMonth = DATEADD(day,-1,@reportDateCurrentMonth) END; SET @monthCounter = @monthCounter - '1' SET @month = DATEADD(month,-1,@month) SET @reportDateCurrentMonth = DATEADD(month,@monthCounter,@ReportDate) SET @dayCounter = DATEPART(DAY,@ReportDate) END; -- SUM Daily Data into Monthly Slices Select sum(Volume) AS Volume ,contract_name ,DATEADD(MONTH, DATEDIFF(MONTH, 0, date_registered), 0) AS MonthRegistered FROM #DailyVolumes group by contract_name ,DATEADD(MONTH, DATEDIFF(MONTH, 0, date_registered), 0) ORDER BY MonthRegistered DESC ,contract_name -- Clear Temp Table DROP TABLE #DailyVolumes
I am hoping someone can tell me how to accomlish what I am after... Which is to do away with the loops and have the data aggregate in a single operation
NB I have done my aggregation for full month periods - that was a piece of cake compared to this