I have a table that has a general structure of the following:
customer charge_type charge_amt location datetime
abcd 001 100.00 A123 7/1/2014 00:00:00
abcd 002 50.00 B123 7/2/2014 00:00:00
I want to end up with the following data:
customer charge_amt location
abcd 150.00 B123
That is, I want the customer, the total charge_amt, and the final location based on the datetime. I'm currently doing it in two steps:
customer and charge_amt
customer and location
and then joining them.
Is there a better way? Faster way? I'm working with ~1,000,000 records, so not a HUGE dataset, but there are other steps involved in this.
So, any advice?
You need a Windowed Aggregate Function:
sum(charge_amt) -- total amout
over (partition by customer),
qualify row_number() -- latest row
over (partition by customer
order by datetime desc) = 1
Thanks! I'd used the qualify for the location table, but didn't know I could do the sum() over(). That helped a lot (both with this and in the future!)