Post reply

Pivot query using dates between 2 dates

  • December 18, 2015 at 2:18 pm

    #1846885

    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

  • December 18, 2015 at 3:09 pm

    #1846888

    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)
    Intro to Tally Tables and Functions

  • December 20, 2015 at 6:27 am

    #1846971

    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.

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

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