Is there a way to use windowing functions to simplify this query?
I have some data, real-world example. In order to display the data a particular way, the data is stored in such a way that I have to manipulate it to get the results I want. Essentially, there are two different types of invoices: a pre-invoice (P) where amounts are billed before an order is processed; and a standard invoice (I) where the remainder is billed after shipping. You would expect, under ordinary circumstances, that the standard invoice would have the pre-invoice amount subtracted out of it, e.g., if the pre-invoice was for $2 and the order was for $10 that the standard invoice would be $8. Instead, though, the standard invoice is stored as $10.
Below is a fully operational query (That query's operational! It's a trap!) that both populates the data and returns the results I want. The goal is to take the invoice amount and if it's a pre-invoice, return that amount; but if it's a standard invoice, to "use up" the value of the pre-invoice and return a new amount, minimum zero. I've included six scenarios, because the pre-invoice could be any amount and could technically be required at any time.
Any assistance on this would be much appreciated. I tried some windowing functions, including UNBOUNDED PRECEDING type stuff, but it always seemed like I needed to be recursive and go into an infinite loop. Hence my brute-force approach, below.
DECLARE @PData TABLE (
CustNum INT
,TransNum INT
,InvType NVARCHAR(1)
,InvAmt DECIMAL(5,2)
,CRank INT
,TRank INT
,ModInvAmt DECIMAL(5,2)
)
INSERT INTO @PData (
CustNum
,TransNum
,InvType
,InvAmt
)
VALUES
(124, 1,'P',2)
,(124, 2,'I',10)
,(124, 3,'I',10)
,(153, 4,'I',10)
,(153, 5,'P',2)
,(153, 6,'I',10)
,(324, 7,'I',10)
,(324, 8,'I',10)
,(324, 9,'P',2)
,(441,10,'P',12)
,(441,11,'I',10)
,(441,12,'I',10)
,(455,13,'I',10)
,(455,14,'P',12)
,(455,15,'I',10)
,(667,16,'I',10)
,(667,17,'I',10)
,(667,18,'P',12)
UPDATE pd1
SET CRank = pd2.CDR
FROM @PData pd1
JOIN (SELECT CustNum, TransNum, DENSE_RANK() OVER (ORDER BY CustNum) AS CDR
FROM @PData) pd2
ON pd1.TransNum = pd2.TransNum
UPDATE pd1
SET TRank = pd2.TDR
FROM @PData pd1
JOIN (SELECT CustNum, TransNum, DENSE_RANK() OVER (PARTITION BY CustNum ORDER BY TransNum) AS TDR
FROM @PData) pd2
ON pd1.TransNum = pd2.TransNum
DECLARE @Counter1 INT
,@Counter2 INT
,@CBal DECIMAL(5,2)
,@TNum INT
,@IAmt DECIMAL(5,2)
SET @Counter1 = 0
WHILE @Counter1 < (SELECT MAX(CRank) FROM @PData)
BEGIN
SET @Counter1 += 1
SET @CBal = 0
SET @Counter2 = 0
WHILE @Counter2 < (SELECT MAX(TRank) FROM @PData WHERE CRank = @Counter1)
BEGIN
SET @Counter2 += 1
SET @TNum = (SELECT TransNum FROM @PData WHERE CRank = @Counter1 AND TRank = @Counter2)
SET @IAmt = (SELECT InvAmt FROM @PData WHERE TransNum = @TNum)
IF (SELECT InvType FROM @PData WHERE TransNum = @TNum) = 'P'
BEGIN
UPDATE @PData SET ModInvAmt = @IAmt WHERE TransNum = @TNum
SET @CBal += -@IAmt
END
ELSE
BEGIN
UPDATE @PData SET ModInvAmt = (ABS(@IAmt+@CBal)+(@IAmt+@CBal))/2 -- MINIMUM = 0
WHERE TransNum = @TNum
SET @CBal += (@IAmt - (ABS(@IAmt+@CBal)+(@IAmt+@CBal))/2)
END
END
END
SELECT CustNum
,TransNum
,InvType
,InvAmt
,ModInvAmt
FROM @PData
Here are the results I get:
I wouldn't normally report the original invoice amount--just the new one--but I've included it here so it's more clear how it changed.
CustNum TransNum InvType InvAmt ModInvAmt
124 1 P 2.00 2.00
124 2 I 10.00 8.00
124 3 I 10.00 10.00
153 4 I 10.00 10.00
153 5 P 2.00 2.00
153 6 I 10.00 8.00
324 7 I 10.00 10.00
324 8 I 10.00 10.00
324 9 P 2.00 2.00
441 10 P 12.00 12.00
441 11 I 10.00 0.00
441 12 I 10.00 8.00
455 13 I 10.00 10.00
455 14 P 12.00 12.00
455 15 I 10.00 0.00
667 16 I 10.00 10.00
667 17 I 10.00 10.00
667 18 P 12.00 12.00
I would do it recursivly with common table expressions.
Best regards Peter
DECLARE @PData TABLE (
CustNum INT
,TransNum INT
,InvType NVARCHAR(1)
,InvAmt DECIMAL(5,2)
)
INSERT INTO @PData (
CustNum
,TransNum
,InvType
,InvAmt
)
VALUES
(124, 1,'P',2)
,(124, 2,'I',10)
,(124, 3,'I',10)
,(153, 4,'I',10)
,(153, 5,'P',2)
,(153, 6,'I',10)
,(324, 7,'I',10)
,(324, 8,'I',10)
,(324, 9,'P',2)
,(441,10,'P',12)
,(441,11,'I',10)
,(441,12,'I',10)
,(455,13,'I',10)
,(455,14,'P',12)
,(455,15,'I',10)
,(455,19,'I',10)
,(667,16,'I',10)
,(667,17,'I',10)
,(667,18,'P',12)
;WITH Data as (
SELECT
CustNum,
TransNum,
InvType,
InvAmt,
ROW_NUMBER() OVER (PARTITION BY custNum ORDER BY Transnum ASC) row,
CASE WHEN InvType = 'P' THEN cast(-1*InvAmt AS DECIMAL(5,2)) ELSE 0 END prepaidAmt
FROM
@PData
), modified as(
SELECT
CustNum,
TransNum,
InvType,
InvAmt,
prepaidAmt,
row,
InvAmt total
FROM Data d1
WHERE row = 1
UNION ALL
SELECT
d2.CustNum,
d2.TransNum,
d2.InvType,
d2.InvAmt,
CASE
WHEN
d2.InvAmt+m.prepaidAmt < 0
THEN
CAST (d2.InvAmt+m.prepaidAmt AS DECIMAL(5,2))
ELSE
CASE
WHEN
d2.invtype = 'P'
THEN
CAST(-1*d2.invamt AS DECIMAL(5,2))
ELSE
0
END
END ,
d2.row,
CASE
WHEN
d2.InvAmt+m.prepaidAmt <0
THEN
0
ELSE
CAST( d2.InvAmt+m.prepaidAmt AS DECIMAL(5,2))
END
FROM Data d2
JOIN modified m
ON
m.CustNum = d2.CustNum and
m.row = d2.row-1
)
SELECT
m.CustNum,
m.TransNum,
m.InvType,
m.InvAmt,
m.total
FROM modified m
ORDER BY
custnum,
transnum