Untitled
unknown
plain_text
6 days ago
4.4 kB
3
Indexable
-- Query: Business Date and Report Date With CTE_PARAM AS ( SELECT CAST('{CustomCurrentDate}' AS DATE) as CustomCurrentDate ) , CTE_PeriodRange AS ( SELECT CASE WHEN (SELECT CustomCurrentDate FROM CTE_PARAM) IS NOT NULL THEN TRY_CONVERT(DATE,(SELECT CustomCurrentDate FROM CTE_PARAM),23) ELSE CAST((SELECT system_dt FROM custom_comm_system_dt sd WHERE sd.business_area = 'FA' ) AS DATE) END as CurrentDate ) , CTE_DateList AS ( SELECT TOP 60 DATEADD(DAY, -1* ROW_NUMBER() OVER(ORDER BY a.object_id), DATEADD(DAY, 10, (SELECT CurrentDate FROM CTE_PeriodRange) ) ) as AsOfDate FROM sys.all_objects a CROSS JOIN sys.all_objects b ) , CTE_AccountPeriod AS ( SELECT FORMAT(efctv_dt, 'MMM yyyy') as AccountPeriod , cutoff_dt as CutOffDate , efctv_dt as EffectiveDate , ROW_NUMBER() OVER (ORDER BY efctv_dt) as PeriodSeq FROM custom_comm_actg_period co (NOLOCK) WHERE co.efctv_dt >= DATEADD(YEAR, -1, DATEPART(year, (SELECT CurrentDate FROM CTE_PeriodRange))) ) , AccountPeriod AS ( SELECT ap1.AccountPeriod , ap1.EffectiveDate as AccountPeriodEffectiveDate , ap1.EffectiveDate , ap1.CutOffDate as CurrentCutOffDate , ap2.CutOffDate as LastCutOffDate , ap2.AccountPeriod as LastAccountPeriod FROM CTE_AccountPeriod ap1 LEFT JOIN CTE_AccountPeriod ap2 ON ap1.PeriodSeq - 1 = ap2.PeriodSeq ) , CTE_Holidays AS ( SELECT holiday_dt, holiday_typ FROM custom_comm_calendar (NOLOCK) WHERE business_area = 'FA' AND ctryiso2_cd = 'HK' AND deleted = 0 ) , CTE_Date2Calendar AS ( SELECT DATEPART(YEAR, AsOfDate) * 10000 + DATEPART(MONTH, AsOfDate) * 100 + DATEPART(DAY, AsOfDate) as CalendarSK , CAST( CASE DATEPART(dw, AsOfDate) WHEN 1 THEN 1 -- Sunday WHEN 7 THEN 1 -- Saturday ELSE 0 END AS BIT) as IsWeekend , AsOfDate FROM CTE_DateList ) , CTE_BaseCalendar AS ( SELECT CalendarSK ,AsOfDate ,CASE WHEN holiday_typ IS NULL AND IsWeekend != 0 THEN 'E' ELSE holiday_typ END as HolidayType ,CASE WHEN holiday_typ IS NOT NULL OR IsWeekend != 0 THEN 0 ELSE 1 END as IsWorkingDay FROM CTE_Date2Calendar dc LEFT JOIN CTE_Holidays hol ON dc.AsOfDate = hol.holiday_dt ) , CTE_WorkSeq AS ( SELECT CalendarSK ,AsOfDate ,HolidayType ,IsWorkingDay ,ws.WorkDaySeq FROM CTE_BaseCalendar bc OUTER APPLY ( SELECT COUNT(CalendarSK) as WorkDaySeq FROM CTE_BaseCalendar ws WHERE ws.CalendarSK<=bc.CalendarSK AND ws.IsWorkingDay=1 ) ws ) , Calendar AS ( SELECT ws1.AsOfDate ,qd.AsOfDate as QueryDate ,qd.HolidayType as HolidayType ,qd.IsWorkingDay as IsWorkingDay ,qd.WorkDaySeq as WorkDaySeq ,CASE WHEN vd.AsOfDate = c_bd_0.AsOfDate AND qd.IsWorkingDay = 1 THEN 1 ELSE 0 END as IsReportDay ,c_bd_0.AsOfDate as CurrentCutOffDate ,l_bd_0.AsOfDate as LastCutOffDate ,vd.AccountPeriod as AccountPeriod ,vd.AccountPeriodEffectiveDate as AccountPeriodEffectiveDate ,vd.CurrentCutOffDate as AccountPeriodCutOffDate ,vd.LastAccountPeriod as LastAccountPeriod ,vd.LastCutOffDate as LastAccountPeriodCutOffDate FROM CTE_WorkSeq ws1 OUTER APPLY ( SELECT TOP 1 AsOfDate, HolidayType, IsWorkingDay, WorkDaySeq, CalendarSK FROM CTE_WorkSeq ws2 WHERE ws2.CalendarSK < ws1.CalendarSK ) qd OUTER APPLY ( SELECT AsOfDate, ap.AccountPeriodEffectiveDate, ap.AccountPeriod, ap.CurrentCutOffDate, ap.LastAccountPeriod, ap.LastCutOffDate, CalendarSK, WorkDaySeq FROM CTE_WorkSeq ws2 LEFT JOIN AccountPeriod ap ON ap.LastCutOffDate < ws2.AsOfDate AND ap.CurrentCutOffDate >= ws2.AsOfDate WHERE ws2.CalendarSK <= qd.CalendarSK AND ws2.IsWorkingDay = 1 AND ws2.WorkDaySeq = qd.WorkDaySeq-1 ) vd OUTER APPLY ( SELECT TOP 1 AsOfDate, CalendarSK, WorkDaySeq FROM CTE_WorkSeq ws2 WHERE ws2.AsOfDate <= vd.CurrentCutOffDate AND ws2.IsWorkingDay = 1 ) c_bd_0 OUTER APPLY ( SELECT TOP 1 AsOfDate, CalendarSK, WorkDaySeq FROM CTE_WorkSeq ws2 WHERE ws2.AsOfDate <= vd.LastCutOffDate AND ws2.IsWorkingDay = 1 ) l_bd_0 ) SELECT TOP 1 * FROM Calendar WHERE AsOfDate >= (SELECT CurrentDate FROM CTE_PeriodRange) ORDER BY AsOfDate ASC ;
Editor is loading...
Leave a Comment