Skip to content

Instantly share code, notes, and snippets.

@tobiascoetzee
Last active June 2, 2018 00:56
Show Gist options
  • Save tobiascoetzee/2ceed462eec805212c4ef14348a3e9ec to your computer and use it in GitHub Desktop.
Save tobiascoetzee/2ceed462eec805212c4ef14348a3e9ec to your computer and use it in GitHub Desktop.
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.
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment