sql to check 53 weeks and then compare the 53 week this year with week1 last year.

Question

Hi Guys

Need help in this sql logic

I have a calendar(designed as view) . This works perfect for 52 weeks but is unable to return date filed last year when the week is 53(this year).

What i really want is (where i am facing issue is) to calculate datefield last year(actual date) and the way i have to calculate is

if we have week 53 (this year)(from 24-01-2021 to 30-01-2021) then it should be compared against week 1 of last year(26-01-2020 to 01-02-2020)

My sql code:-

with Daily
AS
(
select

cast(replace(cast(cp.StartDate as date),'-','') as varchar(10)) as Date_Key,  
cast(cp.StartDate as date) as Date_fld,  
c.pkCalendarID Year_ID,  
c.Description Entertainer_Year,  
C.Year Fiscal_Year,  
c.StartDate FY_Start_Date,  
c.EndDate FY_End_Date,   
cp.Sequence Day_In_Year,  
cp.name Day_Name,  
cp.StartDate Day_Start_Date,  
cp.EndDate Day_End_Date  

from Calendar c
join CalendarPeriod cp on c.pkCalendarID = cp.fkCalendarID
join PeriodType pt on pt.pkPeriodTypeID = cp.fkPeriodTypeID
join PeriodTypeCalendar ptc on ptc.fkCalendarID = c.pkCalendarID and ptc.fkPeriodTypeID = pt.pkPeriodTypeID
where cp.fkPeriodTypeID = 3
), Weekly
as
(
select
C.Year w_Fiscal_Year,
cp.Sequence Week_In_Year,
cp.name Week_Name,
cp.StartDate Week_Start_Date,
cp.EndDate Week_End_Date,
cast(replace(cast(cp.StartDate as date),'-','') as varchar(10)) as Week_Start_Date_Key,
cast(replace(cast(cp.EndDate as date),'-','') as varchar(10)) as Week_End_Date_Key
from Calendar c
join CalendarPeriod cp on c.pkCalendarID = cp.fkCalendarID
join PeriodType pt on pt.pkPeriodTypeID = cp.fkPeriodTypeID
join PeriodTypeCalendar ptc on ptc.fkCalendarID = c.pkCalendarID and ptc.fkPeriodTypeID = pt.pkPeriodTypeID
where cp.fkPeriodTypeID = 2
),Monthly
as
(
select
C.Year m_Fiscal_Year,
cp.Sequence Month_In_Year,
cp.name Month_Name,
cp.StartDate Month_Start_Date,
cp.EndDate Month_End_Date,
cast(replace(cast(cp.StartDate as date),'-','') as varchar(10)) as Month_Start_Date_Key,
cast(replace(cast(cp.EndDate as date),'-','') as varchar(10)) as Month_End_Date_Key
from Calendar c
join CalendarPeriod cp on c.pkCalendarID = cp.fkCalendarID
join PeriodType pt on pt.pkPeriodTypeID = cp.fkPeriodTypeID
join PeriodTypeCalendar ptc on ptc.fkCalendarID = c.pkCalendarID and ptc.fkPeriodTypeID = pt.pkPeriodTypeID
where cp.fkPeriodTypeID = 1
),period_map
as
(
select
*
from Daily d
join Weekly w on d.Fiscal_Year = w.w_Fiscal_Year and cast(d.Date_fld as date) between cast(w.Week_Start_Date as date) and cast(w.Week_End_Date as date)
join monthly m on m.m_Fiscal_Year = d.Fiscal_Year and cast(d.Date_fld as date) between cast(m.Month_Start_Date as date) and cast(m.Month_End_Date as date)
)

select
Date_Key,
Week_Start_Date_Key,
Week_End_Date_Key,
Month_Start_Date_Key,
Month_End_Date_Key,
Day_In_Year,
Week_In_Year,
Month_In_Year,
Fiscal_Year,
Date_fld,
cast(Day_Name as varchar(7)) as Day_Name,
Week_Start_Date,
Week_End_Date,
cast(Week_Name as varchar(7)) as Week_Name,
Month_Start_Date,
Month_End_Date,
cast(Month_Name as varchar(9)) as Period_Name,
cast(DateName( month , DateAdd( month , Month_In_Year , -1 )) as varchar(9)) as Month_Name_Full,
cast(left(DateName( month , DateAdd( month , Month_In_Year , -1 )),3) as varchar(3)) as Month_Name_Short,
cast(DATENAME(DW,Date_fld) as varchar(9)) as Week_Name_Full,
cast(left(DATENAME(DW,Date_fld),3) as varchar(3)) as Week_Name_Short,
cast((select Date_fld from daily where Fiscal_Year = pm.Fiscal_Year - 1 and Day_In_Year = pm.Day_In_Year) as date) as Date_fld_LY,
cast(case when Month_In_Year between 1 and 3 then 'Q1'
when Month_In_Year between 4 and 6 then 'Q2'
when Month_In_Year between 7 and 9 then 'Q3'
when Month_In_Year between 10 and 12 then 'Q4' End as varchar(2)) as FY_Quarter_Name,
cast(case when Month_In_Year between 1 and 3 then 1
when Month_In_Year between 4 and 6 then 2
when Month_In_Year between 7 and 9 then 3
when Month_In_Year between 10 and 12 then 4 End as tinyint) as [FY_Quarter],
cast(case when Month_In_Year between 1 and 6 then 'H1'
when Month_In_Year between 7 and 12 then 'H2' End as varchar(2)) as [FY_Half_Year_Name],
cast(case when Month_In_Year between 1 and 6 then 1
when Month_In_Year between 7 and 12 then 2 End as tinyint) as [FY_Half_Year]
from period_map pm
--order by cast(Date_fld as date)
GO

As you can see that the date_fld column is populating correctly but when it is week 53 it is giving null value however it should have been((26-01-2020 to 01-02-2020)

Any help will be appreciated

Regards
Farhan Jamil

Answer 1

Use a calendar table and calculate it once, correctly.

https://weblogs.sqlteam.com/dang/2010/07/19/calendar-table-and-datetime-functions/

Answer 2

Hi @Farhan Jamil ，

Experts have provided solutions, did you try and solve the problem as suggested？If your problem has been solved, please choose the answer that is helpful to you and accept it. Thank you!

Regards
Echo