# 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 dON 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 dON 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!"

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!"

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 dON 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 dON 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!"

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.

• 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!"

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_workdayFROM	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;GOSET ANSI_NULLS ON;GOCREATE 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 ASRETURN	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]    );WITHcte_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_dateFROM cte_tally1000 tWHERE 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_countFROM (    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)