Searching for most recent non-zero records

Database
Fan

Searching for most recent non-zero records

Hi,

I have some basic inventory data showing inventory of products by date.

I am trying to get a query that returns the most recent date when inventory turned positive. I have put a sample of the output.

Note: For product codes C and D there are never zero's in all the rows, so query should return the first date.

Product       Date             Inventory
A 01/02/2015 0
A 01/03/2015 5
A 01/04/2015 6
A 01/05/2015 7
A 01/06/2015 7
A 01/07/2015 7

B 01/02/2015 1
B 01/03/2015 0
B 01/04/2015 0
B 01/05/2015 7
B 01/06/2015 7
B 01/07/2015 8

C 01/02/2015 7
C 01/03/2015 8
C 01/04/2015 7
C 01/05/2015 9
C 01/06/2015 7
C 01/07/2015 10

D 01/05/2015 3
D 01/06/2015 2
D 01/07/2015 1

E 01/02/2015 7
E 01/03/2015 8
E 01/04/2015 7
E 01/05/2015 9
E 01/06/2015 0
E 01/07/2015 0

Expected Results:

Product       MaxDate         MaxDateInventory   FirstDate
A 01/07/2015 7 01/03/2015
B 01/07/2015 8 01/05/2015
C 01/07/2015 10 01/02/2015
D 01/07/2015 1 01/05/2015
E 01/07/2015 0 01/07/2015

2 REPLIES
Junior Contributor

Re: Searching for most recent non-zero records

You always need the most current row plus consecutive rows where Inventory <> 0:

SELECT
t.*,
SUM(CASE WHEN Inventory = 0 THEN 1 ELSE 0 end)
OVER (PARTITION BY Product
ORDER BY dt DESC
ROWS UNBOUNDED PRECEDING) AS grp,
ROW_NUMBER()
OVER (PARTITION BY Product
ORDER BY dt DESC) AS rn
FROM tab AS t
QUALIFY grp = 0
OR rn = 1

Now move this Select to a Derived Table and then apply either an aggregation or another OVER:

SELECT ...
,MIN(dt) OVER (PARTITION BY product)
FROM
(
...
) AS dt
QUALIFY rn = 1
Fan

Re: Searching for most recent non-zero records

It worked out perfectly. Thank you very much.