SQL Server calculates the current total with the query
I am using SQL Server 2012 and have a table as shown below:
DECLARE @T TABLE(Id INT, [Type] CHAR(1), Quantity INT, Price MONEY, UnitPrice AS (Price/Quantity))
INSERT INTO @T VALUES
(1, 'I', 30, 1500),
(2, 'O', 5, NULL),
(3, 'O', 20, NULL),
(4, 'O', 2, NULL),
(5, 'I', 10, 2500),
(6, 'I', 8, 1000),
(7, 'O', 3, NULL),
(8, 'O', 10, NULL),
(9, 'I', 12, 3600)
In my table I have Type Column With Values ('I' and 'O')
I have a unit price for the type of "I" Type of record and "O" the type of record "I" type Meaning I want to calculate the recording RunningTotalPrice (Amount Number * UnitPrice for each row ).
The following code calculates RunningTotalQuantity:
SELECT *,
SUM(CASE WHEN [Type] = 'I' Then Quantity ELSE -Quantity END)OVER (ORDER BY Id) AS QuantityRunningTotal
FROM @T
and the results of this query:
Id Type Quantity Price UnitPrice QuantityRunningTotal
1 I 30 1500/00 50/00 30
2 O 5 NULL NULL 25
3 O 20 NULL NULL 5
4 O 2 NULL NULL 3
5 I 10 2500/00 250/00 13
6 I 8 1000/00 125/00 21
7 O 3 NULL NULL 18
8 O 10 NULL NULL 8
9 I 12 3600/00 300/00 20
I want to have the following result
Id Type Quantity Price UnitPrice QuantityRunningTotal Price RunningTotalPrice
1 I 30 1500/00 50/00 30 1500/00 1500/00
2 O 5 NULL 50/00 25 250/00 1250/00
3 O 20 NULL 50/00 5 1000/00 250/00
4 O 2 NULL 50/00 3 100/00 150/00
5 I 10 2500/00 250/00 13 2500/00 2650/00
6 I 8 1000/00 125/00 21 1000/00 3650/00
7 O 3 NULL 125/00 18 375/00 3275/00
8 O 10 NULL 125/00 8 1250/00 2025/00
9 I 12 3600/00 300/00 20 3600/00 5625/00
In this result, the Null Unitprice Column priced last has a unit price before the records. and calculate the price (quantity * UnitPrice) and calculate the total price quantity.
source to share
Unfortunately, functions LEAD
and LAG
cannot be used for the last value NULL
, so you will need to use OUTER APPLY
to use the previous one UnitPrice
on strings where the type is 'O':
SELECT t.ID,
t.[Type],
t.Quantity,
t.Price,
t.UnitPrice,
SUM(CASE WHEN t.[Type] = 'I' THEN t.Quantity ELSE -t.Quantity END) OVER (ORDER BY t.Id) AS QuantityRunningTotal,
CASE WHEN t.[Type] = 'I' THEN t.Price ELSE t.Quantity * p.UnitPrice END AS Price2,
SUM(CASE WHEN t.[Type] = 'I' THEN t.Price ELSE -t.Quantity * p.UnitPrice END)OVER (ORDER BY t.Id) AS QuantityRunningTotal
FROM @T AS t
OUTER APPLY
( SELECT TOP 1 t2.UnitPrice
FROM @T AS t2
WHERE t2.ID < t.ID
AND t2.UnitPrice IS NOT NULL
ORDER BY t2.ID DESC
) AS p;
source to share