SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Bones of SQL – Grouping By User-Defined Calendar Periods

By Bob Hovious,

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 1st? 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.

Partial Results of Totals by Month
TransMonth Transactions TotalAmt  PeriodStart PeriodEnd  DaysInPeriod
NULL 100216   62640271  2016-01-01 2016-12-31 366
1 8512 5316003 2016-01-01 2016-01-31 31
2 7980 4962757 2016-02-01 2016-02-29 29
3 8437 5257661 2016-03-01 2016-03-31 31
4 8300 5227137 2016-04-01 2016-04-30 30

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

Partial Results of Totals By User-Defined Period

UserPeriod 

Transactions 

TotalAmt

PeriodStart

PeriodEnd 

DaysInPeriod 

NULL

100216

62640271

2016-01-01

2016-12-31

 366

1

7681

4790244

2016-01-01

2016-01-28

 28

2

7720

4823178

2016-01-29

2016-02-25

 28

3

7649

4745513

2016-02-26

2016-03-24

 28

4

7655

4788525

2016-03-25

2016-04-21

 28

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.

 

Resources:

Generate Sample Data.sql

This article is part of the series Bones of SQL:

Total article views: 1475 | Views in the last 30 days: 2
 
Related Articles
FORUM

Bi-Weekly Pay Period Total Worked Hours

How to figure the total # of hours from a given date in a pay period.

FORUM

Getting datepart to work for query

Datepart function

FORUM

Using datepart as Precendence Constraint

Using datepart as Precendence Constraint

FORUM

DatePart

Show rolling future 12 months

FORUM

Datepart combining into one filed

trying to make this datepart look more professional

Tags
date manipulation    
group by    
t-sql    
 
Contribute