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 (112) 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 userdefined 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 
20160101 
20161231 
366 
1 
8512 
5316003 
20160101 
20160131 
31 
2 
7980 
4962757 
20160201 
20160229 
29 
3 
8437 
5257661 
20160301 
20160331 
31 
4 
8300 
5227137 
20160401 
20160430 
30 
Totals by UserDefined 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:
DY1 / DP = P
(11) / 3 = 0
(21) / 3 = 0
(31) / 3 = 0
(41) / 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 userdefined 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.
 userdefined 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 UserDefined Period
UserPeriod

Transactions

TotalAmt

PeriodStart

PeriodEnd

DaysInPeriod

NULL

100216

62640271

20160101

20161231

366

1

7681

4790244

20160101

20160128

28

2

7720

4823178

20160129

20160225

28

3

7649

4745513

20160226

20160324

28

4

7655

4788525

20160325

20160421

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