Inventory Snapshot for missing Period

• getsaby

  Old Hand

  Points: 353

  More actions

  October 27, 2026 at 6:06 pm

  #5078638

  Hello,

  We maintain an inventory table that captures monthly snapshots of item inventory levels. However, a new record is only created when there is a change in the inventory value. As a result, if an item has an inventory value of 200 in January 2026 and the value remains unchanged through March 2026 with value of 250, the table will contain records only for January and March. There can be no change in Value for an year or so too.

  The business users would like to see a complete monthly inventory snapshot, including months where no inventory changes occurred. In this example, the expected output would include three records: January 2026 (200), February 2026 (200), and March 2026 (250).

  

  Here is some sample data, the key is {Dstrct_code, Stock_code, Whouse_ID, Full_Acct_Per}

  with cte as 
  (
  select 'CCTB' dstrct_code,  '000001719' stock_code, '' WHOUSE_ID, '202404' FULL_ACCT_PER, 12 SOH
  UNION ALL
  select 'CCTB' dstrct_code,  '000001719' stock_code, '' WHOUSE_ID, '202401' FULL_ACCT_PER, 15 SOH
  UNION ALL
  select 'CCTB' dstrct_code,  '000001719' stock_code, '' WHOUSE_ID, '202304' FULL_ACCT_PER, 18 SOH
  UNION ALL
  select 'CCTB' dstrct_code,  '000001719' stock_code, 'CCRK' WHOUSE_ID, '202404' FULL_ACCT_PER, 12 SOH
  UNION ALL
  select 'CCTB' dstrct_code,  '000001719' stock_code, 'CCRK' WHOUSE_ID, '202401' FULL_ACCT_PER, 15 SOH
  UNION ALL
  select 'CCTB' dstrct_code,  '000001719' stock_code, 'CCRK' WHOUSE_ID, '202304' FULL_ACCT_PER, 18 SOH
  UNION ALL
  select 'BTTB' dstrct_code,  '12001719' stock_code, 'BT' WHOUSE_ID, '202512' FULL_ACCT_PER, 1 SOH
  UNION ALL
  select 'BTTB' dstrct_code,  '12001719' stock_code, 'BT' WHOUSE_ID, '202412' FULL_ACCT_PER, 18 SOH
  UNION ALL
  select 'BTTB' dstrct_code,  '12001719' stock_code, '' WHOUSE_ID, '202512' FULL_ACCT_PER, 1 SOH
  UNION ALL
  select 'BTTB' dstrct_code,  '12001719' stock_code, '' WHOUSE_ID, '202412' FULL_ACCT_PER, 18 SOH
  )
  SELECT * FROM cte;