CODEWITHSUNDEEP
sql
VMK
VMK

Reputation: 35

SQL - How to select recent record after a specific status

Product table query

Select Product, Quantity, Status, Txn_date  from ProductTransaction

[Table]

1

Required Output Fetch only one immediate 'Received' status record after the recent status 'Ship' for each product

2

I tried so far..but it's wrong

Select p.Product, p.Quantity, p.Status, p.Txn_date 
from ProductTransaction p
inner join 
(
      Select  Product, Status,  max(Txn_date) as LatestDate
    from ProductTransaction 
    where status='Ship'
    Group by Product,Status
) SubMax 
on p.Txn_date > SubMax.LatestDate
and p.Product = SubMax.Product 
and p.Status='Recieved'

wrong result - Don't need all 'Received' status. Only need to fetch older txn_date record for each product

3

Upvotes: 1

Views: 750

Answers (3)

VMK
VMK

Reputation: 35

I think, Meanwhile I have to go with nested sub queries. Please let me know if we can tune up this query.

select pt2.* from (select pt2.*, min(case when status = 'Recieved' then txn_date end) over (partition by product) as min_recieved_date from ( select pt.* from (select pt.*, max(case when status = 'Ship' then txn_date end) over (partition by product) as max_shipped_date from ProductTransaction pt where pt.Product in('P504','P510') ) pt where status = 'Recieved' and txn_date > max_shipped_date )pt2)pt2 where txn_date=pt2.min_recieved_date

Upvotes: 0

Mark Barinstein
Mark Barinstein

Reputation: 12314

Try this:

/*
WITH ProductTransaction (Product, Quantity, Status, Txn_date) AS
(
VALUES
  ('P504', 50, 'Received',     '2021-03-18'::DATE)
, ('P504', 50, 'Transit',      '2021-03-17'::DATE)  
, ('P504', 25, 'Received',     '2021-03-16'::DATE)
, ('P504', 58, 'ShipToStore',  '2021-03-15'::DATE)
, ('P504', 32, 'Ship',         '2021-03-14'::DATE)
, ('P504', 20, 'Active',       '2021-03-12'::DATE)
, ('P504', 30, 'Acknowledged', '2021-03-12'::DATE)
, ('P504', 20, 'Ship',         '2021-03-10'::DATE)
, ('P510', 25, 'Ship',         '2021-03-15'::DATE)
, ('P510', 35, 'Acknowledged', '2021-03-12'::DATE)
, ('P510', 25, 'Received',     '2021-03-12'::DATE)
, ('P510', 25, 'Ship',         '2021-03-10'::DATE)
)
*/
SELECT Product, Quantity, Status, Txn_date
FROM
(
SELECT 
  P.*
, LAG (STATUS) OVER (PARTITION BY PRODUCT ORDER BY TXN_DATE) STATUS_PREV
FROM ProductTransaction P
WHERE STATUS IN ('Received', 'Ship')
)
WHERE STATUS = 'Received' AND STATUS_PREV = 'Ship';

This query returns the result needed.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269823

One method uses window functions

select pt.*
from (select pt.*,
             max(case when status = 'Shipped' then txn_date end) over (partition by product) as max_shipped_date
      from ProductTransaction pt
     ) pt
where status = 'Received' and txn_date > max_shipped_date

Upvotes: 1

Related Questions