How to get sets of date divided by week on the basis of present date and First day of month

Question

for example

if today is 22 Jan ,then i want get the date set as

(StartDate :16-Jan-2018 & EndDate:22-Jan-2018)

and

second date set is as

(StartDate :9-Jan-2018 & EndDate:15-Jan-2018)

and third date set as

(StartDate :02-Jan-2018 & EndDate:08-Jan-2018)

and last date set as

(StartDate :1-Jan-2018 & EndDate:1-Jan-2018)

where first date will be given by user i.e :22-Jan-2018

Add sample data and the expected result as formatted text!!! (No images, please.) Also show us your current query attempt. — jarlh

Jayasurya Satheesh Jayasurya Satheesh · Accepted Answer · 2018-01-22T12:21:15

Try This

DECLARE @First DATE = '01/22/2018'

;WITH CTE
AS
(
    SELECT
       SeqNo = 1,
       StartDt = CAST(DATEADD(DD,-6,@First) AS DATE),
       EndDt = CAST(@First AS DATE)

    UNION ALL

    SELECT
       SeqNo = SeqNo+1,
       StartDt = CASE WHEN DAY(CAST(DATEADD(DD,-1,StartDt) AS DATE)) = 1
                       THEN CAST(DATEADD(DD,-1,StartDt) AS DATE)
                    ELSE CAST(DATEADD(DD,-7,StartDt) AS DATE) END,
       EndDt = CAST(DATEADD(DD,-1,StartDt) AS DATE)
       FROM CTE
          WHERE SeqNo < 4
)
SELECT
    *
    FROM CTE

My Result

How to get sets of date divided by week on the basis of present date and First day of month

1 Answers