Reputation: 2577
I'm working with some data in oracle and the data is a slightly incomplete for the calculation I need to do. The table looks like this (customer is not unique):
customer | status_From | status_To | date_Of_Change
What I'm trying to do is get this data:
Customer (grouped by) | status | days in status (sum for customer as status)
Where status will be the date of one row subtracted from the date of the next row. The same status may appear repeatedly, sometimes sequentially, sometimes not. If there is not another row, I'd like to use the current date for the math.
So essentially, it's
select customer,
SUM(CASE WHEN status_From LIKE 'A%' THEN (?Date of next row?) - date_of_change ELSE 0 END) AS DaysAsA,
from Log
GROUP BY customer
Is there a way I can do this in oracle without having to do multiple queries (in coldfusion) and looping through them?
Upvotes: 1
Views: 507
Reputation: 596
Use lead() analytic function to retrive value of the next row:
select
..
,lead(t.status, 1, 0) over(order by t.status) nextStatus
from
table1 t
Upvotes: 2