All Forums Database
pp01 2 posts Joined 12/11
18 Jan 2016
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
dnoeth 4628 posts Joined 11/04
18 Jan 2016

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

 

Dieter

pp01 2 posts Joined 12/11
18 Jan 2016

It worked out perfectly. Thank you very much.

You must sign in to leave a comment.