3

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
  • What RDBMS? What version of said RDBMS? Loops are generally not performant in databases, so try to think of doing it as a set based operation. – LowlyDBA Apr 20 '18 at 21:34
  • This would be executed on MS SQL Server 2016. I understand that loops are not efficient and should look at set based methods just haven't been able to successfully rewrite it and address the daily balances. – Jasonn38 Apr 20 '18 at 21:36
  • Queries like this tend to work a bit better when you use a unique key to work on a set number of rows, like in this example. – Erik Darling Apr 20 '18 at 21:51
  • Can you just shorten the WHERE [DateKey_FK_TransDate] <= @SnapshotDate statement to have >= or BETWEEN to give defined start and stop time for the WHERE to use? So this might mean you need to chunk it out per year, 3 month spans, etc. in smaller batches perhaps. – Pimp Juice IT Apr 21 '18 at 5:26
  • This is "Daily" and it inserts into [InventoryBalance] table "daily" for records where [DateKey_FK_TransDate] <= @SnapshotDate? – Pimp Juice IT Apr 21 '18 at 5:29
2

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.

  • so I tried rewriting my query using this suggested LEFT JOIN but i'm not getting all the data. – Jasonn38 Apr 20 '18 at 22:15
  • Edit the question and add table definitions (create table statements) and some sample data (as insert statements) that demonstrates the problem and I'll have a look. – Lennart Apr 20 '18 at 22:17
  • Hi Lennart, I've added code for creating source and dest tables along with sample data as you suggested. Any further help on coming up with a better solution would be much appreciated. – Jasonn38 Apr 24 '18 at 17:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.