Stuck trying to create a date table that shows week numbers for financial years

Question

Post reply

Stuck trying to create a date table that shows week numbers for financial years

scienceof8

Old Hand

Points: 349
More actions
October 14, 2020 at 12:04 pm
Go to Answer
#3797097
Hello All,
Please can you help me with the following challenge:
I need to create a date table to join against for another query.
The table needs to indicate week numbers (for Fridays) for financial years (week 1 being the first in April) and also highlight the 3rd Friday of every month.
I would like to create the table based on a range, for example 01/04/2012 to current date.
The table ideally will look the following or similar:

The following from another forum is similar:
```
DECLARE @StartDate DATETIME
SET @StartDate = '01/01/2013'

DECLARE @EndDate DATETIME 
SET @EndDate = GETDATE()

DECLARE @TableOfDates TABLE(DateValue DATETIME)

DECLARE @CurrentDate DATETIME

SET @CurrentDate = @StartDate

WHILE @CurrentDate <= @EndDate
BEGIN
    INSERT INTO @TableOfDates(DateValue) VALUES (@CurrentDate)

    SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate)
END

SELECT DateValue,'W'+cast(DATEPART(wk,DateValue) as varchar(30)) as 'week' FROM @TableOfDates
```
Hopefully someone can help, please let me know if you need any further information?
Thanks in advance

ScottPletcher

SSC Guru

Points: 101074

October 14, 2020 at 3:09 pm

Answer

#3797185

The good-ole standard tally table should handle this nicely. I default to end of calendar year for end date; naturally change that if/as you need to.

DECLARE @end_date date
DECLARE @start_date date

SET @start_date = '20120401'
IF @end_date IS NULL
    /* default = last day of current calendar year, adjust as needed to match your requirements */
    SET @end_date = DATEADD(DAY, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0))
    /* this would set last day to end of next fiscal year, based on Apr to Mar */
    --SET @end_date = DATEADD(DAY, -1, DATEADD(MONTH, 4, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)))

;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_starting_friday AS (
    SELECT DATEADD(DAY, -DATEDIFF(DAY, 4, DATEADD(DAY, 6, @start_date)) % 7, DATEADD(DAY, 6, @start_date)) AS starting_friday
)
SELECT
    YEAR(Date) % 100 + CASE WHEN MONTH(Date) >= 4 THEN 1 ELSE 0 END AS FinancialYear,
    CASE WHEN MONTH(Date) >= 4 THEN MONTH(Date) - 3 ELSE MONTH(Date) + 9 END AS Month,
    t.number + 1 AS Week,
    Date,
    CASE WHEN DAY(Date) BETWEEN 15 AND 21 THEN 'Yes' ELSE 'No' END AS IsThirdFriday
FROM cte_calc_starting_friday f
INNER JOIN cte_tally1000 t ON t.number BETWEEN 0 AND DATEDIFF(DAY, starting_friday, @end_date) / 7
CROSS APPLY (
    SELECT DATEADD(DAY, 7 * t.number, starting_Friday) AS Date
) AS calc1
ORDER BY t.number

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11395 More actions · Answer 1

Please provide DDL and sample data. Pictures are great, but we cannot readily consume them to help with your query

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

scienceof8 Old Hand Points: 349 More actions · Answer 2

Hello Mike,

There is no sample data for this one. The table needs to be created dynamically and the image is supplied is ultimately an example of the result set i am looking for.

I would obviously usually provide all source tables, etc.

scienceof8 Old Hand Points: 349 More actions · Answer 3

Thanks Scott,

That was exactly what I was looking for.

I needed the week number per year (not a running tally of weeks) so the only change i made was to change the following line,

from:

    t.number + 1 AS Week,

to:

    DATEPART(wk,Date) AS Week,