Post reply

increment months for two dates combination as a new label column

  • October 25, 2018 at 9:29 am

    #2011174

    hegdesuchi - Monday, October 22, 2018 1:22 PM

    I have to calculate a new Label column for each combination of Start Date and End Date.

    If start Date is Jan-18 then if End Date is Jan-18, then Label column is Month 1,
    If Start Date is Jan-18 then if end Date is Feb-18 then Label column is Month 2,
    If Start Date is Feb-18 then if end date is Feb-18, then Label columns is Month 1,
    If Start Date is Feb-18 then if end date is March-18, then Label columns is Month 2 etc etc...

    StartDate EndDate LabelColumn
    Jan-18  Jan-18 Month 1
    Jan-18  Feb-18 Month 2
    Jan-18  Feb-18 Month 2
    Jan-18  Mar-18 Month 3
    Jan-18  Apr-18 Month 4
    Feb-18  Feb-18 Month 1
    Feb-18  Mar-18 Month 2
    Feb-18  Mar-18 Month 2
    Feb-18  Apr-18 Month 3

    how do we do it using CTE?

    Just to be sure that there's some understanding here...

    If the data you posted above already exists in a table, there is no need for a CTE.  Just use the formula that Jonathon posted in your SELECT list and you're done.

    If you mean to generate not only the "LabelColumn" but the Start and End dates, as well, that's a different story that requires a CROSS JOIN with criteria to change it to a "TRIANGULAR JOIN" to generate all possibilities.  If that's the real case here, post back and we'll show you how to do that.

    And, last but not least, do you actually understand the formula that Jonathan used and what the underlying dates that formula will "generate" actually are and why?

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

Viewing post 16 (of 16 total)

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