The "Numbers" or "Tally" Table: What it is and how it replaces a loop

Question

The "Numbers" or "Tally" Table: What it is and how it replaces a loop

Viewing 15 posts - 346 through 360 (of 511 total)

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

We're good now. Steve Jones and one of the Developers were able to recover the article in it's entirety. None of us know what happened for sure.

Thank you for your patience.

--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.

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

ken.trock SSCertifiable Points: 5147 More actions · Answer 2

I just read this article in full today. This is really useful! In one of Jeff's examples he uses a right join to the tally table to get a list of all dates within a range so that he can output a 0 total for certain entities when there's no row for it. It's essentially this snippet.

SELECT t.N-1+@DateStart AS ShippedDate FROM dbo.Tally t WHERE t.N-1+@DateStart <= @DateEnd

We do some half hour reporting around here. I tried a quick tweak of it to get every half hour interval within a date range.

DECLARE @DateStart DATETIME

DECLARE @DateEnd DATETIME

SELECT @DateStart = '7/1/11', @DateEnd = '7/20/11'

SELECT dateadd(mi, 30, dateadd(dd, t.N - 1, @DateStart)) AS ShippedDate FROM master..Tally t

WHERE dateadd(mi, 30, dateadd(dd, t.N - 1, @DateStart)) <= @DateEnd

Unfortunately this only gets the 1st half hour for each date (the 00:30 time). How might you go about getting all half hour clicks? Hopefully it doesn't need a cursor :w00t:

Ken

shannonjk SSCarpal Tunnel Points: 4286 More actions · Answer 3

Like this?

DECLARE @DateStart DATETIME = '7/1/11'

, @DateEnd DATETIME = '7/20/11'

DECLARE @Daydiff int = Datediff(dd, @DateStart, @DateEnd) + 1

SELECT DATEADD(mi, Mi.N * 30, dateadd(d, D.N -1 , @DateStart)) FROM dbo.Tally AS D, dbo.Tally Mi

WHERE D.N <= @Daydiff AND Mi.N <= 48

ORDER BY 1

Link to my blog http://notyelf.com/

shannonjk SSCarpal Tunnel Points: 4286 More actions · Answer 4

Woops, one more try. This gives you 07/11/2011 00:00:00 to 07/21/2011 23:30:00

DECLARE @DateStart DATETIME = '7/1/11'

, @DateEnd DATETIME = '7/20/11'

DECLARE @Daydiff int = Datediff(dd, @DateStart, @DateEnd) + 1

SELECT DATEADD(mi, (Mi.N - 1) * 30, dateadd(d, D.N -1 , @DateStart))

FROM dbo.Tally AS D, dbo.Tally Mi

WHERE D.N <= @Daydiff AND Mi.N <= 48

ORDER BY 1

Link to my blog http://notyelf.com/

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

Nicely done, Shannon. I'd like to make one lil' teeny suggestion that will make your code absolutely fly. Instead of a very costly sort based on the unindexed results, try a dual sort on the clustered indexes of the Tally tables. If you look at the execution plan, the SORT icon actually goes away because the clustered indexes are sorted in the correct order. All the ORDER BY on the following code does is to guarantee the sort if anything ever goes wrong with the clustered indexes.

SELECT DATEADD(mi, (mi.N - 1) * 30, DATEADD(d, d.N -1 , @DateStart))

FROM dbo.Tally d, dbo.Tally mi

WHERE d.N <= @Daydiff AND mi.N <= 48

ORDER BY d.N, mi.N

--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.

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

Ken,

Thanks for stopping by. I'm glad you enjoyed the article and I certainly appreciate the feedback. Please see the note I left for Shannon above. It's a performance tweek I made on her original code.

--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.

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

David Burrows SSC Guru Points: 65142 More actions · Answer 7

How about a simpler approach

SELECT DATEADD(minute, (t.N - 1) * 30, @DateStart)

FROM dbo.Tally t

WHERE t.N BETWEEN 1 AND (DATEDIFF(day, @DateStart, @DateEnd) + 1) * 48

ORDER BY t.N

Far away is close at hand in the images of elsewhere.
Anon.

ken.trock SSCertifiable Points: 5147 More actions · Answer 8

Oh sure thing. Thanks to everyone for their solutions.

Ken

shannonjk SSCarpal Tunnel Points: 4286 More actions · Answer 9

Jeff, Thanks for the tip! I have actually been focusing on that exact thing with indexes lately :). On a side note, I am actually a 'him' not a 'her' 😉

David,

Great work simplifying that even more!

Link to my blog http://notyelf.com/

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

David Burrows (7/21/2011)
How about a simpler approach
SELECT DATEADD(minute, (t.N - 1) * 30, @DateStart)
FROM dbo.Tally t
WHERE t.N BETWEEN 1 AND (DATEDIFF(day, @DateStart, @DateEnd) + 1) * 48
ORDER BY t.N

Good idea but the dual 11,000 row Tally Table has 30 years of "capability"... doing it directly as you have limits the functionality to 299.16666666667 days. 🙂 You could Cross-Join the Tally table to get more, but it actually takes 65 milliseconds per year longer.

--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.

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

shannonjk (7/21/2011)
Jeff, Thanks for the tip! I have actually been focusing on that exact thing with indexes lately :). On a side note, I am actually a 'him' not a 'her' 😉
David,
Great work simplifying that even more!

Ah... Ok. Where does the "shannonjk" handle come from?

--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.

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

shannonjk SSCarpal Tunnel Points: 4286 More actions · Answer 12

Jeff Moden (7/21/2011)
shannonjk (7/21/2011)
Jeff, Thanks for the tip! I have actually been focusing on that exact thing with indexes lately :). On a side note, I am actually a 'him' not a 'her' 😉
David,
Great work simplifying that even more!
Ah... Ok. Where does the "shannonjk" handle come from?

My first name, and the initials of my middle and last name...It's an Irish name 😀

Link to my blog http://notyelf.com/

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 13

Got it! Lynn Pettis has a similar problem. Apologies for the typical American mistake on the name. :blush:

--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.

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

shannonjk SSCarpal Tunnel Points: 4286 More actions · Answer 14

Eh no worries! Being a DBA rarely people actually see my face so I get it all the time with the companies I work with :-D. Ironically enough I just did the same thing with a new employee named Jamie who also happens to be male :-D. The age of non-physical presence communication has some flaws apparently!

Link to my blog http://notyelf.com/

David Burrows SSC Guru Points: 65142 More actions · Answer 15

Jeff Moden (7/21/2011)[hrGood idea but the dual 11,000 row Tally Table has 30 years of "capability"... doing it directly as you have limits the functionality to 299.16666666667 days. 🙂 You could Cross-Join the Tally table to get more, but it actually takes 65 milliseconds per year longer.

Good spot Jeff 🙂

Could increase the tally table which, in my tests for 30 years, would make marginal difference in query time but 50% less cpu :crazy:

One of those 'it depends' issues depending on the max date range 😀

Far away is close at hand in the images of elsewhere.
Anon.