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

Transact-SQL
1

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.

Thank you for any help you can provide.
Luke

2

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

3

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

you have your start date

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

4

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
     AS (SELECT TOP 1 Dateadd(wk, 0, Dateadd(day, 1 - Datepart(weekday,
                                                      @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

image
image319×539 6.15 KB

5 
;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
6

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
7

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);
8

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.