Post reply

Five previous working days

  • February 17, 2016 at 3:16 am

    #324336

    Hi

    I'm struggling to write a code to get five previous working days. I'm trying to get 'Today', 'Yesterday', 'A day before Yesterday', '3 days prior Today' and '4 days prior Today', but the fields should only be Working days. for Yesterday I would minus 1 from getdate(), but I would also have to test if it's not a weekend. Please help.

  • February 17, 2016 at 3:32 am

    #1859208

    hoseam (2/17/2016)

    Hi

    I'm struggling to write a code to get five previous working days. I'm trying to get 'Today', 'Yesterday', 'A day before Yesterday', '3 days prior Today' and '4 days prior Today', but the fields should only be Working days. for Yesterday I would minus 1 from getdate(), but I would also have to test if it's not a weekend. Please help.

    SELECT Today

    FROM (

    SELECT Today = DATEADD(DAY,0-n,CAST(GETDATE() AS DATE))

    FROM (VALUES (0),(1),(2),(3),(4),(5),(6)) t (n)

    ) d

    WHERE DATEDIFF(DAY,-2,Today)%7 > 1

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • February 17, 2016 at 3:40 am

    #1859211

    DECLARE @DATE_VARIABLE DATETIME = GETUTCDATE();

    SELECT *

    FROM ( SELECT CAST(DATEADD(DAY, -[N], @DATE_VARIABLE) AS DATE)

    FROM ( VALUES ( 0), ( 1), ( 2), ( 3), ( 4), ( 5), ( 6) ) [a] ( [N] )

    ) a ( [DATES] )

    WHERE DATEPART(WEEKDAY, CAST([DATES] AS DATETIME) + @@datefirst - 1) < 6;

    That being said. . . define "working days". What about bank holidays, national holidays, or whatever your particular nation has as "extra" days off? Does this need to work across different countries?

    A calendar table is best, with working days marked in.

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • February 17, 2016 at 3:57 am

    #1859212

    Can I get different columns like 'Today', 'Yesterday', 'A day before Yesterday', '3 days prior Today' and '4 days prior Today'?

  • February 17, 2016 at 4:01 am

    #1859213

    Working days being Monday to Friday

  • February 17, 2016 at 4:08 am

    #1859215

    hoseam (2/17/2016)

    Can I get different columns like 'Today', 'Yesterday', 'A day before Yesterday', '3 days prior Today' and '4 days prior Today'?

    SELECT

    [Today] = MAX(CASE WHEN rn = 5 THEN Today END),

    [Yesterday] = MAX(CASE WHEN rn = 4 THEN Today END),

    [2 wd ago] = MAX(CASE WHEN rn = 3 THEN Today END),

    [3 wd ago] = MAX(CASE WHEN rn = 2 THEN Today END),

    [4 wd ago] = MAX(CASE WHEN rn = 1 THEN Today END)

    FROM (

    SELECT Today, rn = ROW_NUMBER() OVER(ORDER BY Today)

    FROM (VALUES (0),(1),(2),(3),(4),(5),(6)) t (n)

    CROSS APPLY (SELECT Today = DATEADD(DAY,0-n,CAST(GETDATE() AS DATE))) x

    WHERE DATEDIFF(DAY,-2,Today)%7 > 1

    ) d

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

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