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 )

    @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)

          WHILE @dayCounter > '0'


                INSERT INTO #DailyVolumes
                CONCAT('PREFIX-',DWRccn.contract_name) AS contract_name
                    ,count(distinct(CONCAT(rtrim(p.xxxx_id), '-', rtrim(r.lis_req_id)))) AS Volume
                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
                    [date_registered] = @reportDateCurrentMonth
                    AND a.lis_code = 'S'
                    AND visit_type IN ('I', 'E')
                GROUP BY 

                -- Set new values on Daily counter & Date to grab
                SET @dayCounter = @dayCounter - '1'
                SET @reportDateCurrentMonth = DATEADD(day,-1,@reportDateCurrentMonth)

       SET @monthCounter = @monthCounter - '1'
       SET @month = DATEADD(month,-1,@month)
       SET @reportDateCurrentMonth = DATEADD(month,@monthCounter,@ReportDate)
       SET @dayCounter = DATEPART(DAY,@ReportDate)


-- SUM Daily Data into Monthly Slices
    sum(Volume) AS Volume
    ,DATEADD(MONTH, DATEDIFF(MONTH, 0, date_registered), 0) AS MonthRegistered
FROM #DailyVolumes
group by 
    ,DATEADD(MONTH, DATEDIFF(MONTH, 0, date_registered), 0)
    MonthRegistered DESC 
-- 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

1 Answer 1


NOTE: I'm assuming you're running SQL Server 2012 (or higher), otherwise the datefromparts() code will need to be modified.

I'd opt for getting rid of the loops in favor of a single query that includes a date generator, which is relatively easy to do with a common table expression (CTE), eg:

declare @days   tinyint,         -- first X days of a month to query
        @months smallint,        -- go back Y months for our query
        @today  date

select  @days   = 5              -- only interested in days 1-5 of each month
        @months = 4              -- only interested in this month plus the previous 4 months
        @today  = sysdatetime();

dategen as
(select -- piece our years/months/days together into dates
        datefromparts ( year(dateadd(m,-m.number,@today)),  -- get year  for @today minus m.number of months
                       month(dateadd(m,-m.number,@today)),  -- get month for @today minus m.number of months
                       d.number) as search_date             -- day of month

 from   master..spt_values m,
        master..spt_values d

 -- no join clause => cartesian product (or you could add a cross join, your call)
 where  m.type = 'P'
 and    m.number between 0 and @months
 and    d.type = 'P'
 and    d.number between 1 and @days)

select  search_date
from    dategen
where   search_date <= @today
order by 1

 | search_date         |  @today = 18 November 2017
 | ------------------- |
 | 01/07/2017 00:00:00 |  first 5 days of July 2017
 | 02/07/2017 00:00:00 |
 | 03/07/2017 00:00:00 |
 | 04/07/2017 00:00:00 |
 | 05/07/2017 00:00:00 |

 | 01/08/2017 00:00:00 |  first 5 days of August 2017
 | 02/08/2017 00:00:00 |
 | 03/08/2017 00:00:00 |
 | 04/08/2017 00:00:00 |
 | 05/08/2017 00:00:00 |

 | 01/09/2017 00:00:00 |  first 5 days of September 2017
 | 02/09/2017 00:00:00 |
 | 03/09/2017 00:00:00 |
 | 04/09/2017 00:00:00 |
 | 05/09/2017 00:00:00 |

 | 01/10/2017 00:00:00 |  first 5 days of October 2017
 | 02/10/2017 00:00:00 |
 | 03/10/2017 00:00:00 |
 | 04/10/2017 00:00:00 |
 | 05/10/2017 00:00:00 |

 | 01/11/2017 00:00:00 |  first 5 days of November 2017
 | 02/11/2017 00:00:00 |
 | 03/11/2017 00:00:00 |
 | 04/11/2017 00:00:00 |
 | 05/11/2017 00:00:00 |

Here's a dbfiddle for the (above) CTE example.

Plugging the CTE into your insert/select would generate something like:

-- declare/select @variables;

dategen as
(select datefromparts( year(dateadd(m,-m.number,@today)), 
                      d.number) as search_date

 from   master..spt_values m,
        master..spt_values d

 where  m.type = 'P'
 and    m.number between 0 and @months
 and    d.type = 'P'
 and    d.number between 1 and @days)

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

FROM   accession a

join   dategen dg
on     a.date_registered = dg.search_date

join   value1 P
on     a.value1_id = p.value1_id

join   [DataWarehouseReporting].[dbo].[DIM_contract_code_name] DWRccn 
on     a.contract_code = DWRccn.contract_code

AND    a.lis_code = 'S'
AND    visit_type IN ('I', 'E')
AND    dg.search_date <= @today

GROUP BY CONCAT('PREFIX-',DWRccn.contract_name)


  • rename local variables as you see fit
  • set the @months/@days values to your desired limits
  • I haven't tested the insert/select (don't have the DDL for your system) so there may be some minor syntax issues ... ??
  • if performance is still not quite up to what you're expecting then it'll be necessary to review the query plan (and potentially the available indexes)

Assuming the (above) CTE/insert/select query generates the desired results, the next step would be to eliminate #DailyVolumes and add the final select/sum(Volume) code to the above query ... easiest to understand would probably be something like:


dategen as
(select ...),

dailyvolumes as
(select ... from accession/dategen/value1/DWRcnn ...)

select sum(Volume) ...
from   dailyvolumes ...
  • I had to wait till I got to work to try this. I was so excited I could hardly wait! I got to work I spent a bit of time - and I got it working. And it was COOL how it worked - and didn't require a LOOP! I don't want to seem ungrateful at all - but What I did NOT Expect was that in the end it ran significantly slower than the exact same result using my original double while loop! I just thought it would be useful to report back how things worked out.
    – kiltannen
    Commented Nov 20, 2017 at 1:06
  • Because I am doing this in a Data Warehouse environment - My current thinking is I will build up a Temp table each day after the ETL which will give me the daily data that I can them summarise "on the fly". Maybe with a bit of slicing and dicing at that point. I am currently doing some testing to see if this will give reasonable performance gains...
    – kiltannen
    Commented Nov 20, 2017 at 1:08
  • What kind of P&T analysis have you done on the various queries? Before adding more complexity to your warehouse it may be worth seeing what, if any, kind of tuning can be done on the queries; if you're not sure about query tuning and/or don't have anyone local that can help, consider opening a new question ... but be ready to provide table/column details (in particular datatypes), index details, actual queries and their query plans; sure, perhaps the volumes of data are just too much, but maybe all you need is to tweak a query and/or index eh
    – markp-fuso
    Commented Nov 20, 2017 at 2:41
  • Before answering your question about P&T - I am reporting I took a different tack. By using your CTE coaching - but in a different spot I got the load time down to an acceptable timeframe of about 15secs. What I did was to select into a temp table ALL daily data aggregated for the entire date range I was after - which gave me a set of totals for every day from my start date of query (I chose 14 months to give good timeseries trending). I then used the CTE selecting which dates I wanted data from, and aggregated just those dates - giving me the "MonthToDate" trending I was after!
    – kiltannen
    Commented Nov 20, 2017 at 3:04
  • P&T, Assuming this means Performance & Tuning - I ran the queries in SSMS (BTW I am running SQL 2012 so the datefromparts function was all good). I then observed how many seconds each query took to return. Or in one case it took 22 minutes! gasp
    – kiltannen
    Commented Nov 20, 2017 at 3:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.