J'utilise SQL Server 2012 et j'ai une table comme ci-dessous:
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)
Dans mon tableau, j'ai une colonne de type avec des valeurs ('I' and 'O')
J'ai un prix unitaire pour 'I' Type Record et 'O' Type Record utilisé dernier 'I' Type Record Valeur Je veux calculer RunningTotalPrice (Sum de Quantité * PrixUnitaire de chaque rangée).
Le code suivant calcule RunningTotalQuantity:
SELECT *, SUM(CASE WHEN [Type] = 'I' Then Quantity ELSE -Quantity END)OVER (ORDER BY Id) AS QuantityRunningTotal FROM @T
et les résultats de cette requête sont:
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
Je veux avoir le résultat suivant
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
Dans ce résultat, la colonne Prix Unitaire Null avec le dernier prix unitaire existe avant loggings. et Calculez le prix (Quantité * PrixUnitaire) et Calculez le total cumulé du prix.
Malheureusement, les fonctions LEAD
et LAG
ne peuvent pas être utilisées jusqu'à la dernière NULL
non NULL
, donc vous devrez utiliser OUTER APPLY
pour get le UnitPrice
précédent à utiliser dans les lignes où le type est '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;