I have a table that records when tasks were completed. Tasks belong to a workflow, but in this example I'm just trying to get the LAG working.
I would like to find information about how long each task takes.
I've tried:
select
completed_date,
lag(completed_date) over (order by id) prevrow,
prevrow - completed_date
from
task_complete
where workflow_id = 1
But this results in an error. Is there a way to calculate the difference between the current row and previous row?
According to the Oracle documentation:
Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.
This means that you can't use the results of an analytic function in the current level of the query.
There are two solutions to this. You could either include the LAG function as often as necessary in the select list. Notice that this is what you would do even with a normal function because you can't refer to the column alias (prevrow) elsewhere in the same select list anyway.
select
completed_date,
lag(completed_date) over (order by id) as prevrow,
lag(completed_date) over (order by id) - completed_date as date_diff
from
task_complete
where workflow_id = 1
OR you can use a subquery to get the results:
select
completed_date,
prevrow,
prevrow - completed_date as date_diff
from (
select
completed_date,
lag(completed_date) over (order by id) as prevrow
from
task_complete
where workflow_id = 1
)
You are very close, try moving your difference calculation to the analytic function column:
select
completed_date,
lag(completed_date) over (order by id) - completed_date diff,
from
task_complete
where workflow_id = 1;
Try this:
SELECT completed_date
, prevrow - completed_date thediff
FROM (select completed_date,
lag(completed_date) over (order by id) prevrow
from task_complete
where workflow_id = 1);
