Generating weekending dates in SQL Server-CodePudding

I am working in Microsoft SQL Server Management Studio. I want to generate all the weekend dates (Saturdays) for different ID's and their different time ranges.

This is what my data looks like:

ID   minDate    MaxDate
-------------------------
101  5/16/2020  9/25/2021
102  8/7/2021   8/21/2021
103  5/1/2021   9/26/2021

For ID = 101, I would like to return all the week ending dates (Saturdays) starting from 5/16/2020, 5/23/2020, 5/30/2020, 6/6/2020 ..... all the way until 9/25/2021.

And I will need to do the same thing for all the different ID's.

Thank you in advance!

CodePudding user response：

..fiddle..

select *, dateadd(week, nm.rn-1, dateadd(day, 7-datepart(weekday, dateadd(day, @@datefirst,mindate)), mindate)) as _Saturday
from
(
values
(101, cast('20200516' as date), cast('20210925' as date)),
(102, '20210708', '20210822'),
(103, '20210501', '20210926')
) as t(id,mindate,maxdate)
join
(
    --tally..max 1000 weeks
    select row_number() over(order by @@spid) as rn
    from (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) as a(n)
    cross join (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) as b(n)
    cross join (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) as c(n)
) as nm on nm.rn <= datediff(week, mindate, maxdate) 1
where t.maxdate >= dateadd(week, nm.rn-1, dateadd(day, 7-datepart(weekday, dateadd(day, @@datefirst,mindate)), mindate))
order by t.id, nm.rn;