Grouping dates by dateadd and datediff

Question

I'm trying to group a bunch of dates by period, namely "month"

First I declare the table :

--inserts 36 dates every 10 days
create table sales (sales_date datetime, sales_amount decimal(12,2))
insert into sales (sales_date,sales_amount)
select '20140101',1000 union all
select '20140110',1000 union all
select '20140120',1000 union all
select '20140130',1000 union all
...

then I query entries grouped by period (of size = precision * months)

DECLARE @precision int = 1 --number of months

SELECT 
dateadd(month, (datediff(month, 0, sales_date) / @precision) * @precision, 0) as FromDate,
dateadd(month, @precision + (datediff(month, 0, sales_date) / @precision) * @precision, 0) as ToDate, 
count(*) as number
FROM sales
GROUP BY 
dateadd(month, (datediff(month, 0, sales_date) / @precision) * @precision, 0),
dateadd(month, @precision + (datediff(month, 0, sales_date) / @precision) * @precision, 0)

Results:

Test 1 : precision of 1 month
From : 01/01/2014, To : 01/02/2014, number : 4
From : 01/02/2014, To : 01/03/2014, number : 2
From : 01/03/2014, To : 01/04/2014, number : 3
From : 01/05/2014, To : 01/06/2014, number : 4
From : 01/06/2014, To : 01/07/2014, number : 3
From : 01/07/2014, To : 01/08/2014, number : 3
From : 01/08/2014, To : 01/09/2014, number : 3
From : 01/09/2014, To : 01/10/2014, number : 3
From : 01/10/2014, To : 01/11/2014, number : 3
From : 01/11/2014, To : 01/12/2014, number : 3
From : 01/12/2014, To : 01/01/2015, number : 2

Test 2 : precision of 2 months
From : 01/01/2014, To : 01/03/2014, number : 6
From : 01/03/2014, To : 01/05/2014, number : 6
From : 01/05/2014, To : 01/07/2014, number : 7
From : 01/07/2014, To : 01/09/2014, number : 6
From : 01/09/2014, To : 01/11/2014, number : 6
From : 01/11/2014, To : 01/01/2015, number : 5

Test 3 : precision of 3 months
From : 01/01/2014, To : 01/04/2014, number : 9
From : 01/04/2014, To : 01/07/2014, number : 10
From : 01/07/2014, To : 01/10/2014, number : 9
From : 01/10/2014, To : 01/01/2015, number : 8

Test 4 : precision of 4 months
From : 01/01/2014, To : 01/05/2014, number : 12
From : 01/05/2014, To : 01/09/2014, number : 13
From : 01/09/2014, To : 01/01/2015, number : 11

Everything works fine until Test 5 :

Test 5 : precision of 5 months
From : 01/10/2013, To : 01/03/2014, number : 6
From : 01/03/2014, To : 01/08/2014, number : 16
From : 01/08/2014, To : 01/01/2015, number : 14

The first line is annoying, I need it to start at 01/01/2014 like the previous precisions

Is there any way to overcome this issue ?

Answer 1

you don't need to divide by precision and again multiply by precision for the FROM column

here is what is happening

datediff(month, 0, sales_date)/5  -> 273.6   as it is integer division, it is converting to 273

further multiplying it by 273*5 = 1365 ( so 3 months are lost due to integer division)

so this one should work for you

DECLARE @precision int = 5 --number of months

SELECT 
dateadd(month, (datediff(month, 0, sales_date) ) , 0) as [From],
dateadd(month, @precision + (datediff(month, 0, sales_date) ) , 0) as [To], 
count(*) as number
FROM sales
GROUP BY 
dateadd(month, (datediff(month, 0, sales_date) ) , 0),
dateadd(month, @precision + (datediff(month, 0, sales_date) ), 0)