Oracle Date Subtraction from previous record

pm-archtect
pm-archtect used Ask the Experts™
on
Hi

I need to perform a calculation.  I need to subtract a date in the current row from the previous row and use the result in a division.

I have another calculated field that that is the DIFFERENCE  (in kilometres).

The new value is calculated as follows:

DIFFERENCE / (This rows DATERECEIVED - Previous rows DATERECEIVED)

Please see the attached sample data
Date-Subtractions---Sample-Data.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Geert GOracle dba
Top Expert 2009

Commented:
use the LEAD aggregate

select difference/(datereceived - prev_datereceived) from (
select *, lead(datereceived) over (order by datereceived) prev_datereceived
from yourtable)
Geert GOracle dba
Top Expert 2009

Commented:
you might want to skip the first row, as it hasn't got a predecessor

select difference/(datereceived - prev_datereceived) from (
select *, lead(datereceived) over (order by datereceived) prev_datereceived
from yourtable)
where prev_datereceived is not null

Author

Commented:
I get "Inconsistent Data Types" when I try to do the date subtraction.  I would like the result to be a number such as  1.11 (of a day) which would result in my above calculation being something like:

Difference / (Current Row Date - Previous Row Date) would look like this:

500 / 1.11

How would I convert the result of the following into this kind of number do you know please? i.e.

24-05-2012 22:30.22 - 23-05-2012 19:27.00

The result of which is just over 1 day
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

You're working with timestamps, so the result of subtracting one from the other is an interval. Cast the timestamps as dates:

select
cast(timestamp'2012-05-24 22:30:22' as date)
-cast(timestamp'2012-05-23 19:27:00' as date)
from dual
/
awking00Information Technology Specialist

Commented:
It appears to me that you really want to use lag();
SQL> select * from yourtable;

DATERECEI       UNIT KILOMETRAGE DIFFERENCE
--------- ---------- ----------- ----------
23-MAY-12        114        4000
24-MAY-12        114        4500        500
27-MAY-12        114        5000        500
28-MAY-12        114        6500       1500
29-MAY-12        114        7500       1000
30-MAY-12        114        8000        500
01-JUN-12        114        9000       1000

SQL> select datereceived, unit, kilometrage,difference,
  2  difference/(datereceived - lag(datereceived) over (partition by unit order
by datereceived)) km_per_day
  3  from yourtable;

DATERECEI       UNIT KILOMETRAGE DIFFERENCE KM_PER_DAY
--------- ---------- ----------- ---------- ----------
23-MAY-12        114        4000
24-MAY-12        114        4500        500 571.428571
27-MAY-12        114        5000        500 157.514767
28-MAY-12        114        6500       1500 1625.28217
29-MAY-12        114        7500       1000 1058.82353
30-MAY-12        114        8000        500 443.076923
01-JUN-12        114        9000       1000 498.442368
awking00Information Technology Specialist

Commented:
Note - I partitioned by unit since I thought you might want this calculation based on each unit. If that's the case, you would need to only calculate where the unit = the lag(unit).

Author

Commented:
Hi Awking00

Because the difference field is also derived on the fly, I've had to modify your example slightly as follows (this inlcudes my calculation to create the "DIFFERENCE" column as I was unable to reference it directly (or I don't know how to):

distance - LAG (distance, 1, 0) OVER (ORDER BY unitid, datereceived) AS difference,
(distance - LAG (distance, 1, 0) OVER (ORDER BY unitid, datereceived))/((datereceived - lag(datereceived) over (partition by unitid order by datereceived))) AS km_per_day

Will this effect the results do you think or is there a more elegant way to express this please?
Geert GOracle dba
Top Expert 2009

Commented:
>> awking00, yeah i always mess up which is which LAG or LEAD

use a subquery or a CTE (common table expression)

with cte as
  (select
     unitid,
     distance d, lag(distance, 1, 0) over (order by unitid, datereceived) nd,
     date_received dtr, lag(date_received) over (partition by unitid order by datereceived) ndtr
  from yourtable)
select
  unitid,  
  dtr date_received,
  d - nd difference,
  (d - nd)/(dtr - ndtr) AS km_per_day
from cte
awking00Information Technology Specialist

Commented:
Using distance - lag(distance) ... as a replacement for difference should not affect the results and is probably as "elegant" as it will get. However, "order by unitid, datereceived"
will create different results than "partitioned by unitid order by datereceived" as shown below (note - my test table has already performed the difference calculation):
SQL> select * from yourtable;

DATERECEI       UNIT KILOMETRAGE DIFFERENCE
--------- ---------- ----------- ----------
23-MAY-12        114        4000
24-MAY-12        114        4500        500
27-MAY-12        114        5000        500
28-MAY-12        114        6500       1500
29-MAY-12        114        7500       1000
30-MAY-12        114        8000        500
01-JUN-12        114        9000       1000
28-MAY-12        115        6500       1500
29-MAY-12        115        7500       1000
30-MAY-12        115        8000        500
01-JUN-12        115        9000       1000
SQL> select datereceived, unit, kilometrage,difference,
  2  difference/(datereceived - lag(datereceived) over (partition by unit order
by datereceived
)) km_per_day
  3  from yourtable;

DATERECEI       UNIT KILOMETRAGE DIFFERENCE KM_PER_DAY
--------- ---------- ----------- ---------- ----------
23-MAY-12        114        4000
24-MAY-12        114        4500        500 571.428571
27-MAY-12        114        5000        500 157.514767
28-MAY-12        114        6500       1500 1625.28217
29-MAY-12        114        7500       1000 1058.82353
30-MAY-12        114        8000        500 443.076923
01-JUN-12        114        9000       1000 498.442368
28-MAY-12        115        6500       150029-MAY-12        115        7500       1000 1058.82353
30-MAY-12        115        8000        500 443.076923
01-JUN-12        115        9000       1000 498.442368
SQL> select datereceived, unit, kilometrage,difference,
  2  difference/(datereceived - lag(datereceived) over (order by unit, daterecei
ved
)) km_per_day
  3  from yourtable;

DATERECEI       UNIT KILOMETRAGE DIFFERENCE KM_PER_DAY
--------- ---------- ----------- ---------- ----------
23-MAY-12        114        4000
24-MAY-12        114        4500        500 571.428571
27-MAY-12        114        5000        500 157.514767
28-MAY-12        114        6500       1500 1625.28217
29-MAY-12        114        7500       1000 1058.82353
30-MAY-12        114        8000        500 443.076923
01-JUN-12        114        9000       1000 498.442368
28-MAY-12        115        6500       1500 -367.72217
29-MAY-12        115        7500       1000 1058.82353
30-MAY-12        115        8000        500 443.076923
01-JUN-12        115        9000       1000 498.442368
awking00Information Technology Specialist

Commented:
A copy and paste distorted the display of the partition by unit query, it really looks like this:
SQL> select datereceived, unit, kilometrage,difference,
  2  difference/(datereceived - lag(datereceived) over (partition by unit order
by datereceived)) km_per_day
  3  from yourtable;

DATERECEI       UNIT KILOMETRAGE DIFFERENCE KM_PER_DAY
--------- ---------- ----------- ---------- ----------
23-MAY-12        114        4000
24-MAY-12        114        4500        500 571.428571
27-MAY-12        114        5000        500 157.514767
28-MAY-12        114        6500       1500 1625.28217
29-MAY-12        114        7500       1000 1058.82353
30-MAY-12        114        8000        500 443.076923
01-JUN-12        114        9000       1000 498.442368
28-MAY-12        115        6500       1500
29-MAY-12        115        7500       1000 1058.82353
30-MAY-12        115        8000        500 443.076923
01-JUN-12        115        9000       1000 498.442368

Author

Commented:
Forgive me, I'm reading your example above and can no longer see the wood for the trees. Which are you suggesting is the correct path to follow please?
Information Technology Specialist
Commented:
The correct path is to go ahead and substitute "distance - lag(distance) ..." for the difference and partition by unitid order by datereceived.
select datereceived, unitid, kilometrage,
distance - lag(distiance) over (partition by unitid order by datereceived) difference,
distance - lag(distiance) over (partition by unitid order by datereceived)
/(datereceived - lag(datereceived) over (partition by unitid order by datereceived)) km_per_day
from yourtable;

Author

Commented:
Thank you for your prompt responses - great help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial