Introduction

Another tip for newcomers to SQL. Everybody knows how easy it is to group by month of the year, but what if you get a request for totals by 28 or 30 day periods starting January 1^st? There is no handy DATEPART() parameter that can produce these for you. But don’t worry. There is a way that is just as easy.

The examples used will come from a table called #TransHistory with the following schema

CREATE TABLE #TransHistory 
    (TransID INT IDENTITY(1,1) PRIMARY KEY
   , TransDate DATE
   , TransAmt INT)

The attached script in the Resources section was used to populate the table, if you want to experiment after you finish reading. But be aware that the script populates the table with random dates and amounts, so your totals will be different from the examples here.

Classic Totals By Month

The simple way to calculate totals by month is to use DATEPART(MONTH) to render each TransDate into a TransMonth (1-12) and then group by TransMonth.

The example below uses WITH ROLLUP to create a grand totals row which appears at the top of the result set. The grand totals row will be compared to the results from the user-defined technique to ensure that all dates and amounts are accounted for.

  --- Totals by Month
  SELECT DATEPART(MONTH,TransDate) as TransMonth
         , COUNT(*) as Transactions
         , SUM(TransAmt) as TotalAmt
         , MIN(TransDate) as PeriodStart
         , MAX(TransDate) as PeriodEnd
         , DATEDIFF(DAY, MIN(TransDate), MAX(TransDate))+1 as DaysInPeriod
  FROM #TransHistory
  GROUP BY DATEPART(MONTH,TransDate) WITH ROLLUP
  ORDER BY TransMonth

Nothing extraordinary here. For each TransMonth, the query produces transactions per month, total amounts, first and last dates in the period, and number of days in the period.

Totals by User-Defined Period

The simple technique to separate the year into 28 day periods is based on the characteristics of integer division in SQL. When one integer is divided by another integer, the result is itself an integer. Truncation occurs, but no rounding. So, assume we wanted to divide the day of the year (DY) into periods (P) of 3 days apiece (DP.

DY/DP = P

1  /  3  = 0

2  /  3  = 0

3  /  3  = 1

4 /   3  = 1

etc.

The problem, of course, is that we need DY = 3 to be in the first period with DYs 1 and 2, not in the second group with DY = 4. The simple solution is to subtract 1 from the day of the year before dividing by DP. This will produce:

DY-1  / DP = P

(1-1)  /   3  = 0

(2-1)  /   3  = 0

(3-1)  /   3  = 0

(4-1)  /   3  = 1

If we want the period numbering to start with 1, we just add one to the result of the division. Obviously, this formula works for any number of days in the user-defined period. (But a period can’t be longer than a year.)   So our CTE becomes

WITH CalculatedPeriod as
(SELECT TransDate
       ,TransAmt
       ,(datepart(DAYOFYEAR,TransDate)-1)/@DP+1 
         as UserPeriod
 FROM #TransHistory)

The query using this CTE substitutes UserPeriod for TransMonth, but is otherwise identical to the Totals by Month query.

-- user-defined period totals
declare @DP int = 28;  -- Days in Period
WITH CalculatedPeriod as
 (SELECT TransDate
        ,TransAmt
        ,(datepart(DAYOFYEAR,TransDate)-1)/@DP+1 
          as UserPeriod
  FROM #TransHistory)
SELECT UserPeriod
     , COUNT(*) as Transactions
     , SUM(TransAmt) as TotalAmt
     , MIN(TransDate) as PeriodStart
     , MAX(TransDate) as PeriodEnd
     , DATEDIFF(DAY, MIN(TransDate), MAX(TransDate))+1 as DaysInPeriod
FROM CalculatedPeriod
GROUP BY UserPeriod WITH ROLLUP
ORDER BY UserPeriod

The results of both queries have the same totals at the top for Transactions and TotalAmt, and all 366 days of 2016 were included. Changing the value of @DP will allow totals to be accumulated into different sized periods at will.   

This technique isn't limited to sequential days. Periods of four weeks based on the week number from DATEPART(WEEK) can be created just by changing the the CTE.  

-- create four-week periods using DATEPART(WEEK)
WITH CalculatedPeriod as 
(SELECT TransDate
  ,TransAmt
  ,(DATEPART(WEEK,Transdate)-1) / 4 + 1 as UserPeriod 
 FROM #TransHistory
)
SELECT UserPeriod as FourWeekPeriod
  , COUNT(*) as Transactions
  , SUM(TransAmt) as TotalAmt
  , MIN(TransDate) as PeriodStart
  , MAX(TransDate) as PeriodEnd
  , DATEDIFF(DAY, MIN(TransDate), MAX(TransDate))+1 as DaysInPeriod
  FROM CalculatedPeriod
  GROUP BY UserPeriod WITH ROLLUP
  ORDER BY UserPeriod

Conclusion

Although newcomers to SQL often bemoan the way integer division works, in this case it works to our advantage. The same principle used with dates in this article could be applied to any sequential series of numbers. If needed, the same technique could be used to total every 1000 transactions based on TransID. The technique is elegantly simple, flexible, and fast. Add it to your toolbox.