Find last business day from table with date and day in SQL 2008 and 2012

  • prafullaahirrao

    SSC Enthusiast

    Points: 198

    Hi All,

    I need last business day from a table with ID,date and day column in it.

    For e.g.

    ID Date Day

    1 1-Jan-2016 Friday

    2 2-Jan-2016 Saturday

    3 3-Jan-2016 Sunday

    .

    .

    29 29-Jan-2016 Friday

    30 30-Jan-2016 Saturday

    31 31-Jan-2016 Sunday

    32 1-Feb-2016 Monday

    33 2-Feb-2016 Tuesday

    .

    .

    60 29-Feb-2016 Monday

    and so on....

    so I need

    29-Jan-2016 Friday(Excluding Saturday and Sunday)

    29-Feb-2016 Monday

    .

    .

    .

    Thanks

  • Lowell

    SSC Guru

    Points: 323361

    A Calendar Table is really handy for this.

    As an Inline calculation

    the way i would tackle it is with DateMath, and a case statement that checks for Saturday or Sunday

    here i created 64 months if dates, and used a calculation to confirm i was getting the last biz day of the month:

    does this look right?

    /*

    TheDate LastDayOfMonth DatePartOfLastDay DateNameOfLastDay LastBizDay LastBizDayOfWeek

    2016-01-15 00:00:00.000 2016-01-31 1 Sunday 2016-01-29 Friday

    2016-02-15 00:00:00.000 2016-02-29 2 Monday 2016-02-29 Monday

    2016-03-15 00:00:00.000 2016-03-31 5 Thursday 2016-03-31 Thursday

    */

    WITH ExampleMonths

    AS

    (

    SELECT DATEADD(mm,MiniTally.N,'2015-01-15') AS TheDate

    FROM (select row_number() over (order by num) -1 as N

    from (values (1)) t (num)

    group by cube (num, num, num, num, num, num) --2^6 = 64 ,more num = another order power of 2)

    ) MiniTally

    )

    select TheDate,

    DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,TheDate ) + 1, 0)) AS ABitLessThanFirstOfMonth,

    CONVERT(DATE,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,TheDate ) + 1, 0))) AS LastDayOfMonth,

    DATEPART(weekday,(CONVERT(DATE,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,TheDate ) + 1, 0))))) AS DatePartOfLastDay,

    DATENAME(dw,(CONVERT(DATE,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,TheDate ) + 1, 0))))) AS DateNameOfLastDay,

    CASE

    WHEN DATEPART(weekday,(CONVERT(DATE,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,TheDate ) + 1, 0))))) IN (7) --Saturday

    THEN DATEADD(dd,-1,

    CONVERT(DATE,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,TheDate ) + 1, 0)))

    )

    WHEN DATEPART(weekday,(CONVERT(DATE,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,TheDate ) + 1, 0))))) IN(1) --Sunday

    THEN DATEADD(dd,-2,

    CONVERT(DATE,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,TheDate ) + 1, 0)))

    )

    ELSE CONVERT(DATE,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,TheDate ) + 1, 0)))

    END AS LastBizDay,

    DATENAME(dw,CASE

    WHEN DATEPART(weekday,(CONVERT(DATE,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,TheDate ) + 1, 0))))) IN (7) --Saturday

    THEN DATEADD(dd,-1,

    CONVERT(DATE,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,TheDate ) + 1, 0)))

    )

    WHEN DATEPART(weekday,(CONVERT(DATE,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,TheDate ) + 1, 0))))) IN(1) --Sunday

    THEN DATEADD(dd,-2,

    CONVERT(DATE,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,TheDate ) + 1, 0)))

    )

    ELSE CONVERT(DATE,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,TheDate ) + 1, 0)))

    END) AS LastBizDayOfWeek

    FROM ExampleMonths

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • John Mitchell-245523

    SSC Guru

    Points: 148259

    Isn't it as simple as this?

    SELECT DISTINCT

    MAX(TheDate) OVER(PARTITION BY YEAR(MyDate), MONTH(MyDate)) AS LastBizofMonth

    , MyDay

    FROM MyTable

    WHERE MyDay NOT IN ('Saturday', 'Sunday')

    By the way, please don't double-post.

    John

  • Thom A

    SSC Guru

    Points: 98273

    John Mitchell-245523 (11/29/2016)


    Isn't it as simple as this?

    SELECT DISTINCT

    MAX(TheDate) OVER(PARTITION BY YEAR(MyDate), MONTH(MyDate)) AS LastBizofMonth

    , MyDay

    FROM MyTable

    WHERE MyDay NOT IN ('Saturday', 'Sunday')

    By the way, please don't double-post.

    John

    Depends on the OP's definition of a Working Day. For example, I need to omit Public Holiday's as well as weekends. This also includes Observed holidays, such as Christmas Day this year, due to it falling on a Sunday (it is therefore observed on the Tuesday, as that is the next non public holiday weekday).

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • prafullaahirrao

    SSC Enthusiast

    Points: 198

    Thanks John

  • Alan Burstein

    SSC Guru

    Points: 61026

    Using this sample data:

    CREATE TABLE #myTable (dt date UNIQUE NOT NULL);

    WITH E AS (SELECT v FROM (VALUES (1),(1),(1),(1),(1),(1)) t(v))

    INSERT #myTable

    SELECT CAST(DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT 1))-1, '20140101') AS date)

    FROM E a, E b, E c, E d;

    You could do this:

    SELECT [date] = MAX(dt),

    [day] = DATENAME(WEEKDAY,MAX(dt))

    FROM #myTable

    WHERE DATENAME(WEEKDAY,dt) NOT IN ('Saturday', 'Sunday')

    GROUP BY DATEPART(YEAR,dt), DATEPART(MONTH,dt)

    ORDER BY MAX(dt); -- not required, including for readability

    -- Alan Burstein


    Helpful links:Best practices for getting help on SQLServerCentral -- Jeff ModenHow to Post Performance Problems -- Gail ShawNasty fast set-based string manipulation functions:For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)To split strings based on patterns try PatternSplitCMNeed to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8KI cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code.  -- Itzik Ben-Gan 2001

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply