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:

      Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
    Jan 13 12 11 10 9 8 7 6 5 4 3 2
    Feb 2 13 12 11 10 9 8 7 6 5 4 3
    Mar 3 2 13 12 11 10 9 8 7 6 5 4
    Apr 4 3 2 13 12 11 10 9 8 7 6 5
    May 5 4 3 2 13 12 11 10 9 8 7 6
    Jun 6 5 4 3 2 13 12 11 10 9 8 7
    Jul 7 6 5 4 3 2 13 12 11 10 9 8
    Aug 8 7 6 5 4 3 2 13 12 11 10 9
    Sep 9 8 7 6 5 4 3 2 13 12 11 10
    Oct 10 9 8 7 6 5 4 3 2 13 12 11
    Nov 11 10 9 8 7 6 5 4 3 2 13 12
    Dec 12 11 10 9 8 7 6 5 4 3 2 13

    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