Reputation: 103
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
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
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 |
+----------+---------------+-------------+
Upvotes: 1
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:
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
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