Oracle SQL how many days has passed since previous order-CodePudding

I have a problem how to calculate the days how many days has passed since previous order.

My code:

select
    order_id,
    order_date
from 
    oe.orders
where customer_id = 838
order by
    order_date desc

The order_id and order_date are like below:

order_id = 1920 & order_date= 25-MAR-19 15.45.38.000000000
order_id = 1618 & order_date= 08-FEB-19 12.51.39.000000000
order_id = 1592 & order_date= 04-FEB-19 07.35.46.000000000 ...

I am new user of sql and no idea how to do it. Thank you for your help!

CodePudding user response：

If you want the differences in days (just the date part) then:

WITH
    tbl AS
        (
            Select 1 "ID", To_Date('25-MAR-19 15.45.38', 'dd-MON-yy hh24:mi:ss') "A_DATE" From Dual Union All
            Select 2 "ID", To_Date('08-FEB-19 12.51.39', 'dd-MON-yy hh24:mi:ss') "A_DATE" From Dual Union All
            Select 3 "ID", To_Date('04-FEB-19 07.35.46', 'dd-MON-yy hh24:mi:ss') "A_DATE" From Dual Union All
            Select 4 "ID", To_Date('28-JAN-19 12.13.10', 'dd-MON-yy hh24:mi:ss') "A_DATE" From Dual 
        )

Select
    ID "ID",
    TRUNC(A_DATE, 'dd') - TRUNC(Nvl(First_Value(A_DATE) OVER (Order By ID Rows Between 1 Preceding And Current Row), A_DATE), 'dd') "DAYS_DIFF"
From
    tbl

ID	DAYS_DIFF
1	0
2	-45
3	-4
4	-7

OR ...

Select
    ID "ID",
    TRUNC(A_DATE, 'dd') - TRUNC(Nvl(Last_Value(A_DATE) OVER (Order By ID Rows Between Current Row And 1 Following ), A_DATE), 'dd') "DAYS_DIFF"
From
    tbl
Order By TRUNC(A_DATE, 'dd')

... result

ID	DAYS_DIFF
4	0
3	7
2	4
1	45

Regards

CodePudding user response：


CREATE TABLE orders
(ORDER_ID, ORDER_DATE) AS
SELECT 3,  TIMESTAMP'2022-10-31 09:54:48' FROM DUAL UNION ALL
SELECT 2,  TIMESTAMP'2022-10-17 19:04:44' FROM DUAL UNION ALL
SELECT 1,  TIMESTAMP'2022-10-08 14:44:23' FROM DUAL 

SELECT order_id, order_date,
order_date - LAG(order_date) OVER (ORDER BY order_id) AS diff
FROM   orders;

ORDER_ID    ORDER_DATE    DIFF
1    08-OCT-22 02.44.23.000000 PM     - 
2    17-OCT-22 07.04.44.000000 PM     000000009 04:20:21.000000000
3    31-OCT-22 09.54.48.000000 AM     000000013 14:50:04.000000000