Untitled

-- 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
;