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

Question

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

Viewing 7 posts - 16 through 21 (of 21 total)

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

david.edwards 76768 Ten Centuries Points: 1246 More actions · Answer 1

Jason A. Long wrote:

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.

Thanks Jason,

I do use SQL Prompt (I have become quite embarrassingly dependent on it 😀 )

Thanks Scott also, I'm going to bookmark this whole thread, so I can steal your code also.

One day sometime, I will have the time to get this done properly (or so I keep telling myself to keep my sane). I've done a few sub-optimal things in the past, sometimes knowingly, just due to pressure to "get it done" which grates a little.

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

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 2

david.edwards 76768 wrote:

Thanks Jason,
I do use SQL Prompt (I have become quite embarrassingly dependent on it 😀 )
Thanks Scott also, I'm going to bookmark this whole thread, so I can steal your code also.
One day sometime, I will have the time to get this done properly (or so I keep telling myself to keep my sane). I've done a few sub-optimal things in the past, sometimes knowingly, just due to pressure to "get it done" which grates a little.

Yea... I'm more than a little dependent on myself. The last time I tried using someone else's copy of SSMS, without SQL Prompt, I physically recoiled... as if I'd touched something that shouldn't be touched. 😀

In any case, I have attached (or at least I'm attempting to attach) four snippet files. Just drop them into your snippet folder and you should be good to go. Two are tally code (one is for 10K rows and the other 10M rows) and the other two are calendar (date) versions of the same.

EDIT: Let's all take a moment to appreciate the irony of RedGate owning a forum that prohibits files created by it's own software... FML...

Snippet code pasted below...

This reply was modified 4 years, 4 months ago by Jason A. Long.
This reply was modified 4 years, 4 months ago by Jason A. Long.

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 3

ctally_1E04.sqlpromptsnippet

<?xml version="1.0" encoding="utf-8"?>
<CodeSnippets>
  <CodeSnippet Format="1.0.0">
    <Header>
      <Title>ctally_1E04</Title>
      <Shortcut>ctally_1E04</Shortcut>
      <Description>CTE based Tally Table (10,000 rows)</Description>
      <Author />
      <SnippetTypes>
        <SnippetType>Expansion</SnippetType>
      </SnippetTypes>
    </Header>
    <Snippet>
      <Declarations />
      <Code Language="sql"><![CDATA[WITH 
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_Tally (n) AS (
SELECT $CURSOR$
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM
cte_n2 a CROSS JOIN cte_n2 b-- 10,000
)
SELECT 
t.n
FROM 
cte_Tally t;]]>
    </Snippet>
  </CodeSnippet>
</CodeSnippets>

ccalendar_1E04.sqlpromptsnippet

<?xml version="1.0" encoding="utf-8"?>
<CodeSnippets>
  <CodeSnippet Format="1.0.0">
    <Header>
      <Title>ccalendar_1E04</Title>
      <Shortcut>ccalendar_1E04</Shortcut>
      <Description>CTE based Calendar Table (10,000 rows)</Description>
      <Author />
      <SnippetTypes>
        <SnippetType>Expansion</SnippetType>
      </SnippetTypes>
    </Header>
    <Snippet>
      <Declarations />
      <Code Language="sql"><![CDATA[WITH 
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 $CURSOR$
CONVERT(DATE, DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1, '1900-01-01'))
FROM
cte_n2 a CROSS JOIN cte_n2 b-- 10,000
)
SELECT 
c.dt
FROM
cte_Calendar c;]]>
    </Snippet>
  </CodeSnippet>
</CodeSnippets>

BI_Analyticz Old Hand Points: 330 More actions · Answer 4

This solution is working like a charm. Thanks to all for your wonderfull suggestions. and ideas.

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 5

BI_Analyticz wrote:

This solution is working like a charm. Thanks to all for your wonderfull suggestions. and ideas.

There were several solutions offered. Which one did you go with?

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 6

Jeff Moden wrote:

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.

If the columns are bit data types - you could change the line to this:

- SUM(CAST(d.isWeekend | d.isHoliday AS int))

For the other solutions - I would be careful using tinyint, smallint or even possibly integer (depends on the tally function being used). These could cause a warning in the execution plan for the implicit conversions which may have an affect on the cardinality estimator. Not sure if the warning actually resolves to an issue or not...

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

ScottPletcher SSC Guru Points: 101097 More actions · Answer 7

Jeffrey Williams wrote:

Jeff Moden wrote:
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.
If the columns are bit data types - you could change the line to this:
- SUM(CAST(d.isWeekend | d.isHoliday AS int))
For the other solutions - I would be careful using tinyint, smallint or even possibly integer (depends on the tally function being used). These could cause a warning in the execution plan for the implicit conversions which may have an affect on the cardinality estimator. Not sure if the warning actually resolves to an issue or not...

Perhaps. But it wouldn't affect the COUNT(*) I used, based on the PKs on the table. SQL's stats on that should be perfect, or nearly so, I would think.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".