CODEWITHSUNDEEP
sqlsql-server
Naucle
Naucle

Reputation: 626

sum dates values based on custom quarters

Let's say I have the following data :

Data to sum

What I want to achieve using SQL, is summing the Montant column, based on quarter ranges (3 months increment) where these quarters start at a custom initial date (not necessarily the 1st of January).

Example :

Let's say, I pick 08/05/2017 as my initial date, my quarters are going to be :

08/05/2017 - 07/08/2017

08/08/2017 - 07/11/2017

08/11/2017 - 07/02/2018

08/02/2018 - 07/05/2018

and so on...

The resulting table will be like this :

Result table

Here's what I achieved so far :

SELECT 
    [Type], 
    CONVERT(date, DATEADD(QUARTER, TAB4.PeriodOffset, '08/05/2017')) AS [Periode], 
    SUM([Montant]) AS [Montant]
FROM
(
    SELECT 
        [Type],
        DATEDIFF(QUARTER, '08/05/2017', [Date]) AS [PeriodOffset]
        [Montant]
    FROM
    ( 
        --Data
    ) TAB3
) TAB4
GROUP BY TAB4.PeriodOffset, TAB4.[Type]

The problem here, is that DATEDIFF(QUARTER, '08/05/2017', [Date]) compares dates based on the standard quarters (starting with the 1st of January, 1st of April, 1st of July and the 1st of October). Which means that DATEDIFF(QUARTER, '08/05/2017', '08/07/2017') is equal to 1 (because 08/05/2017 is in the second standard quarter and 08/07/2017 is in the third) while I need it to be equal to 0 (because 08/05/2017 and 08/07/2017 belong to the same quarter if we start with 08/05/2017). And here's what I get :

enter image description here

Upvotes: 0

Views: 292

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

One way to do this is to generate the custom start and end dates using a recursive cte. Thereafter, left join on to this table to get the desired result.

with quarter_dates(startdate,enddate) as 
(select cast('2017-05-08' as date),cast(dateadd(day,-1,dateadd(month,3,'2017-05-08')) as date)
 union all
 select dateadd(month,3,startdate),dateadd(month,3,enddate) 
 from quarter_dates
 where startdate <'2020-12-01' --change this value based on the last startdate required
)
select d.startdate,d.enddate,y.type,sum(y.montant)
from quarter_dates d
left join yourtable y on y.periode between d.startdate and d.enddate
group by d.startdate,d.enddate,y.type

Upvotes: 1

Related Questions