CODEWITHSUNDEEP
sql-server
CMJ
CMJ

Reputation: 141

Get certain dates and number of days between each row from two dates

I am looking to find all the dates between two dates, with a three months frequency, as well as the last day in the interval and the number of days between the dates in the output.

I have the following SQL: 

DECLARE @StartDate date = '2019-10-21'
DECLARE @EndDate date = '2020-06-25'
DECLARE @Frequenc int = 3

SELECT 
CalendarID, 
MAX(c.Year),
MAX(c.Month), 
c.DayofMonth

FROM Dim_Calendar c

WHERE c.Date BETWEEN @StartDate AND @EndDate AND c.DayofMonth = DATEPART(dd, @StartDate)

GROUP BY c.DayofMonth, c.CalendarID

And what I get from that query is the following: 

CalendarID | Year | Month | DayofMonth
20191021     2019   10      21
20191121     2019   11      21
20191221     2019   12      21
20200121     2020   1       21
20200221     2020   2       21
20200321     2020   3       21
20200421     2020   4       21
20200521     2020   5       21
20200621     2020   6       21

What I need to get is the following: 

CalendarID | Year | Month | DayofMonth | DaysBetween
20191021     2019   10      21           0
20200121     2020   1       21           92
20200421     2020   4       21           91
20200625     2020   6       25           65

I hope this makes sense, and thank you in advance :-)

UPDATE

I found a solution to part of the question. Now I just need to find the days in between the dates in each row: 

DECLARE @StartDate date = '2019-10-21'
DECLARE @EndDate date = '2020-06-25'
DECLARE @DrawdownDate date = '2019-06-25'
DECLARE @Frequency int = 3


SELECT 
CalendarID, 
MAX(c.Year) AS Year,
MAX(c.Month) AS Month, 
c.DayofMonth AS DayofMonth

FROM Dim_Calendar c

WHERE   c.Date BETWEEN @StartDate AND @EndDate 
        AND ((c.DayofMonth = DATEPART(dd, @StartDate) 

        AND Month IN 
        (DATEPART(MM,@StartDate),
        DATEPART(MM,DATEADD(mm, @Frequency, @StartDate)), 
        DATEPART(MM,DATEADD(mm, @Frequency * 2, @StartDate)),
        DATEPART(MM,DATEADD(mm, @Frequency * 3, @StartDate)))

        ) OR c.Date = @EndDate)

GROUP BY c.DayofMonth, c.CalendarID

Upvotes: 0

Views: 42

Answers (1)

CMJ
CMJ

Reputation: 141

I found the answer after searching, and used the LAG() function. Below is the code I used: 

DECLARE @StartDate date = '2019-10-21'
DECLARE @EndDate date = '2020-06-25'
DECLARE @DrawdownDate date = '2019-06-25'
DECLARE @Frequency int = 3


SELECT 
c.CalendarID, 
MAX(c.Year) AS Year,
MAX(c.Month) AS Month, 
c.DayofMonth AS DayofMonth, 
DATEDIFF(day, LAG(c.Date, 1) OVER (ORDER BY c.Date), c.Date) AS DateDiff


FROM Dim_Calendar c

WHERE   c.Date BETWEEN @StartDate AND @EndDate 
        AND ((c.DayofMonth = DATEPART(dd, @StartDate) 

        AND Month IN 
        (DATEPART(MM,@StartDate),
        DATEPART(MM,DATEADD(mm, @Frequency, @StartDate)), 
        DATEPART(MM,DATEADD(mm, @Frequency * 2, @StartDate)),
        DATEPART(MM,DATEADD(mm, @Frequency * 3, @StartDate)))

        ) OR c.Date = @EndDate)

GROUP BY c.DayofMonth, c.Date, c.CalendarID

And the result: 

CalendarID | Year | Month | DayofMonth | DaysBetween
20191021     2019   10      21           0
20200121     2020   1       21           92
20200421     2020   4       21           91
20200625     2020   6       25           65

Upvotes: 1

Related Questions