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.
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
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
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.