Get Monday dates for last 4 weeks

Transact-SQL
1

Hi,

I need query where I need to get date for each Monday for last 4 weeks. Also If Monday date is missing then get next subsequent day date for that missing Monday for that week.

Can anyone help on this?

2

I think is it helpful

declare @fromdate datetime ,@todate datetime

set @fromdate = dateadd(MONTH, -1, CURRENT_TIMESTAMP)
set @todate = CURRENT_TIMESTAMP

;with cte as (SELECT DATEADD(DAY,number,@fromdate) [Date]
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(DAY,number,@fromdate) <=@todate)

select convert(date,Date) from cte where datename(dw,date) = 'Monday'

3

Thanks for reply. However this query doesn't considers missing dates. E.g. If in last four weeks entry is missing for specific Monday then I need next date(Tueday after missing Monday) for the same.

4

What is the indication/criterion or column that helps you determine that a Monday is missing?

Another way to get the last 4 Mondays:

select
	dateadd(dd, -datediff(dd,0,getdate())%7-n*7,getdate())
from
	(values (0),(1),(2),(3) ) v(n);
5

Hello James,

I have one column for dates. So if any entry is missing for Monday for last four weeks I need to select next sub subsequent date for that.