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
✓ Extra quality
ExtraProxies brings the best proxy quality for you with our private and reliable proxies
✓ Extra anonymity
Top level of anonymity and 100% safe proxies – this is what you get with every proxy package
✓ Extra speed
1,ooo mb/s proxy servers speed – we are way better than others – just enjoy our proxies!
USA proxy location
We offer premium quality USA private proxies – the most essential proxies you can ever want from USA
Our proxies have TOP level of anonymity + Elite quality, so you are always safe and secure with your proxies
Use your proxies as much as you want – we have no limits for data transfer and bandwidth, unlimited usage!
Superb fast proxy servers with 1,000 mb/s speed – sit back and enjoy your lightning fast private proxies!
99,9% servers uptime
Alive and working proxies all the time – we are taking care of our servers so you can use them without any problems
No usage restrictions
You have freedom to use your proxies with every software, browser or website you want without restrictions
Perfect for SEO
We are 100% friendly with all SEO tasks as well as internet marketing – feel the power with our proxies
Buy more proxies and get better price – we offer various proxy packages with great deals and discounts
We are working 24/7 to bring the best proxy experience for you – we are glad to help and assist you!