Calculate aging between two date fields excluding weekends and holidays in SQL

  • Hi Friends,

    I have a table which has DateTime, IsWeekend, IsHoliday fields in my DB.

    I have another table which has the list of tickets logged with columns like

    Select TicketID, Status,CreatedDate,ResolvedDate, etc., From Table_A

    How can I calculate the aging of these tickets excluding the weekends and holidays. I have read so many posts which I feel very advanced for me to understand since they have used hardcoded dates in their example. Can someone help.

    • This topic was modified 6 months, 1 week ago by  BI_Analyticz.
  • Something like this:

    SELECT A.TicketID, A.Status, A.CreatedDate, A.ResolvedDate,
    (SELECT COUNT(*) FROM dbo.Date_Table DT
    WHERE DT.Date >= A.CreatedDate AND DT.Date >= A.ResolvedDate AND
    DT.IsWeekend = 0 AND DT.IsHoliday = 0) AS [TicketAge],
    ...
    FROM dbo.Table_A A

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Hi,

    Please refer below code snippet -

    ;WITH DateTable AS (
    SELECT * FROM (VALUES
    ('2020-07-01',0,0),
    ('2020-07-02',0,0),
    ('2020-07-03',0,1),
    ('2020-07-04',1,0),
    ('2020-07-05',1,0),
    ('2020-07-06',0,0),
    ('2020-07-07',0,0),
    ('2020-07-08',0,0)
    ) AS t(DateValue,isWeekEnd,isHoliday)
    ),
    TicketTable AS (
    SELECT * FROM (VALUES
    ('Ticket1','2020-07-01','2020-07-06'),
    ('Ticket2','2020-07-07','2020-07-08'),
    ('Ticket3','2020-07-07',NULL)
    ) AS t(Ticket,CreateDate,ResolvedDate)
    )
    SELECT t.Ticket,t.CreateDate,t.ResolvedDate,
    DATEDIFF(DAY,t.CreateDate,t.ResolvedDate) - SUM(d.isWeekEnd)-SUM(d.isHoliday) AS TicketAge
    FROM TicketTable AS t INNER JOIN DateTable AS d
    ON d.DateValue BETWEEN t.CreateDate AND COALESCE(t.ResolvedDate,GETDATE())
    GROUP BY t.Ticket,t.CreateDate,t.ResolvedDate

    Anand

  • Anand929 wrote:

    Hi,

    Please refer below code snippet -

    ;WITH DateTable AS (
    SELECT * FROM (VALUES
    ('2020-07-01',0,0),
    ('2020-07-02',0,0),
    ('2020-07-03',0,1),
    ('2020-07-04',1,0),
    ('2020-07-05',1,0),
    ('2020-07-06',0,0),
    ('2020-07-07',0,0),
    ('2020-07-08',0,0)
    ) AS t(DateValue,isWeekEnd,isHoliday)
    ),
    TicketTable AS (
    SELECT * FROM (VALUES
    ('Ticket1','2020-07-01','2020-07-06'),
    ('Ticket2','2020-07-07','2020-07-08'),
    ('Ticket3','2020-07-07',NULL)
    ) AS t(Ticket,CreateDate,ResolvedDate)
    )
    SELECT t.Ticket,t.CreateDate,t.ResolvedDate,
    DATEDIFF(DAY,t.CreateDate,t.ResolvedDate) - SUM(d.isWeekEnd)-SUM(d.isHoliday) AS TicketAge
    FROM TicketTable AS t INNER JOIN DateTable AS d
    ON d.DateValue BETWEEN t.CreateDate AND COALESCE(t.ResolvedDate,GETDATE())
    GROUP BY t.Ticket,t.CreateDate,t.ResolvedDate

    There will be a huge surprise for you if the isWeekEnd and isHoliday columns are of the BIT datatype. 😉  Otherwise, nice job.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • BI_Analyticz wrote:

    Hi Friends,

    I have a table which has DateTime, IsWeekend, IsHoliday fields in my DB.

    I have another table which has the list of tickets logged with columns like

    Select TicketID, Status,CreatedDate,ResolvedDate, etc., From Table_A

    How can I calculate the aging of these tickets excluding the weekends and holidays. I have read so many posts which I feel very advanced for me to understand since they have used hardcoded dates in their example. Can someone help.

    Read one more... read the article at the first link in my signature line below.  😉  It won't take you long to do such a thing and you'll get much better answers more quickly.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden wrote:

    Anand929 wrote:

    Hi,

    Please refer below code snippet -

    ;WITH DateTable AS (
    SELECT * FROM (VALUES
    ('2020-07-01',0,0),
    ('2020-07-02',0,0),
    ('2020-07-03',0,1),
    ('2020-07-04',1,0),
    ('2020-07-05',1,0),
    ('2020-07-06',0,0),
    ('2020-07-07',0,0),
    ('2020-07-08',0,0)
    ) AS t(DateValue,isWeekEnd,isHoliday)
    ),
    TicketTable AS (
    SELECT * FROM (VALUES
    ('Ticket1','2020-07-01','2020-07-06'),
    ('Ticket2','2020-07-07','2020-07-08'),
    ('Ticket3','2020-07-07',NULL)
    ) AS t(Ticket,CreateDate,ResolvedDate)
    )
    SELECT t.Ticket,t.CreateDate,t.ResolvedDate,
    DATEDIFF(DAY,t.CreateDate,t.ResolvedDate) - SUM(d.isWeekEnd)-SUM(d.isHoliday) AS TicketAge
    FROM TicketTable AS t INNER JOIN DateTable AS d
    ON d.DateValue BETWEEN t.CreateDate AND COALESCE(t.ResolvedDate,GETDATE())
    GROUP BY t.Ticket,t.CreateDate,t.ResolvedDate

    There will be a huge surprise for you if the isWeekEnd and isHoliday columns are of the BIT datatype. 😉  Otherwise, nice job.

    Is it really impossible for something to be a weekend and a holiday?  I know normally the holiday moves, but I wouldn't do SUMs counting on only one of them to ever be set.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • ScottPletcher wrote:

    Jeff Moden wrote:

    Anand929 wrote:

    Hi,

    Please refer below code snippet -

    ;WITH DateTable AS (
    SELECT * FROM (VALUES
    ('2020-07-01',0,0),
    ('2020-07-02',0,0),
    ('2020-07-03',0,1),
    ('2020-07-04',1,0),
    ('2020-07-05',1,0),
    ('2020-07-06',0,0),
    ('2020-07-07',0,0),
    ('2020-07-08',0,0)
    ) AS t(DateValue,isWeekEnd,isHoliday)
    ),
    TicketTable AS (
    SELECT * FROM (VALUES
    ('Ticket1','2020-07-01','2020-07-06'),
    ('Ticket2','2020-07-07','2020-07-08'),
    ('Ticket3','2020-07-07',NULL)
    ) AS t(Ticket,CreateDate,ResolvedDate)
    )
    SELECT t.Ticket,t.CreateDate,t.ResolvedDate,
    DATEDIFF(DAY,t.CreateDate,t.ResolvedDate) - SUM(d.isWeekEnd)-SUM(d.isHoliday) AS TicketAge
    FROM TicketTable AS t INNER JOIN DateTable AS d
    ON d.DateValue BETWEEN t.CreateDate AND COALESCE(t.ResolvedDate,GETDATE())
    GROUP BY t.Ticket,t.CreateDate,t.ResolvedDate

    There will be a huge surprise for you if the isWeekEnd and isHoliday columns are of the BIT datatype. 😉  Otherwise, nice job.

    Is it really impossible for something to be a weekend and a holiday?  I know normally the holiday moves, but I wouldn't do SUMs counting on only one of them to ever be set.

    Totally agreed but if the columns are the BIT datatype, the SUMs won't work anyway.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Not a complete answer to the OP's question, because it doesn't need the "IsWeekend" field or take into account Holidays, but I thought I'd join the discussion with this, if that's ok.

    I keep meaning to revisit this Scalar Function (don't shoot me) I knocked up ages ago to calculate durations in Working Days.

    It's not caused any performance issues in the DB I use it in, so has dropped down the priority list. It's used exclusively in SELECT statements.

    I do intend to include reference to a "public holidays" table too, at some point.  I'm just convinced it's a bit too "not-best-practice" and would appreciate an outside opinion. I have no peers at work to bounce  questions like this off, unfortunately.

    :EDIT: Apologies, if I originally nicked it from somewhere, I usually comment with a URL if I do that and there was none.

    "Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
    — Samuel Johnson
    I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?

  • I keep meaning to revisit this Scalar Function (don't shoot me) I knocked up ages ago to calculate durations in Working Days.

    shadow fight 3

    • This reply was modified 2 weeks, 4 days ago by  Sang15512.
    • This reply was modified 2 weeks, 4 days ago by  Sang15512.
  • Agree with you, both scenarios are valid and need to be handled. I just wanted to share an approach that can be used as a sample for real problem. But still thanks for pointing that out and also for appreciating the effort. 🙂

    • This reply was modified 6 months ago by  Anand929.

    Anand

  • Enquiring minds want to know... where the hell did the OP go?

    And, yeah... I'm a poet and don't know it. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • This is a solution that Jeff, myself and a few other worked out a few years ago. The idea of using a "date code" and a two seek operations rather than doing an aggregation was Jeff's idea... I wish I could take credit but I can't.

    In any case it is, by a wide margin, the fastest "working days function" that I am aware of.

    USE tempdb;	-- using tempdb for demo purposes only!!!
    GO

    --======================================================================================================================================
    -- Start by creating a "workday_function_calendar" table, holding the date codes for 2020.

    IF OBJECT_ID('tempdb.dbo.workday_function_calendar', 'U') IS NOT NULL
    BEGIN DROP TABLE dbo.workday_function_calendar; END;

    CREATE TABLE dbo.workday_function_calendar (
    date_code int NOT NULL,
    workday_num int NOT NULL,
    is_workday tinyint NOT NULL, -- this is 1 or 0 like a bit but we need it for math so using tinyint instead.
    CONSTRAINT pk_workday_function_calendar PRIMARY KEY CLUSTERED(date_code)
    );

    WITH -- generate all dates between '2020-01-01' and '2020-12-31'
    cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)), -- 10
    cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b), -- 100
    cte_Calendar (dt) AS (
    SELECT TOP (366)
    CONVERT(DATE, DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1, '2020-01-01'))
    FROM
    cte_n2 a CROSS JOIN cte_n2 b -- 10,000
    ),
    cte_holiday AS (
    SELECT
    h.holiday_date
    FROM
    ( VALUES ('2020-07-04'),('2020-01-01'),('2020-05-25'),('2020-07-03'),('2020-09-07'),('2020-11-26'),('2020-11-27'),('2020-12-25') ) h (holiday_date)
    )
    INSERT dbo.workday_function_calendar (date_code, workday_num, is_workday)
    SELECT
    date_code = DATEDIFF(DAY, '1900-01-01', c.dt),
    workday_num = SUM(iwd.is_workday) OVER (ORDER BY c.dt ROWS UNBOUNDED PRECEDING),
    iwd.is_workday
    FROM
    cte_Calendar c
    LEFT JOIN cte_holiday h
    ON c.dt = h.holiday_date
    CROSS APPLY ( VALUES (CASE WHEN DATEPART(WEEKDAY, c.dt) BETWEEN 2 AND 6 AND h.holiday_date IS NULL THEN 1 ELSE 0 END) ) iwd (is_workday);

    --======================================================================================================================================
    -- Create the code for the bo.GetWorkingDays inline table valued function.

    SET QUOTED_IDENTIFIER ON;
    GO
    SET ANSI_NULLS ON;
    GO
    CREATE FUNCTION dbo.GetWorkingDays
    /* ===================================================================
    07/22/2020 JL, Created: Calculates the number of working days between two dates.
    =================================================================== */
    --===== Define I/O parameters
    (
    @beg_date datetime,
    @end_date datetime;
    )
    RETURNS TABLE WITH SCHEMABINDING AS
    RETURN

    SELECT
    working_days =
    e.workday_num
    - b.workday_num
    + CASE
    WHEN @beg_date > @end_date THEN NULL -- returns a NULL value if the Eend_date preceeds the @end_date
    WHEN CONVERT(TIME(7), @beg_date) = '00:00:00.0000000' THEN b.is_workday
    ELSE 0
    END

    FROM
    (VALUES (DATEDIFF(DAY, '1900-01-01', @beg_date), DATEDIFF(DAY, '1900-01-01', @end_date)) ) be (beg_dt, end_dt)
    CROSS APPLY (
    SELECT
    wfc.workday_num, wfc.is_workday
    FROM
    dbo.workday_function_calendar wfc
    WHERE
    wfc.date_code = be.beg_dt
    ) b
    CROSS APPLY (
    SELECT
    wfc.workday_num
    FROM
    dbo.workday_function_calendar wfc
    WHERE
    wfc.date_code = be.end_dt
    ) e;
    GO

     

  • Jason, Jeff I like that 🙂

    I need to use tally tables more, ever since I found out about them, but have been dallying with my tallying, as we're getting all poetic.

    "Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
    — Samuel Johnson
    I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?

  • david.edwards 76768 wrote:

    Jason, Jeff I like that 🙂

    I need to use tally tables more, ever since I found out about them, but have been dallying with my tallying, as we're getting all poetic.

    In this case, I'm only using the tally to build the calendar table. It has nothing to do with the function itself.

    And yes, tally tables, tally functions or, in the case, inline tally cte are great tools to have in your belt. I keep hoping that Microsoft will eventually add a "system tally table" that would allow us to directly access that mythical " internal table of constants" we see in constant scan nodes.

    If you use RedGate SQL Prompt, I can share a few snippets that saves a lot of typing when I use that inline code.

  • I have separate work_day and nonwork_day tables, for assorted reasons, including that I think it is simpler and very efficient (esp. when I need to see only nonwork days).  Code below, first table creates/loads and then the working days calc.

    I admit that we don't include time directly in our date calcs.  If we need to, we adjust the dates passed into the COUNT() to account for times that exceed a limit, but even that is very rare in our case; we may just be lucky in that we don't have to worry about it.

    Edit: Yes, in real life the nonwork_dates table has a few additional columns, including an encoded tinyint value for why it's a nonwork date.

    /*load the date tables*/
    CREATE TABLE dbo.nonwork_dates (
    nonwork_date date NOT NULL,
    CONSTRAINT nonwork_dates__PK PRIMARY KEY CLUSTERED ( nonwork_date ) WITH ( FILLFACTOR = 100 ) ON [PRIMARY]
    )
    INSERT INTO dbo.nonwork_dates VALUES
    ('2020-01-01'),('2020-05-25'),('2020-07-03'),('2020-09-07'),
    ('2020-11-26'),('2020-11-27'),('2020-12-25')

    CREATE TABLE dbo.work_dates (
    work_date date NOT NULL,
    CONSTRAINT work_dates__PK PRIMARY KEY CLUSTERED ( work_date ) WITH ( FILLFACTOR = 100 ) ON [PRIMARY]
    )

    ;WITH
    cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
    ),
    cte_tally1000 AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number
    FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2 CROSS JOIN cte_tally10 c3
    )
    INSERT INTO dbo.work_dates ( work_date )
    SELECT DATEADD(DAY, t.number - 1, '20200101') AS work_date
    FROM cte_tally1000 t
    WHERE t.number BETWEEN 1 AND 366 AND
    DATEDIFF(DAY, 0, DATEADD(DAY, t.number - 1, '20200101')) % 7 <= 4 AND
    NOT EXISTS(SELECT 1 FROM dbo.nonwork_dates nd WHERE nd.nonwork_date = DATEADD(DAY, t.number - 1, '20200101'))
    ORDER BY 1


    /*actual query to calc work days*/
    /*of course it could be moved to a function, but I don't really see the need for that*/
    SELECT start_date, end_date,
    (SELECT COUNT(*)
    FROM dbo.work_dates
    WHERE work_date >= start_date AND work_date <= end_date) AS work_days_count
    FROM (
    SELECT CAST('20200101' AS date) AS start_date, CAST('20200105' AS date) AS end_date
    UNION ALL
    SELECT '20200101', '20201231'
    ) AS test_dates

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

Viewing 15 posts - 1 through 15 (of 22 total)

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