Create a Select to select the first day of the week (Monday) in each week of the financial year

Hi,
I need to create a report that shows the first weekday in each week for 52 weeks (we ignore any extra days). Is there a way to do a select statement to show 52 results, each with a different starting day?
e.g. 05/07/2021
12/07/2021
19/07/2021
etc.

Luke

I've found an article to use a Select from (values). I can use this, but is there a more automatic approach?

one idea

is to use a tally table
in the tally table you will have numbers 1,2,3,4 or whatever 15,16,17

from your startdate select the first weekday

and keep adding 7 to that and again 7 till you reach the end of 52 weeks

i can give a demo by .. writing the t-sql ..please let me know

hi hope this helps

``````DECLARE @start_date DATETIME = '2020-09-08';

WITH numbers
AS (SELECT n = 0 -- Anchor
UNION ALL
SELECT n + 1 -- Recursive
FROM   numbers
WHERE  n + 1 <= 52),
wk_day_startdate
@start_date),
Datediff(dd, 0, @start_date)))
AS
wk_day_startdate
FROM   numbers)
SELECT Dateadd(dd, n * 7, wk_day_startdate)
FROM   numbers,
wk_day_startdate
``````

``````;WITH
cte_tally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally1000 AS (
SELECT 0 AS number UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number
FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2  CROSS JOIN cte_tally10 c3
),
cte_calc_first_Monday AS (
SELECT DATEADD(DAY, -DATEDIFF(DAY, 0, GETDATE()) % 7, CAST(GETDATE() AS date)) AS first_Monday
)
SELECT DATEADD(DAY, t.number * 7, first_Monday) AS Monday_dates
FROM cte_calc_first_Monday
INNER JOIN cte_tally1000 t ON t.number BETWEEN 0 AND 51
ORDER BY t.number``````

Thanks Harishgg1.

I did what you suggested, but instead of using a tally table, I used the row_number * 7 (for the amount of days in a week) - 7 since the row_number starts at 1, not 0.

This works really well. It means I am still selecting values, but I find this easier to read and troubleshoot with the limited SQL knowledge I have.

Thanks for the great help.

``````declare @SendDate as date
set @SendDate = '2021-07-05'

SELECT
DateAdd(day,7*ROW_NUMBER() OVER (ORDER BY SendDate)-7,SendDate) as SendDate

FROM (VALUES
(@SendDate),
(@SendDate),
(@SendDate),
(@SendDate),
(@SendDate),
(@SendDate),
(@SendDate),
(@SendDate),
--Done 52 times``````

Here is another method:

``````Declare @SendDate date = '2021-07-05';

Select dateadd(day, -datediff(day, 0, @SendDate) % 7 - 7, @SendDate);  --Previous weeks Monday

With t(n)
As (
Select t.n
From (Values (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)
)
Select Top 52
dateadd(week, checksum(row_number() over(Order By @@spid)), dt.PrevMonday)
From t t1
Cross Join t t2
Cross Join (Values (dateadd(day, -datediff(day, 0, @SendDate) % 7 - 7, @SendDate))) As dt(PrevMonday);
``````

Recursion for number generation has notoriously poor performance in SQL Server, although for only 52/53 values, it's obviously not going to be that noticeable.

But definitely don't use that method for 10Ks, 100Ks, or 1Ms of tally numbers.