SQL query that returns a list of all the products sold with a running total for each product and each day plus a total for each day and product.

dayproducttotal.sql

SET NOCOUNT ON;
 
DECLARE @Balances AS TABLE
(
    ProductId INT
    ,ModifiedDate DATETIME
    ,UnitPrice MONEY
    ,ModifiedDateBalance MONEY
    ,ProductIdBalance MONEY
    ,ModifiedDateTotal MONEY
    ,ProductIdTotal MONEY
)
 
 
DECLARE
    @ProductId AS INT
    ,@PreviousProductId AS INT
    ,@ModifiedDate AS DATETIME
    ,@PreviousModifiedDate AS DATETIME
    ,@UnitPrice AS MONEY
    ,@ProductBalance AS MONEY
    ,@ModifiedDateBalance AS MONEY
 
 
DECLARE BalanceCursor CURSOR FAST_FORWARD FOR
    SELECT
        ProductID
        ,ModifiedDate
        ,UnitPrice
    FROM
    Sales.SalesOrderDetail
    ORDER BY ProductID, ModifiedDate, SalesOrderID;
 
OPEN BalanceCursor;
 
FETCH NEXT FROM BalanceCursor INTO @ProductId, @ModifiedDate, @UnitPrice;
 
SELECT @PreviousProductId = @ProductId
    ,@PreviousModifiedDate = @ModifiedDate
    ,@ProductBalance = 0
    ,@ModifiedDateBalance = 0;
 
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @ProductId <> @PreviousProductId
    BEGIN
        UPDATE @Balances SET
            ProductIdTotal = @ProductBalance
            ,ModifiedDateTotal = @ModifiedDateBalance
        WHERE ProductId = @PreviousProductId
 
        SELECT @PreviousProductId = @ProductId, @ProductBalance = 0;
    END
 
    IF @ModifiedDate <> @PreviousModifiedDate
    BEGIN
        UPDATE @Balances
            SET ModifiedDateTotal = @ModifiedDateBalance
        WHERE ProductId = @PreviousProductId
        AND ModifiedDate = @PreviousModifiedDate
 
        SELECT @PreviousModifiedDate = @ModifiedDate, @ModifiedDateBalance = 0;
    END
 
    SET @ProductBalance = @ProductBalance + @UnitPrice;
    SET @ModifiedDateBalance = @ModifiedDateBalance + @UnitPrice;
 
    INSERT INTO @Balances VALUES (@ProductId, @ModifiedDate, @UnitPrice, @ModifiedDateBalance, @ProductBalance, 0, 0)
 
    FETCH NEXT FROM BalanceCursor INTO @ProductId, @ModifiedDate, @UnitPrice;
END
 
CLOSE BalanceCursor;
DEALLOCATE BalanceCursor;
 
SELECT * FROM @Balances;