Case with Over Partition by - Not giving results

Transact-SQL
1

Hi.. I'm trying to create a case statement so that it does only row_number for the valid partition...

CASE WHEN (COALESCE(UserFlg,ProdFlg) = 1 OR ValidProdFlg=0) THEN 0 ELSE ROW_NUMBER() OVER(PARTITION BY CustID,ProdID ORDER BY ProdCreatedDt ASC ,ProdSeqNbr ASC) END AS CustProdFILO,
CASE WHEN (COALESCE(UserFlg,ProdFlg) = 1 OR ValidProdFlg=0) THEN 0 ELSE ROW_NUMBER() OVER(PARTITION BY CustID,ProdID ORDER BY ProdCreatedDt DESC ,ProdSeqNbr DESC) END AS CustProdLIFO,
CASE WHEN (COALESCE(UserFlg,ProdFlg) = 1 OR ValidProdFlg=0) THEN 0 ELSE ROW_NUMBER() OVER(PARTITION BY ProdID ORDER BY ProdCreatedDt ASC ,ProdSeqNbr ASC) END AS ProdFILO,
CASE WHEN (COALESCE(UserFlg,ProdFlg) = 1 OR ValidProdFlg=0) THEN 0 ELSE ROW_NUMBER() OVER(PARTITION BY ProdID ORDER BY ProdCreatedDt DESC,ProdSeqNbr DESC) END AS ProdLIFO,

Here As soon as 1 record becomes UserFlg or ProdFlg=1 or validProdflg=0 then the row_number() is not coming out properly. Basically trying to create a valid "first in last out " & "last in first out"

Please let me know there are any solution...

2

ROW_NUMBER function (and other windowing functions) operate on the virtual table that is received by the SELECT clause. So, if your goal is that the row_number should count only a subset of those rows ( i.e., rows that do not meet the WHEN clause in your expressions), then windowing functions won't do that.

You would need to generate a virtual table (perhaps using a CTE or a CROSS APPLY construct) that contains only the rows for which you want to apply the row_number function and use the row_number function to that virtual table.