Inventory Snapshot for missing Period

  • 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).

    Screenshot 2026-10-27 134811

    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;

     

     

  • Something like this?

    with cte as 
    (
    select 'CCTB' dstrct_code, '000001719' stock_code, '' WHOUSE_ID, '202404' FULL_ACCT_PER, 12 SOH
    UNION ALL select 'CCTB', '000001719', '', '202401', 15
    UNION ALL select 'CCTB', '000001719', '', '202304', 18
    UNION ALL select 'CCTB', '000001719', 'CCRK', '202404', 12
    UNION ALL select 'CCTB', '000001719', 'CCRK', '202401', 15
    UNION ALL select 'CCTB', '000001719', 'CCRK', '202304', 18
    UNION ALL select 'BTTB', '12001719', 'BT', '202512', 1
    UNION ALL select 'BTTB', '12001719', 'BT', '202412', 18
    UNION ALL select 'BTTB', '12001719', '', '202512', 1
    UNION ALL select 'BTTB', '12001719', '', '202412', 18
    ),
    bounds AS ( -- month range: first to last period in data
    SELECT CONVERT(date, MIN(FULL_ACCT_PER) + '01') AS mn,
    CONVERT(date, MAX(FULL_ACCT_PER) + '01') AS mx
    FROM cte
    ),
    months AS ( -- one row per month
    SELECT mn AS m, mx FROM bounds
    UNION ALL
    SELECT DATEADD(month, 1, m), mx FROM months WHERE m < mx
    ),
    ranges AS ( -- each record valid from its period until next change
    SELECT dstrct_code, stock_code, WHOUSE_ID, SOH,
    CONVERT(date, FULL_ACCT_PER + '01') AS start_dt,
    LEAD(CONVERT(date, FULL_ACCT_PER + '01'))
    OVER (PARTITION BY dstrct_code, stock_code, WHOUSE_ID
    ORDER BY FULL_ACCT_PER) AS next_dt
    FROM cte
    )
    SELECT r.stock_code,
    CONVERT(char(6), m.m, 112) AS snapshot_per,
    SUM(r.SOH) AS SOH
    FROM ranges r
    JOIN months m
    ON m.m >= r.start_dt
    AND (r.next_dt IS NULL OR m.m < r.next_dt)
    GROUP BY r.stock_code, m.m
    ORDER BY r.stock_code, m.m
    OPTION (MAXRECURSION 0);

    This creates a calendar table to join to, so you can fill in missing ranges. If you have a permanent table (always a nice touch), this can run quickly there, but this is fairly quick.

    Essentially, the months CTE gets all the months. The ranges then uses the LEAD to get the number to fill in and this recurses to find the missing months.

    Without recursion (please test both on larger data sets)

    with cte as 
    (
    select 'CCTB' dstrct_code, '000001719' stock_code, '' WHOUSE_ID, '202404' FULL_ACCT_PER, 12 SOH
    UNION ALL select 'CCTB', '000001719', '', '202401', 15
    UNION ALL select 'CCTB', '000001719', '', '202304', 18
    UNION ALL select 'CCTB', '000001719', 'CCRK', '202404', 12
    UNION ALL select 'CCTB', '000001719', 'CCRK', '202401', 15
    UNION ALL select 'CCTB', '000001719', 'CCRK', '202304', 18
    UNION ALL select 'BTTB', '12001719', 'BT', '202512', 1
    UNION ALL select 'BTTB', '12001719', 'BT', '202412', 18
    UNION ALL select 'BTTB', '12001719', '', '202512', 1
    UNION ALL select 'BTTB', '12001719', '', '202412', 18
    ),
    bounds AS ( -- month range: first to last period in data
    SELECT CONVERT(date, MIN(FULL_ACCT_PER) + '01') AS mn,
    CONVERT(date, MAX(FULL_ACCT_PER) + '01') AS mx
    FROM cte
    ),
    months AS ( -- one row per month
    SELECT mn AS m, mx FROM bounds
    UNION ALL
    SELECT DATEADD(month, 1, m), mx FROM months WHERE m < mx
    ),
    ranges AS ( -- each record valid from its period until next change
    SELECT dstrct_code, stock_code, WHOUSE_ID, SOH,
    CONVERT(date, FULL_ACCT_PER + '01') AS start_dt,
    LEAD(CONVERT(date, FULL_ACCT_PER + '01'))
    OVER (PARTITION BY dstrct_code, stock_code, WHOUSE_ID
    ORDER BY FULL_ACCT_PER) AS next_dt
    FROM cte
    )
    SELECT r.stock_code,
    CONVERT(char(6), m.m, 112) AS snapshot_per,
    SUM(r.SOH) AS SOH
    FROM ranges r
    JOIN months m
    ON m.m >= r.start_dt
    AND (r.next_dt IS NULL OR m.m < r.next_dt)
    GROUP BY r.stock_code, m.m
    ORDER BY r.stock_code, m.m
    OPTION (MAXRECURSION 0);

    If I run these on 1,000,000 rows, the recursive one goes a little faster for me, and less reads.

  • Here's a tally based approach

    declare @end_dt date='20260101';

    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),
    lead_cte as (
    select dstrct_code, stock_code, WHOUSE_ID, FULL_ACCT_PER, SOH, dt,
    datediff(month, calc.dt, lead(calc.dt, 1, @end_dt) over (partition by dstrct_code, stock_code, WHOUSE_ID order by FULL_ACCT_PER)) acct_per_gap
    from cte
    cross apply (values (datefromparts(left(FULL_ACCT_PER,4),right(FULL_ACCT_PER,2),1))) calc(dt))
    select lc.*, dateadd(month, fn.n, lc.dt) calc_dt
    from lead_cte lc
    cross apply dbo.fnTally(0, lc.acct_per_gap-1) fn
    order by lc.dstrct_code, lc.stock_code, lc.WHOUSE_ID, calc_dt;

    calc_dt is the first day of the accounting period returned as a date.  If you need the existing YYYYMM format you could use CONVERT(char(6), calc_dt, 112)

    @end_dt is the overall ending date, specified as the first day of the month following the final required accounting period.  It must be later than every source period

    The query uses Jeff Moden's dbo.fnTally function.  Article is here.  Code is here

    CREATE FUNCTION [dbo].[fnTally]
    /**********************************************************************************************************************
    Jeff Moden Script on SSC: https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally
    **********************************************************************************************************************/
    (@ZeroOrOne BIT, @MaxN BIGINT)
    RETURNS TABLE WITH SCHEMABINDING AS
    RETURN WITH
    H2(N) AS ( SELECT 1
    FROM (VALUES
    (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    )V(N)) --16^2 or 256 rows
    , H4(N) AS (SELECT 1 FROM H2 a, H2 b) --16^4 or 65,536 rows
    , H8(N) AS (SELECT 1 FROM H4 a, H4 b) --16^8 or 4,294,967,296 rows
    SELECT N = 0 WHERE @ZeroOrOne = 0 UNION ALL
    SELECT TOP(@MaxN)
    N = ROW_NUMBER() OVER (ORDER BY N)
    FROM H8
    ;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • A generic approach to the problem. I am assuming a Tally Table (dbo.ScSrNo) with sufficient "SrNo" corrosponding to Number of Months counted from 1900-01-01.

    Data Table definition: CREATE TABLE test.StockGapFill ( ItemCd char(1), YMD1 date, Qty smallint, PRIMARY KEY (ItemCd, YM) )

    DECLARE @FromYM         INT         =   202601

    ,@UptoYM        INT         =   202703

    ,@FromMonths    INT

    ,@UptoMonths    INT   ;

    SELECT @FromMonths = DATEDIFF(MONTH, '1900-01-01', STR(@FromYM, 6)+'01'),  @UptoMonths = DATEDIFF(MONTH, '1900-01-01', STR(@UptoYM, 6)+'01')    ;

    ; WITH CTE_Months AS (SELECT Months = DATEDIFF(MONTH, '1900-01-01', YMD1), * FROM test.StockGapFill)

    , CTE_Item AS (SELECT DISTINCT ItemCd FROM test.StockGapFill)

    , CTE_YM AS (SELECT A.ItemCd, YMD1 = DATEADD(MONTH, B.SrNo, '1900-01-01'), Months = B.SrNo FROM CTE_Item A CROSS JOIN dbo.ScSrNo B WHERE B.SrNo BETWEEN @FromMonths AND @UptoMonths)

    , CTE_MissedYM AS (SELECT A.* FROM CTE_YM A LEFT JOIN CTE_Months B ON A.ItemCd=B.ItemCd AND A.Months=B.Months WHERE B.Months IS NULL)

    , CTE_NextYM AS (SELECT ItemCd, YMD1, Months, NextYM = LEAD(Months, 1) OVER (PARTITION BY ItemCd ORDER BY YM), Qty FROM CTE_Months )

    SELECT A.ItemCd, A.YMD1, A.Qty FROM test.StockGapFill A

    UNION ALL

    SELECT B.ItemCd, B.YMD1, A.Qty FROM CTE_NextYM A JOIN  CTE_MissedYM B ON A.ItemCd=B.ItemCd AND B.Months BETWEEN A.Months AND ISNULL(A.NextYM, 999999)

    ORDER BY 1, 2   ;

    It should take care of all possibilities.

    Thank you.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply