Improve performance of while loop for INSERT INTO query?

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 NOT NULL,
[DateKey_FK_TransDate] [int] NOT NULL,
[Company_FK] [int] NOT NULL,
[ItemFinancialGroup_FK] [int] NOT NULL,
[DebitCredit] numeric NOT NULL,
CONSTRAINT [PK_InventorySample] PRIMARY KEY CLUSTERED
(
[Inven

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.

StackExchange Database Administrators Q#204576, answer score: 2