July 20, 2020 at 1:29 pm
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.
July 20, 2020 at 2:35 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
July 21, 2020 at 7:50 am
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
July 22, 2020 at 12:15 am
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
Change is inevitable... Change for the better is not.
July 22, 2020 at 12:17 am
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
Change is inevitable... Change for the better is not.
July 22, 2020 at 3:39 am
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.ResolvedDateThere 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) A socialist is someone who will give you the shirt off *someone else's* back.
July 22, 2020 at 5:49 am
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.ResolvedDateThere 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
Change is inevitable... Change for the better is not.
July 22, 2020 at 8:52 am
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?
July 22, 2020 at 9:05 am
I keep meaning to revisit this Scalar Function (don't shoot me) I knocked up ages ago to calculate durations in Working Days.
July 22, 2020 at 3:34 pm
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. 🙂
Anand
July 22, 2020 at 8:01 pm
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
Change is inevitable... Change for the better is not.
July 22, 2020 at 8:06 pm
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
July 22, 2020 at 8:55 pm
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?
July 22, 2020 at 9:16 pm
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.
July 23, 2020 at 4:12 am
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply