Every distinct Date between DateA and Date B -TSQL-CodePudding

I'm searching for a query like a calendar giving me back the distinct Dates between "Date A" and Date "A -49 days".

Date A is the a variable. If I look on the Query on Monday to Sunday it will give me back

the Date of the Sunday in the previous Week
the Date of the Sunday in the Week before the previous week
2 Weeks before the Previous Week
5 Weeks before the Previous Week

For Example: I started the query in '2022-01-23'

a_end: '2022-01-16' a_beginn: '2021-12-05' and every date between b_end:'2022-01-09' b_beginn: '2021-11-29' and every date between etc.

CodePudding user response：

You could use a recursive CTE :

WITH T(d) AS (
  SELECT CAST('2022-01-01' AS date)
  UNION ALL
  SELECT DATEADD(day, -1, d)
  FROM T
  WHERE d >= DATEADD(day, -49, '2022-01-01')
)
SELECT d
FROM T
-- OPTION (MAXRECURSION 1000)

If you have more than 100 days to generate you will need to set the MAXRECURSION query hint which is limited to 100 by default (0 means no limit). Beware of infinite loops with this setting though.

CodePudding user response：

You can generate a dynamic calendar table as in this example:

with FindPrevSunday as (
select 
  dateadd(week,datediff(week, '1900-01-07', getdate()), '1900-01-07') PrevSunday
),
JustFourRows as (
select 1 as x union all select 1 as x union all
select 1 as x union all select 1 as x
),
LotsOfRows as (
select Dte=dateadd(day, -Row_number() over (order by a.x) 1, (select PrevSunday from FindPrevSunday))
from 
JustFourRows a --4
cross Join
JustFourRows b --16
cross join  
JustFourRows c --64
cross join
JustFourRows d -- 256
) 
select Dte
from LotsOfRows
cross join
FindPrevSunday PrevS
where Dte between dateadd(day,-48, Prevs.PrevSunday) and PrevSunday

'1900-01-07' is a fixed reference point; known to be Sunday; datediff(week always brings whole/complete weeks; and we use the cross joins to quickly 'generate' rows corresponding to dates in the calendar; then we assign dates, and then filter for the limit we are interested in. This example can generate up to 256 days, but you can add more cross joins, if you wish.