Returning Continuous Months and Copying Values from the Previous Month

Currently, I have the query:

SELECT 
    i.ItemNbr,
    i.TranMonth,
    i.Warehouse,
    i.OpeningQty,
    i.OpeningPrice,
    i.OpeningAmt
FROM  
    Inventory i
WHERE 
    i.ItemNbr IN ('00188613')

It returns this data as result:

ItemNbr TranMonth   Warehouse   OpeningQty  OpeningPrice    OpeningAmt
----------------------------------------------------------------------
00188613    2024-01     01      4.0000      303.100000      1212.4000
00188613    2024-04     01      4.0000      303.100000      1212.4000
00188613    2024-05     01      5.0000      303.100000      1515.5000
00188613    2024-06     01      4.0000      303.100000      1212.4000
00188613    2024-12     01      4.0000      365.400000      1461.6000

Regarding the financial aspects, our Finance team wants the data returned with the following conditions:

TranMonth should be continuous; any missing TranMonth values should be filled in by copying values from the previous TranMonth
The data should be shown up to the current month

They expect the data to be displayed as shown here:

ItemNbr    TranMonth    Warehouse   OpeningQty  OpeningPrice    OpeningAmt
--------------------------------------------------------------------------
00188613    2024-01     01          4.0000      303.1           1212.4
00188613    2024-02     01          4.0000      303.1           1212.4
00188613    2024-03     01          4.0000      303.1           1212.4
00188613    2024-04     01          4.0000      303.1           1212.4
00188613    2024-05     01          5.0000      303.1           1515.5
00188613    2024-06     01          4.0000      303.1           1212.4
00188613    2024-07     01          4.0000      303.1           1212.4
00188613    2024-08     01          4.0000      303.1           1212.4
00188613    2024-09     01          4.0000      303.1           1212.4
00188613    2024-10     01          4.0000      303.1           1212.4
00188613    2024-11     01          4.0000      303.1           1212.4
00188613    2024-12     01          4.0000      365.4           1461.6
00188613    2025-01     01          4.0000      365.4           1461.6

For example, as you can see, the original data table is missing TranMonth values for 2024-02 and 2024-03. The new data table fills in these missing TranMonth, and the Warehouse, OpeningQty, OpeningPrice and OpeningAmt values are copied from 2024-01.

Additionally, the original data table is missing 2025-01, which is the current month. The new table fills in the current month and copies the corresponding values from 2024-12.

Please help me write a query to return data as per the above requirement.

For your quick test, I have created an Inventory table and inserted the following data:

CREATE TABLE Inventory
(
    ItemNbr NVARCHAR(20),
    TranMonth CHAR(7),
    Warehouse CHAR(2),
    OpeningQty NUMERIC(18, 4),
    OpeningPrice NUMERIC(18, 4),
    OpeningAmt NUMERIC(18, 4)
)

INSERT INTO Inventory
SELECT '00188613','2024-01','01',4.0000,303.100000,1212.4000
UNION
SELECT '00188613','2024-04','01',4.0000,303.100000,1212.4000
UNION
SELECT '00188613','2024-05','01',5.0000,303.100000,1515.5000
UNION
SELECT '00188613','2024-06','01',4.0000,303.100000,1212.4000
UNION
SELECT '00188613','2024-12','01',4.0000,365.400000,1461.6000

Thanks

Solution

First the months are generated starting from 2024-01-01 in this case for a period of 12 months in the CTE all_months.

SELECT FORMAT(DATEADD(MONTH, rn, '2024-01-01'), 'yyyy-MM') AS TranMonth
  FROM 
  (SELECT TOP (DATEDIFF(MONTH, '2024-01-01', GETDATE()) + 1)
  row_number()  over (order by  (select null)) - 1 AS rn
  FROM master.dbo.spt_values) as a

generates

| TranMonth |
|-----------|
| 2024-01   |
| 2024-02   |
| 2024-03   |
| 2024-04   |
| 2024-05   |
| 2024-06   |
| 2024-07   |
| 2024-08   |
| 2024-09   |
| 2024-10   |
| 2024-11   |
| 2024-12   |
| 2025-01   |

Then all the months are left joined with the inventory table comparing TranMonth. For example for a row of 2024-03 in all_months, it will fetch months less than 2024-03 from inventory months i.e 2024-01 .

| TranMonth | TranMonth_i | ItemNbr  | Warehouse | OpeningQty | OpeningPrice | OpeningAmt | rn |   |
|-----------|-------------|----------|-----------|------------|--------------|------------|----|---|
| 2024-01   | 2024-01     | 00188613 | 01        | 4.0000     | 303.1000     | 1212.4000  | 1  |   |
| 2024-02   | 2024-01     | 00188613 | 01        | 4.0000     | 303.1000     | 1212.4000  | 1  |   |
| 2024-03   | 2024-01     | 00188613 | 01        | 4.0000     | 303.1000     | 1212.4000  | 1  |

Then these rows are ranked based on each ItemNbr ,Warehouse, TranMont and only the latest rank is fetched.

Final Query

I have partitioned by ItemNbr, TranMonth, Warehouse with the assumption that this combination will be looked as a whole but you can modify those as needed.

with all_months as
  
  ( SELECT FORMAT(DATEADD(MONTH, rn, '2024-01-01'), 'yyyy-MM') AS TranMonth
  FROM 
  (SELECT TOP (DATEDIFF(MONTH, '2024-01-01', GETDATE()) + 1)
  row_number()  over (order by  (select null)) - 1 AS rn
  FROM master.dbo.spt_values) as a  ),
  
  join_inv as
  
  ( select m.TranMonth, i.ItemNbr, i.Warehouse, i.OpeningQty, 
  i.OpeningPrice, i.OpeningAmt, 
  
  row_number() over (PARTITION BY  i.ItemNbr , i.Warehouse, m.TranMonth ORDER BY i.TranMonth DESC) AS rn
  from all_months m 
  left join Inventory i 
  on i.TranMonth <= m.TranMonth ) 
  
  select ItemNbr, TranMonth, Warehouse, OpeningQty, 
  OpeningPrice, OpeningAmt 
  from join_inv 
  where rn = 1 
  order by ItemNbr, TranMonth;

Demo Fiddle

Output

| ItemNbr  | TranMonth | Warehouse | OpeningQty | OpeningPrice | OpeningAmt |
|----------|-----------|-----------|------------|--------------|------------|
| 00188613 | 2024-01   | 01        | 4.0000     | 303.1000     | 1212.4000  |
| 00188613 | 2024-02   | 01        | 4.0000     | 303.1000     | 1212.4000  |
| 00188613 | 2024-03   | 01        | 4.0000     | 303.1000     | 1212.4000  |
| 00188613 | 2024-04   | 01        | 4.0000     | 303.1000     | 1212.4000  |
| 00188613 | 2024-05   | 01        | 5.0000     | 303.1000     | 1515.5000  |
| 00188613 | 2024-06   | 01        | 4.0000     | 303.1000     | 1212.4000  |
| 00188613 | 2024-07   | 01        | 4.0000     | 303.1000     | 1212.4000  |
| 00188613 | 2024-08   | 01        | 4.0000     | 303.1000     | 1212.4000  |
| 00188613 | 2024-09   | 01        | 4.0000     | 303.1000     | 1212.4000  |
| 00188613 | 2024-10   | 01        | 4.0000     | 303.1000     | 1212.4000  |
| 00188613 | 2024-11   | 01        | 4.0000     | 303.1000     | 1212.4000  |
| 00188613 | 2024-12   | 01        | 4.0000     | 365.4000     | 1461.6000  |
| 00188613 | 2025-01   | 01        | 4.0000     | 365.4000     | 1461.6000  |