Post reply

Trouble Selecting from Pivot Tbl

  • February 22, 2006 at 2:15 pm

    #167763

    Hi, I have a pivot tbl that looks like this:

     JanFebMarAprMayJunJulAugSepOctNovDec
    Jan1312111098765432
    Feb2131211109876543
    Mar3213121110987654
    Apr4321312111098765
    May5432131211109876
    Jun6543213121110987
    Jul7654321312111098
    Aug8765432131211109
    Sep9876543213121110
    Oct1098765432131211
    Nov1110987654321312
    Dec1211109876543213

    Prgrammatically speaking, my developers will pass (2) parameters: @1stPayDue (i.e JAN) AND @NextPayDue (i.e MAR) and I need to return back '11'

    I tried writing a CASE, but it seemed to go on forever and I was unsure about how to write it w/ variables...Can this be done?  Is there another option for me?  Any assitance is much appreciated...

     


    Aurora

  • February 22, 2006 at 2:32 pm

    #622752

    I would store the data in a table such as:

    Create Table PaymentDate(FirstPayDue char(3), NextPayDue char(3), Number int)

    and then use:

    Select Number from PaymentDate where FirstPayDue = @1stPayDue and NextPayDue = @NextPayDue

  • February 22, 2006 at 2:33 pm

    #622753

    Thinking outside the box, you could also do something like this and not even use a table:

    declare @1stPayDue char(3)

    declare @NextPayDue char(3)

    select @1stPayDue  = 'nov',

            @NextPayDue = 'apr'

    declare @1stDate datetime

    declare @NextDate datetime

    select @1stDate = cast('01 ' + @1stPayDue + ' 2000' as datetime),

           @NextDate = cast('01 ' + @NextPayDue + ' 2000' as datetime)

    if (@NextDate >= @1stDate) begin

      select 13 - datediff(mm, @1stDate, @NextDate)

    end

    else begin

      select datediff(mm, @NextDate, @1stDate) + 1

    end

  • February 22, 2006 at 3:13 pm

    #622763

    Thanks both of you for your options...I tried both and I think that the 'out the box' solution is exactly what I need...

    I adapted it and it works like a charm, SWEET!!!!!!!!!!!!!

     

    THANKS AGAIN!!!!


    Aurora

Viewing 4 posts - 1 through 3 (of 3 total)

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