May 7, 2010 at 7:55 am
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
May 7, 2010 at 8:06 am
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
May 7, 2010 at 8:37 am
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
May 7, 2010 at 8:40 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply