CODEWITHSUNDEEP
sqlpostgresql
kn9
kn9

Reputation: 103

Filtering data by comparing date SQL

I have table with following data :

Order_ID status_update status_date
A Received 01/01/2020
A Pending 01/05/2020
A Processing 01/07/2020
A Delivered 01/15/2020
B Received 02/01/2020
C Received 02/15/2020
C Delivered 02/20/2020
D Received 05/01/2020
D Delivered 05/10/2020

Now, I would like to get full order history between 01/15/2020 to 04/25/2020 with last status update is Delivered.

Result would look like :

Order_ID status_update status_date
A Received 01/01/2020
A Pending 01/05/2020
A Processing 01/07/2020
A Delivered 01/15/2020
C Received 02/15/2020
C Delivered 02/20/2020

How can i write an SQL query to get this result?

Upvotes: 3

Views: 359

Answers (4)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659367

Assuming you want the history for all orders that were delivered between 2020-01-01 and 2020-04-25:

SELECT *
FROM   orders o
WHERE  EXISTS (
   SELECT FROM orders o1
   WHERE  o1.order_id = o.order_id
   AND    o1.status_update = 'Delivered'
   AND    o1.status_date BETWEEN '2020-01-15' AND '2020-04-25'
   )
ORDER  BY order_id, status_date

That includes rows before 2020-01-15, as long as the delivery date is in the time frame. Add another predicate to the outer SELECT to cut off rows before 2020-01-01 (or any date):

AND    o.status_date >= '2020-01-15'

db<>fiddle here

Aside: always use unambiguous ISO format for dates.

Upvotes: 3

Andrei Odegov
Andrei Odegov

Reputation: 3439

Try the following query:

SELECT Order_ID, status_update, status_date
FROM (
  SELECT
    *,
    SUM(CASE status_update WHEN 'Delivered' THEN 1 ELSE 0 END) OVER(
      PARTITION BY Order_ID
    ) AS with_delivery
  FROM test
  WHERE status_date BETWEEN '2020-01-01' and '2020-04-25'
) AS t 
WHERE with_delivery > 0;

The result is as you expect:

+----------+---------------+-------------+
| order_id | status_update | status_date |
+----------+---------------+-------------+
| A        | Received      | 2020-01-01  |
| A        | Pending       | 2020-01-15  |
| A        | Processing    | 2020-01-07  |
| A        | Delivered     | 2020-01-15  |
| C        | Received      | 2020-02-15  |
| C        | Delivered     | 2020-02-20  |
+----------+---------------+-------------+

db<>fiddle

Upvotes: 1

zedfoxus
zedfoxus

Reputation: 37129

You can use something like this:

select * from test a
inner join (
  select distinct order_id from test
  where status_update = 'Delivered'
) b on a.order_id = b.order_id
where status_date between '2020-01-01' and '2020-04-25'

Example: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=fddcc53f80e94c2c1c48f4e557904505

Result:

order_id | status_update | status_date | order_id
:------- | :------------ | :---------- | :-------
A        | Received      | 2020-01-01  | A       
A        | Pending       | 2020-01-15  | A       
A        | Processing    | 2020-01-07  | A       
A        | Delivered     | 2020-01-15  | A       
C        | Received      | 2020-02-15  | C       
C        | Delivered     | 2020-02-20  | C

The same query will work for:

Edit

If you want to get all records for order ID that got delivered between 15th Jan and 25th April, you can do this:

select * from test a
inner join (
  select distinct order_id from test
  where status_update = 'Delivered'
  and status_date between '2020-01-15' and '2020-04-25'
) b on a.order_id = b.order_id

That'll give you the result, I believe, you desire.

Example: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=5e0d226f455068fafa54228837a8313f

Upvotes: 3

The Impaler
The Impaler

Reputation: 48875

You don't mention the specific database you are using, but the query below should work on most databases:

select *
from t
where status_date between date '2020-01-01' and date '2020-04-25'
  and order_id in (
  select order_id
  from t
  where status_update = 'Delivered'
);

Result:

 order_id  status_update  status_date 
 --------- -------------- ----------- 
 A         Received       2020-01-01  
 A         Pending        2020-01-15  
 A         Processing     2020-01-07  
 A         Delivered      2020-01-15  
 C         Received       2020-02-15  
 C         Delivered      2020-02-20

Upvotes: 1

Related Questions