Sql-server – Improve performance of while loop for INSERT INTO query

optimizationperformancequery-performancesql serversql-server-2016

I've got a simple query that creates a daily snapshot that uses a WHILE loop statement in SQL for a range of dates.

The problem is the query is taking quite long running at 25-40 mins. I've tried looking for ways to rewrite it using date table, CTE's, OVER WITH partition clause but have not been successful.

One of the main challenges with other approaches is that they don't seem to fill in the missing dates. The Inventory data has holes in the dates but I need the daily balances for each day in the range of dates.

Can anyone help rewrite this to be more efficient?

DECLARE @SnapshotDate DATE = '2009-01-01' -- YYYY-MM-DD -- earliest date in inventory table
DECLARE @EndSnapshotDate DATE
SELECT @EndSnapshotDate = MAX(DateKey_FK_TransDate) FROM [FACT].[Inventory2]

WHILE (@SnapshotDate <= @EndSnapshotDate) 

BEGIN

    INSERT INTO [FACT].[InventoryBalance]
               (
                [DateKey_FK]
               ,[Company_FK]
               ,[ItemFinancialGroup_FK]
               ,[Balance]
               )
    SELECT 
           @SnapshotDate
          ,[Company_FK]
          ,[ItemFinancialGroup_FK]
          ,SUM([DebitCreditMST]) as 'Balance'
    FROM [FACT].[Inventory2]
    WHERE 
        [DateKey_FK_TransDate] <= @SnapshotDate

    GROUP BY 
           [Company_FK]
          ,[ItemFinancialGroup_FK]

    SET @SnapshotDate = DATEADD(DD, 1, @SnapshotDate) -- Add one day

END
GO

Fact.Inventory2 contains 4 million rows and InventoryBalance daily snapshot ends up being 9 million rows.

As suggested i'm including table definitions to create the source and destination tables with sample data. 

    -- Create table with source data
CREATE TABLE [FACT].[Inventory_Sample](
    [Inventory_SK] [int] IDENTITY(1,1) NOT NULL,
    [DateKey_FK_TransDate] [int] NOT NULL,
    [Company_FK] [int] NOT NULL,
    [ItemFinancialGroup_FK] [int] NOT NULL,
    [DebitCredit] [numeric](28, 12) NOT NULL,
 CONSTRAINT [PK_InventorySample] PRIMARY KEY CLUSTERED 
(
    [Inventory_SK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET IDENTITY_INSERT [FACT].[Inventory_Sample] ON 
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (1, 20090101, 11, 5, CAST(707722.750000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (2, 20090101, 11, 5, CAST(707722.750000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (3, 20090101, 11, 6, CAST(2674.660000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (4, 20090101, 11, 6, CAST(2674.660000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (5, 20090101, 20, 5, CAST(283.380000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (6, 20090101, 20, 5, CAST(283.380000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (7, 20090131, 11, 5, CAST(135398.310000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (8, 20090131, 11, 5, CAST(135398.310000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (9, 20090131, 11, 6, CAST(-1190.200000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (10, 20090131, 11, 6, CAST(-1190.200000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (11, 20090228, 11, 5, CAST(2032.880000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (12, 20090228, 11, 5, CAST(2032.880000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (13, 20090228, 11, 6, CAST(-665.330000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (14, 20090228, 11, 6, CAST(-665.330000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (15, 20090228, 20, 5, CAST(273.440000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (16, 20090228, 20, 5, CAST(273.440000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (17, 20090331, 11, 5, CAST(-207440.200000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (18, 20090331, 11, 5, CAST(-207440.200000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (19, 20090331, 11, 6, CAST(1568.000000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (20, 20090331, 11, 6, CAST(1568.000000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (21, 20090430, 11, 5, CAST(-41274.630000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (22, 20090430, 11, 5, CAST(-41274.630000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (23, 20090430, 11, 6, CAST(-0.020000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (24, 20090430, 11, 6, CAST(-0.020000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (25, 20090430, 20, 5, CAST(-273.440000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (26, 20090430, 20, 5, CAST(-273.440000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (27, 20090531, 11, 5, CAST(104859.530000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (28, 20090531, 11, 5, CAST(104859.530000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (29, 20090630, 11, 5, CAST(-16991.160000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (30, 20090630, 11, 5, CAST(-16991.160000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (31, 20090630, 20, 5, CAST(-283.380000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (32, 20090630, 20, 5, CAST(-283.380000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (33, 20090731, 11, 5, CAST(-13287.340000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (34, 20090731, 11, 5, CAST(-13287.340000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (35, 20090831, 11, 5, CAST(-36128.950000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (36, 20090831, 11, 5, CAST(-36128.950000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (37, 20090831, 20, 5, CAST(-1269.240000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (38, 20090831, 20, 5, CAST(-1269.240000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (39, 20090912, 20, 5, CAST(1269.240000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (40, 20090912, 20, 5, CAST(1269.240000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (41, 20090930, 11, 5, CAST(-65274.840000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (42, 20090930, 11, 5, CAST(-65274.840000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (43, 20090930, 11, 6, CAST(4199.020000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (44, 20090930, 11, 6, CAST(4199.020000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (45, 20091002, 20, 5, CAST(494.760000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (46, 20091002, 20, 5, CAST(494.760000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (47, 20091030, 20, 5, CAST(0.000000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (48, 20091030, 20, 5, CAST(0.000000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (49, 20091031, 11, 5, CAST(19651.780000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (50, 20091031, 11, 5, CAST(19651.780000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (51, 20091031, 11, 6, CAST(1298.400000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (52, 20091031, 11, 6, CAST(1298.400000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (53, 20091105, 20, 5, CAST(668.350000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (54, 20091105, 20, 5, CAST(668.350000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (55, 20091110, 20, 5, CAST(-668.350000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (56, 20091110, 20, 5, CAST(-668.350000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (57, 20091124, 20, 5, CAST(0.000000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (58, 20091124, 20, 5, CAST(0.000000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (59, 20091128, 20, 5, CAST(-494.760000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (60, 20091128, 20, 5, CAST(-494.760000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (61, 20091130, 11, 5, CAST(-59474.730000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (62, 20091130, 11, 5, CAST(-59474.730000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (63, 20091130, 11, 6, CAST(1966.440000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (64, 20091130, 11, 6, CAST(1966.440000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (65, 20091223, 20, 5, CAST(554.030000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (66, 20091223, 20, 5, CAST(554.030000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (67, 20091228, 20, 5, CAST(-554.030000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (68, 20091228, 20, 5, CAST(-554.030000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (69, 20091229, 20, 5, CAST(347.620000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (70, 20091229, 20, 5, CAST(347.620000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (71, 20091231, 11, 5, CAST(-41524.310000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (72, 20091231, 11, 5, CAST(-41524.310000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (73, 20091231, 11, 6, CAST(-782.340000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (74, 20091231, 11, 6, CAST(-782.340000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (75, 20091231, 20, 5, CAST(253.470000000000 AS Numeric(28, 12)))
GO
INSERT [FACT].[Inventory_Sample] ([Inventory_SK], [DateKey_FK_TransDate], [Company_FK], [ItemFinancialGroup_FK], [DebitCredit]) VALUES (76, 20091231, 20, 5, CAST(253.470000000000 AS Numeric(28, 12)))
GO
SET IDENTITY_INSERT [FACT].[Inventory_Sample] OFF
GO

-- Destination table which will hold daily balances
CREATE TABLE [FACT].[InventoryBalance_Sample](
    [Inventory_SK] [int] IDENTITY(1,1) NOT NULL,
    [DateKey_FK_TransDate] [int] NOT NULL,
    [Company_FK] [int] NOT NULL,
    [ItemFinancialGroup_FK] [int] NOT NULL,
    [Balance] [numeric](28, 12) NOT NULL,
 CONSTRAINT [PK_InventoryBalance_Sample] PRIMARY KEY CLUSTERED 
(
    [Inventory_SK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

-- While loop to populate InventoryBalance_Sample table
DECLARE @SnapshotDate INT = '20090101' --YYYYMMDD
DECLARE @EndSnapshotDate INT = '20100101' --YYYYMMDD

WHILE (@SnapshotDate <= @EndSnapshotDate) 

BEGIN

    INSERT INTO [FACT].[InventoryBalance_Sample]
               ([DateKey_FK_TransDate]
               ,[Company_FK]
               ,[ItemFinancialGroup_FK]
               ,[Balance])
    SELECT 
           @SnapshotDate
          ,[Company_FK]
          ,[ItemFinancialGroup_FK]
          ,SUM([DebitCredit]) as 'Balance'
    FROM [FACT].[Inventory_Sample]
    WHERE 
        [DateKey_FK_TransDate] <= @SnapshotDate

    GROUP BY 
           [Company_FK]
          ,[ItemFinancialGroup_FK]

    SET @SnapshotDate = CAST(REPLACE(DATEADD(DD, 1, CONVERT(DATE, CONVERT(VARCHAR(8), @SnapshotDate), 112)),'-','') AS INT) -- Add one day

END
GO

Best Answer

Gaps in data can usually be fixed with a calendar table that you can do a JOIN against. Given your sample data the following appears to produce the same result as your while-loop:

SELECT calendar.dt
     , i.Company_FK
     , i.ItemFinancialGroup_FK
     , SUM(i.DebitCredit)
FROM calendar 
JOIN Inventory_Sample i
    ON calendar.dt >= DateKey_FK_TransDate
GROUP BY calendar.dt, i.Company_FK, i.ItemFinancialGroup_FK
ORDER BY calendar.dt, i.Company_FK, i.ItemFinancialGroup_FK;

It was easier to move the cumulative part to the JOIN condition, than using a window function and a LEFT JOIN, so I changed that from my first attempt.

Related Question