Pivot query using dates between 2 dates

Question

Pivot query using dates between 2 dates

Viewing 3 posts - 16 through 17 (of 17 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

I'm sorry, I've been busy, but I'll try to explain.

The following code is a common way to create a tally table on the fly. It will generate zero reads, so this is great when IO is a problem.

WITH

E(n) AS(

SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)

),

E2(n) AS(

SELECT a.n FROM E a, E b

),

E4(n) AS(

SELECT a.n FROM E2 a, E2 b

),

cteTally(n) AS(

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n

FROM E4

)

SELECT n

FROM cteTally

The code you posted is using a physical tally table which you need to store in your database. It's doing basically the same thing that my code: adding N-1 days to the start date and limiting the number of rows to cover the period of time. The difference is that I used DATEADD instead of basic arithmetic and TOP instead of WHERE. The choice depends on the datatype used and the type of tally table.

I don't need to join my tally table as I'm only generating the columns used in the cross tab. I do join the one with the hours because I need to be sure that I have all the hours. I just used LEFT JOIN instead of RIGHT JOIN.

About the two additional columns, I'm not sure what you mean. Post an example and I'll try to guide you.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

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

ChrisM@Work (12/17/2015)
twin.devil (12/17/2015)
Is there is any limit between two dates? as far as i know cross tab get very slow on reports if the columns list get higher.
you can also check this working on rdlc as well. do perform a POC 1st before finalizing your design.
+1
Good point, TD, I was planning to suggest the same thing. Take a bunch of data and knock up a prototype output, manually, in Excel - shouldn't be too hard.

PIVOT might slow down. Preaggregated CROSSTABs will take just about anything you can throw at them.

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

Tallboy SSCrazy Points: 2455 More actions · Answer 3

Hi,

I've been away for a few days but looking forward to trying out tomorrow. So thanks again everyone. I will no doubt have a few more questions relating to tally's etc in the next few days so will continue to post here.