Recommand · October 22, 2021 0

Return Financial Day number of week

I am trying to return a date function here. Please, how can I return the financialdaynumberofweek column using the financial week column as a reference? Please see attached
enter image description here
FinancialWeek

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

Select Fiscal_Date = 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)
,Case When DateName(WEEKDAY,D) = 'Saturday' Then 1
When DateName(WEEKDAY,D) = 'Sunday' Then 2
When DateName(WEEKDAY,D) = 'Monday' Then 3
When DateName(WEEKDAY,D) = 'Tuesday' Then 4
When DateName(WEEKDAY,D) = 'Wednesday' Then 5
When DateName(WEEKDAY,D) = 'Thursday' Then 6
When DateName(WEEKDAY,D) = 'Friday' Then 7 End FinancialDayNumberOfWeek

,Financial_Day = DatePart(DAY,D)
,Day_Name = DateName(WEEKDAY,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