Need help with getting last and previous value

  • I have a set of data that I would like to get the last value for an account and also the value prior to the last value.

    Account ConsumptionVal TranDate RowID

    1000 400 2010-01-01 1

    1000 300 2010-01-02 2

    1000 356 2010-02-10 3

    1000 600 2010-02-11 4

    2000 100 2010-01-01 5

    3000 401 2010-01-01 6

    3000 400 2010-01-02 7

    Output to new table:

    Account PrevCsmp CurrentCsmp

    1000 356 600

    2000 0 100

    3000 401 400

    Any help would be greatly appreciated! Thanks in advance

  • Something like this ?

    With CteRows

    as

    (

    select Account,ConsumptionVal ,TranDate, RowID,ROW_NUMBER() over(PARTITION by Account order by RowID desc) as Rown

    from <yourtable>

    )

    Select Account,min(case when RowN = 1 then ConsumptionV end),min(case when RowN = 2 then ConsumptionV end)

    from CteRows

    group by Account



    Clear Sky SQL
    My Blog[/url]

  • Hi that helps tremendously thank you.

    How would i modify your logic so that it looks at 2 columns rather than the 1 (account). Here's what i'm trying to say:

    Meter ServiceCode ConsumptionVal TranDate RowID

    1000 AAA 400 2010-01-01 1

    1000 AAA 300 2010-01-02 2

    1000 AAA 356 2010-02-10 3

    1000 BBB 600 2010-02-11 4

    2000 AAA 100 2010-01-01 5

    3000 CCC 401 2010-01-01 6

    3000 CCC 400 2010-01-02 7

    Output to new table:

    Meter SvcCode PrevCsmp CurrentCsmp

    1000 AAA 300 356

    1000 BBB 0 600

    2000 AAA 0 100

    3000 CCC 401 400

  • By fairly simply passing it through and also 'partitioning' on it

    With CteRows

    as

    (

    select Account, ServiceCode,ConsumptionVal ,TranDate, RowID,ROW_NUMBER() over(PARTITION by Account, ServiceCode order by RowID desc) as Rown

    from <yourtable>

    )

    Select Account, ServiceCode,min(case when RowN = 1 then ConsumptionV end),min(case when RowN = 2 then ConsumptionV end)

    from CteRows

    group by Account, ServiceCode



    Clear Sky SQL
    My Blog[/url]

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

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