Creating FISCAL_WEEK column in SQL Server Calendar table

We have a requirement. We need to create a fiscal_week column in a SQL Server table.

Table will have data as Normal_date column as fiscal_date and fiscal_year column which will have year part of the date.

Logic for FISCAL_WEEK is like this:

• FIRST FISCAL WEEK WILL START FROM 1 JAN OF EVERY YEAR AND IT WILL BE TILL FIRST FRIDAY.
• SECOND WEEK STARTS FROM SATURDAY AND IT WILL RUN TILL NEXT FRIDAY.
• THIS WILL GO ON TILL THE END OF YEAR (31 JAN)

We will have data something as below table.

Table With Fields as per Requirement

How would I create query for this ? We will have data from 2010 till 2035 years in the table.

Thanks, Mahesh

Answer

You can use an ad-hoc tally table to create the dates and then the window function sum() over() to calculate the Fiscal_Week

Declare @Date1 date = '2020-01-01'
Declare @Date2 date = '2035-12-31'

Select Fiscal_Date = D
      ,Fiscal_Year = DatePart(YEAR,D)
      ,Day_Name    = DateName(WEEKDAY,D)
      ,Fiscal_Week = case when DateName(WEEKDAY,@Date1)='Saturday' then 0 else 1 end
                     +sum(case when DateName(WEEKDAY,D)='Saturday' then 1 else 0 end) over (partition by DatePart(YEAR,D) order by D)
 From ( Select Top (DateDiff(DAY,@Date1,@Date2)+1) D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),@Date1) 
          From master..spt_values n1,master..spt_values n2,master..spt_values n3
      ) A

Results

Fiscal_Date Fiscal_Year Day_Name    Fiscal_Week
2020-01-01  2020        Wednesday   1
2020-01-02  2020        Thursday    1
2020-01-03  2020        Friday      1
2020-01-04  2020        Saturday    2
2020-01-05  2020        Sunday      2
2020-01-06  2020        Monday      2
...