Avatar of pm-archtect
pm-archtect
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Oracle Date Subtraction from previous record

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
Oracle Database

Avatar of undefined
Last Comment
pm-archtect

8/22/2022 - Mon
Geert G

use the LEAD aggregate

select difference/(datereceived - prev_datereceived) from (
select *, lead(datereceived) over (order by datereceived) prev_datereceived
from yourtable)
Geert G

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
pm-archtect

ASKER
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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
paquicuba

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
/
awking00

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
awking00

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).
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
pm-archtect

ASKER
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 G

>> 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
awking00

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
awking00

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
pm-archtect

ASKER
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?
ASKER CERTIFIED SOLUTION
awking00

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
pm-archtect

ASKER
Thank you for your prompt responses - great help
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.