Patrick
Patrick

Reputation: 2577

Calculate sum based on previous or next row

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

Answers (1)

Slava N.
Slava N.

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

Related Questions