CODEWITHSUNDEEP
sqloracle-databaseselectplsql
Nagaraj B
Nagaraj B

Reputation: 79

How to subtract dates of two column in sql

Please go through this edited table.

enter image description here

You can assume order_header_key as order no.

I want to get the list of order nos whose current status is 3 and previous status was 2, and the status date(status3)-status date(2) <=3 days for that order

in the following table for order no-1 'date(status 3)' - 'date(status 2) = 20 OCT - 19 OCT which is less than 3 days--> So valid order

but for order no 3 'date(status 3)' - 'date(status 2)' = 30 OCT - 24 OCT which is more than 3 days so invalid order

order no 2 in invalid since the statuses are 3 and 1 , 2 is missing

Upvotes: 3

Views: 297

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Use lag():

select o.*
from (select o.*,
             lag(o.status_date) over (partition by o.order_no order by o.status_date ) as prev_sd,
             lag(o.status) over (partition by o.order_no order by o.status_date) as prev_status
      from orders o
     ) o
where prev_status = 2 and status = 3 and
      (status_date - prev_sd) <= 3;

Upvotes: 3

user5683823
user5683823

Reputation:

Analytic functions (lag() in this case) allow you to avoid joins and/or subqueries, and may (and often will) be much faster.

with
--   begin test data; not part of the solution
     orders ( order_no, status, status_date ) as (
       select 1, 1, to_date('18-OCT-16', 'DD-MON-YY')from dual union all
       select 1, 2, to_date('19-OCT-16', 'DD-MON-YY')from dual union all
       select 1, 3, to_date('20-OCT-16', 'DD-MON-YY')from dual union all
       select 1, 1, to_date('20-OCT-16', 'DD-MON-YY')from dual union all
       select 1, 3, to_date('23-OCT-16', 'DD-MON-YY')from dual union all
       select 1, 2, to_date('24-OCT-16', 'DD-MON-YY')from dual union all
       select 1, 1, to_date('30-OCT-16', 'DD-MON-YY')from dual       
     ),
--   end test data; solution is the word "with" from above, plus the query below     
     prep ( order_no, status, status_date, prev_status, prev_status_date) as (
       select order_no, status, status_date,
              lag(status) over (partition by order_no order by status_date),
              lag(status_date) over (partition by order_no order by status_date)
       from   orders
     )
select order_no
from   prep
where  status = 3 and prev_status = 2 and prev_status_date - status_date <= 3
;


ORDER_NO
--------
       1

Upvotes: 2

Mureinik
Mureinik

Reputation: 311326

Assuming an order can't have more than one entry per order_no/status combination, you could join two subqueries:

SELECT s3.order_no
FROM   (SELECT *
        FROM   orders
        WHERE  status = 3) s3
JOIN   (SELECT *
        FROM   orders
        WHERE  status = 2) s2 ON s3.order_no = s2.order_no AND 
                                 s3.status_date - s3.status_date <= 3

Upvotes: 3

Related Questions