2

I have a query showing inventory levels in the warehouse and I want to see the most recent movement date for all warehouse locations that do not have any inventory.

This is a snippet of my current code

SELECT
    WHS_ID
  , itemid 
  , physicalinvent  
  , MAX(
        CASE WHEN (PHYSICALINVENT IS NULL) THEN Record_Date ELSE NULL END
      ) AS LAST_WORK_DATE
    
  , (SELECT TOP 1 CASE 
        WHEN PHYSICALINVENT IS NULL THEN ITEMID ELSE NULL END
        FROM DB.InventSum, DB.Work
        WHERE MAX(WORK.Record_Date) = MAX(LWD.LAST_WORK_DATE)
        )
        AS LAST_ITEM_ID

FROM 
  DB.WHSLoc AS WL FULL 
  CROSS APPLY
    (SELECT(
        CASE WHEN (INV.PHYSICALINVENT IS NULL) THEN work.Record_Date ELSE NULL END) AS LAST_WORK_DATE)
        AS LWD

GROUP BY 
  WHS_ID 
, itemid  
, physicalinvent
, CASE WHEN (PHYSICALINVENT IS NULL) THEN ITEMID ELSE NULL END

ORDER BY 
  WHS_ID;

and below is the output I am getting:

| WHS_Loc |      ItemID     | Phys_Inv | Last_Work  |  Last_Item   |
|  ------ | --------------- | -------- | ---------- | ------------ |
|   101   |    00-562644    |    149   |     NULL   |     NULL     |
|   501   |       NULL      |   NULL   | 2021-11-09 | 00-913090    |
|   501   |       NULL      |   NULL   | 2022-12-07 | 00-936546    |
|   501   |       NULL      |   NULL   | 2021-03-22 | S1-10M800    |
|   501   |       NULL      |   NULL   | 2021-03-23 | 01-1P2493    |
|   901   |    00-941941    |     28   |    NULL    |    NULL      |

My desired output is:

|  WHS_Loc   |      ItemID     | Phys_Inv | Last_Work  |     Last_Item      |
|  --------  | --------------- | -------- | ---------- | ------------------ |
| 10101101   | 00-562644-00001 |    149   |     NULL   |        NULL        |
| 10101501   |       NULL      |   NULL   | 2022-12-07 | 00-936546-00001    |
| 10101901   |    00-941941    |     28   |    NULL    |       NULL         |

I am wanting to return the value in the 'Last_Item' column that corresponds with the MAX(Last_Work) value based on the Max Case function. I've tried the subquery to select the Top 1 and the Max Case on 'Last_Item' but I am not able to come to a solution.

Again I am looking for:

  • one row per 'WHS_Loc'
  • return the matching 'Last_Item' that corresponds to the MAX(Last_Work) when 'Phys_Inv' = 0
  • For Example: I want Row 2 to output:
| 501 | NULL | NULL | 2022-12-07 | 00-936546-00001 |

but when using MAX to find the last item it returns:

| 501 | NULL | NULL | 2022-12-07 | S1-10M800-0001R |
akroeker
  • 21
  • 4

0 Answers0