October 27, 2026 at 6:06 pm
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;
October 28, 2026 at 2:50 pm
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.
October 28, 2026 at 9:24 pm
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
November 5, 2026 at 11:47 am
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