I'm having trouble calculating the next business day in snowflake SQL, excluding weekends and state based public holidays (not all public holidays are national in Australia).
My end goal is to adjust a service request job open date to align with the clients agreed service hours. so if a job request comes in on a weekend for example, the job is actually logged first thing monday morning contractually. Any idea what I'm missing that's making the non work days calculate incorrectly would be greatly appreciated. Thanks in advance.
This is my code so far for one of the states:
SELECT DISTINCT "Report Date",
CASE WHEN ACT.holiday_name IS NULL AND "Calendar Day in Week" IN ('Sat','Sun') THEN "Calendar Day in Week" ELSE ACT.holiday_name END AS "ACT Public Holiday",
CASE WHEN "ACT Public Holiday" IS NULL THEN 'Work Day' END AS "ACT Work Day",
CASE WHEN "ACT Public Holiday" IS NOT NULL THEN LEAD("Report Date") OVER (PARTITION BY "ACT Work Day" ='Work Day' ORDER BY "Report Date") END AS "ACT Next Work Day"
FROM "ODS"."DWBI_DATAHUB"."V_D_CALENDAR" C
LEFT JOIN "ODS"."ODS"."DS_ALLIANCE_HOLIDAY" ACT ON C."Report Date" = ACT.HOLIDAY_DATE AND (ACT.IS_ALL_NODES = 'Y' OR ACT.NODE_ID = 'ACT')
LEFT JOIN "ODS"."ODS"."DS_ALLIANCE_HOLIDAY" NSW ON C."Report Date" = NSW.HOLIDAY_DATE AND (NSW.IS_ALL_NODES = 'Y' OR NSW.NODE_ID = 'NSW')
LEFT JOIN "ODS"."ODS"."DS_ALLIANCE_HOLIDAY" QLD ON C."Report Date" = QLD.HOLIDAY_DATE AND (QLD.IS_ALL_NODES = 'Y' OR QLD.NODE_ID = 'QLD')
LEFT JOIN "ODS"."ODS"."DS_ALLIANCE_HOLIDAY" VIC ON C."Report Date" = VIC.HOLIDAY_DATE AND (VIC.IS_ALL_NODES = 'Y' OR VIC.NODE_ID = 'VIC')
LEFT JOIN "ODS"."ODS"."DS_ALLIANCE_HOLIDAY" SA ON C."Report Date" = SA.HOLIDAY_DATE AND (SA.IS_ALL_NODES = 'Y' OR SA.NODE_ID = 'SA')
LEFT JOIN "ODS"."ODS"."DS_ALLIANCE_HOLIDAY" WA ON C."Report Date" = WA.HOLIDAY_DATE AND (WA.IS_ALL_NODES = 'Y' OR WA.NODE_ID = 'WA')
LEFT JOIN "ODS"."ODS"."DS_ALLIANCE_HOLIDAY" NT ON C."Report Date" = NT.HOLIDAY_DATE AND (NT.IS_ALL_NODES = 'Y' OR NT.NODE_ID = 'NT')
WHERE "Report Date" IS NOT NULL
ORDER BY "Report Date"
