July 23, 2020 at 8:32 am
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?
July 23, 2020 at 11:57 am
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...
July 23, 2020 at 12:02 pm
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>
July 27, 2020 at 4:18 pm
This solution is working like a charm. Thanks to all for your wonderfull suggestions. and ideas.
July 27, 2020 at 8:17 pm
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.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.
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
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 28, 2020 at 4:12 am
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.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.
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply