Home Forums SQL Server 2008 SQL Server 2008 - General Logic to break period of time by month RE: Logic to break period of time by month

  • August 25, 2013 at 11:19 am

    #1644726

    mario17 (8/25/2013)

    Thanks, Jeff

    I just started to go into CROSS APPLY, being a JOIN person I'm trying to translate in into join, and still can't make it work as in original CROSS APPLY, join can't recognize n, I think it's the main difference/advantage for APPLY.

    d

    --*CROSS APPLY (SELECT n FROM Tally WHERE n BETWEEN 1 AND period+1) c

    inner join #Tally on N between 1 and Period+1

    --* CROSS APPLY (SELECT [month]=DATEPART(month, a.fromDD+n-1),DD=CAST(a.fromDD+n-1 AS DATE))

    inner join (SELECT [month]=DATEPART(month, a.fromDD+n-1),DD=CAST(a.fromDD+n-1 AS DATE)) z

    on 1 = 1

    Tx

    M

    BWAA-HAA!!! I'm right there with you on that. I used to hate any form of correlated sub-query. Sure, they made for nice encapsulation, easy readability, and sometimes had a performance advantage (Where IN was good for that not to mention the implied "DISTINCT") but they were (are) a bitch to troubleshoot because the code wouldn't work by itself. You couldn't necessarily just highlight the code and click "Execute" to test it.

    I've got some things to do for work first but I'll try to carve out some time to see if I can come up with something that you'll like better when I'm done. I think this can actually be done with a single CROSS JOIN to the "Tally Table" feature, whatever form it takes.

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