How to use CROSS APPLY in this scenario

debugcn Published at Dev

LCJ

I have a ProductStatus table as listed below. I need to list all products whose latest status is “SU”. Along with that I need to list what was the previous status of this product.

Based on referring various posts, it seems like CROSS APPLY will be suitable for this. I made an attempt as listed below but that didn’t give the expected result.

What is the best way to achieve this in SQL Server 2005 (without using subquery)?

DECLARE @ProductStatus TABLE (ProductStatusID INT,  productCode VARCHAR(5), statusCode VARCHAR(2))

INSERT INTO @ProductStatus

SELECT 1,'10011','RE'  --Recevied
UNION
SELECT 2,'10011','SU'  --Suspended

UNION
SELECT 3,'10012','IT'  -- In Transit
UNION
SELECT 4,'10012','RE' -- Received
UNION
SELECT 10,'10012','PR' -- Produced
UNION
SELECT 12,'10012','SU'  -- Suspended

UNION
SELECT 14,'10013','RE'  -- Recevied

UNION
SELECT 16,'10014','SU'  -- Recevied
UNION
SELECT 18,'10014','RE'  -- Recevied

CROSS APPLY attempt

SELECT *
FROM @ProductStatus P
CROSS APPLY 
(
   SELECT MAX(V.ProductStatusID) as maxVal
   FROM @ProductStatus V
   WHERE V.ProductCode = P.ProductCode
   AND V. ProductStatusID <  P.ProductStatusID 
   GROUP BY V.ProductCode   
)ML
WHERE P.statusCode = 'SU'

EXPECTED RESULT

enter image description here

Gordon Linoff

You can do this with cross apply but I think row_number() is an easier approach:

select ProductCode,
       max(case when seqnum = 1 then statusCode end) as LastStatus,
       max(case when seqnum = 2 then statusCode end) as PrevStatus
from (select p.*,
             row_number() over (partition by ProductCode order by ProductStatusId desc) as seqnum
      from @ProductStatus p
     ) p
group by ProductCode
having max(case when seqnum = 1 then statusCode end) = 'SU';

Collected from the Internet

Please contact [email protected] to delete if infringement.