none
SQL query to find last business day back 13 month

    Question

  • Hi,

    i need a sql query which return output as like below table for field LastBusDayBack13Mth_Dt for a given Date field.


    Date BusinessDay DayOfWeek LastBusDayBack13Mth_Dt
    2019-01-01 Not Business Day Tue 2017-12-30
    2019-01-02 Business Day Wed 2017-12-30
    2019-01-03 Business Day Thu 2017-12-30
    2019-01-04 Business Day Fri 2017-12-30
    2019-01-05 Not Business Day Sat 2017-12-30
    2019-01-06 Not Business Day Sun 2017-12-30
    2019-01-07 Business Day Mon 2017-12-30
    2019-02-01 Business Day Fri 2018-01-31
    2019-02-02 Not Business Day Sat 2018-01-31
    2019-02-03 Not Business Day Sun 2018-01-31
    2019-02-04 Business Day Mon 2018-01-31
    2019-02-05 Business Day Tue 2018-01-31

    Thanks!

    Friday, March 15, 2019 5:10 AM

All replies

  • Hi RavSup,

    Please see:

    IF OBJECT_ID('TempDb..#CalendarTable') IS NOT NULL
    DROP TABLE #CalendarTable
    
    CREATE TABLE #CalendarTable
    (
     [Date] DATE,
     BusinessDay VARCHAR(64),
     [DayOfWeek] VARCHAR(128)
    )
    
    ;WITH CTE AS
    (
     SELECT CAST('2017-12-1' AS DATE) AS V_DATE
     UNION ALL
     SELECT DATEADD(DAY,1, V_DATE) FROM CTE WHERE V_DATE<'2019-02-05'
    )
    INSERT INTO #CalendarTable
    SELECT V_DATE,CASE WHEN LEFT(DATENAME(WEEKDAY, V_DATE),3) IN ('Sat','Sun') THEN 'Not Business Day' ELSE 'Business Day'END, LEFT(DATENAME(WEEKDAY, V_DATE),3) FROM CTE
    option (maxrecursion 0)
    
    UPDATE #CalendarTable SET BusinessDay='Not Business Day' WHERE MONTH([Date])=1 AND DAY([Date])=1
    
    --Query
    SELECT 
    T.[Date],
    T.BusinessDay,
    T.[DayOfWeek],
    T1.MAX_DATE AS LastBusDayBack13Mth_Dt 
    FROM #CalendarTable T
    CROSS APPLY (SELECT MAX([Date]) AS MAX_DATE FROM #CalendarTable WHERE BusinessDay='Business Day' AND [Date]<=EOMONTH(T.[Date],-13)) T1
    WHERE T.[Date]>='2019-01-01'
    
    --Output
    /*
    Date       BusinessDay                                                      DayOfWeek                                                                                                                        LastBusDayBack13Mth_Dt
    ---------- ---------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------
    2019-01-01 Not Business Day                                                 Tue                                                                                                                              2017-12-29
    2019-01-02 Business Day                                                     Wed                                                                                                                              2017-12-29
    2019-01-03 Business Day                                                     Thu                                                                                                                              2017-12-29
    2019-01-04 Business Day                                                     Fri                                                                                                                              2017-12-29
    2019-01-05 Not Business Day                                                 Sat                                                                                                                              2017-12-29
    2019-01-06 Not Business Day                                                 Sun                                                                                                                              2017-12-29
    2019-01-07 Business Day                                                     Mon                                                                                                                              2017-12-29
    2019-01-08 Business Day                                                     Tue                                                                                                                              2017-12-29
    2019-01-09 Business Day                                                     Wed                                                                                                                              2017-12-29
    2019-01-10 Business Day                                                     Thu                                                                                                                              2017-12-29
    2019-01-11 Business Day                                                     Fri                                                                                                                              2017-12-29
    2019-01-12 Not Business Day                                                 Sat                                                                                                                              2017-12-29
    2019-01-13 Not Business Day                                                 Sun                                                                                                                              2017-12-29
    2019-01-14 Business Day                                                     Mon                                                                                                                              2017-12-29
    2019-01-15 Business Day                                                     Tue                                                                                                                              2017-12-29
    2019-01-16 Business Day                                                     Wed                                                                                                                              2017-12-29
    2019-01-17 Business Day                                                     Thu                                                                                                                              2017-12-29
    2019-01-18 Business Day                                                     Fri                                                                                                                              2017-12-29
    2019-01-19 Not Business Day                                                 Sat                                                                                                                              2017-12-29
    2019-01-20 Not Business Day                                                 Sun                                                                                                                              2017-12-29
    2019-01-21 Business Day                                                     Mon                                                                                                                              2017-12-29
    2019-01-22 Business Day                                                     Tue                                                                                                                              2017-12-29
    2019-01-23 Business Day                                                     Wed                                                                                                                              2017-12-29
    2019-01-24 Business Day                                                     Thu                                                                                                                              2017-12-29
    2019-01-25 Business Day                                                     Fri                                                                                                                              2017-12-29
    2019-01-26 Not Business Day                                                 Sat                                                                                                                              2017-12-29
    2019-01-27 Not Business Day                                                 Sun                                                                                                                              2017-12-29
    2019-01-28 Business Day                                                     Mon                                                                                                                              2017-12-29
    2019-01-29 Business Day                                                     Tue                                                                                                                              2017-12-29
    2019-01-30 Business Day                                                     Wed                                                                                                                              2017-12-29
    2019-01-31 Business Day                                                     Thu                                                                                                                              2017-12-29
    2019-02-01 Business Day                                                     Fri                                                                                                                              2018-01-31
    2019-02-02 Not Business Day                                                 Sat                                                                                                                              2018-01-31
    2019-02-03 Not Business Day                                                 Sun                                                                                                                              2018-01-31
    2019-02-04 Business Day                                                     Mon                                                                                                                              2018-01-31
    2019-02-05 Business Day                                                     Tue                                                                                                                              2018-01-31
    */

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 15, 2019 5:49 AM
    Moderator
  • like this

    declare @t table
    (
    Date date,
    BusinessDay varchar(50),
    [DayOfWeek] varchar(3),
    LastBusDayBack13Mnth_dt date
    )
    
    ;With CTE(N)
    AS
    (
    SELECT 1 UNION ALL
    SELECT 1 UNION ALL
    SELECT 1 UNION ALL
    SELECT 1 UNION ALL
    SELECT 1 UNION ALL
    SELECT 1 
    ),
    Number_Mat
    AS
    (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS Seq
    FROM CTE c1
    CROSS JOIN CTE c2
    CROSS JOIN CTE c3
    CROSS JOIN CTE c4
    CROSS JOIN CTE c5
    CROSS JOIN CTE c6
    )
    INSERT @t
    SELECT DATEADD(dd,Seq-1,'20190101') AS Date,
    CASE WHEN DATENAME(dw,DATEADD(dd,Seq-1,'20190101')) IN ('Saturday','Sunday') OR h.Date IS NOT NULL
    THEN 'Not BusinessDay'
    ELSE 'BusinessDay'
    END,
    --DATENAME(dw,DATEADD(dd,Seq-1,'20190101'))
    DATEADD(dd,- CASE WHEN DATENAME(dw,EOMONTH(DATEADD(dd,Seq-1,'20190101'),-13)) = 'Saturday' THEN 2
    WHEN  DATENAME(dw,EOMONTH(DATEADD(dd,Seq-1,'20190101'),-13)) = 'Sunday' THEN 1
    ELSE 0
    END,EOMONTH(DATEADD(dd,Seq-1,'20190101'),-13))
    FROM Number_Mat n
    LEFT JOIN HOliday h
    ON h.Date = DATEADD(dd,Seq-1,'20190101')
    
    
    UPDATE t
    SET LastBusDayBack13Mnth_dt = LastBusDay
    FROM @t t
    INNER JOIN Holiday h
    ON h.Dat = t.LastBusDayBack13Mnth_dt
    CROSS APPLY
    (
    SELECT MAX([Date]) AS LastBusDay
    FROM @t
    WHERE BusinessDay = 'BusinessDay'
    AND [Date] < LastBusDayBack13Mnth_dt
    )t1
    
    SELECT *
    FROM @T

    Here Holiday is your holiday table with all holiday dates added


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, March 15, 2019 7:22 AM