September 7, 2016 at 2:07 pm
I'm trying figure out how to set a Hold column value for based on a running total and an invoice amount. I want to be able to hold an invoice even if the invoice amount is partial to the hold amount.
EX:
Inv#1 17,500, hold amt:20000, Runningtotal 17500, hold
Inv#2 16,000, hold amt:20000, Runningtotal 33500, hold
Inv#3 300, hold amt: 20000, Runningtotal 33800, don't hold
.
.
etc...
This example holds the first record but not the second. I want it to do that for each customer even if the customers first invoice is more than the hold amount. Is there a way to figure out the partial invoice amount of the next invoice that's part of the running total without having to do some funky thing with temp tables or heaven forbid, a cursor?
Declare @invoices Table (
[rowid] Int Identity(1, 1)
Primary Key
,[InvoiceNumber] Int
,[CustomerID] Int
,[InvoiceTotal] Int
,[InvoiceAge] Int
,[InvoiceDate] DateTime
,HoldAmount Int
);
Insert Into @invoices
Values (123456, 1, 17500, DateDiff(Day, '2016-08-31', GetDate()), '2016-08-31',20000),
(123457, 1, 300, DateDiff(Day, '2016-08-25', GetDate()), '2016-08-25',20000),
(123458, 1, 10000, DateDiff(Day, '2016-08-25', GetDate()), '2016-08-25',20000),
(123459, 1, 19000, DateDiff(Day, '2016-08-25', GetDate()), '2016-08-25',20000),
(123460, 1, 16000, DateDiff(Day, '2016-08-26', GetDate()), '2016-08-26',20000),
(123461, 2, 40000, DateDiff(Day, '2016-08-26', GetDate()), '2016-08-26',15000),
(123462, 2, 25000, DateDiff(Day, '2016-08-26', GetDate()), '2016-08-26',15000);
Select *
,Iif([a].[RunningTotal] <= [a].[HoldAmount], 1, 0) As Hold
From (Select [rowid]
,[CustomerID]
,[InvoiceNumber]
,[InvoiceTotal]
,[InvoiceAge]
,[HoldAmount]
,Sum([InvoiceTotal]) Over (Partition By [CustomerID] Order By InvoiceAge, [InvoiceTotal] Rows Unbounded Preceding) As [RunningTotal]
From @invoices) As [a];
Any help is greatly appreciated!
Thanks,
George
September 7, 2016 at 2:48 pm
The "Quirky" method does come to mind as a viable option but I have a feeling that's what you're referring to when you say "some funky thing with temp tables"...
Just so we're on the same page... You what to "not hold" in block sizes greater than or equal to hold amount. So, when an amount gets released, you essentially wand the running total to reset to 0... Is that correct?
September 7, 2016 at 3:18 pm
Jeff will be along shortly to find the flaw in my "quirky" (he always manages to make me slap my forehead when I do one of these)...
In any case make sure you read Jeff's article[/url] before using this methodology in production code.
Any way, this appears to be doing the trick... (at least as far as I understand the requirements)
DECLARE@invoices TABLE (
rowid INT IDENTITY(1,1) PRIMARY KEY,
InvoiceNumber INT,
CustomerID INT,
InvoiceTotal INT,
InvoiceAge INT,
InvoiceDate DATETIME,
HoldAmount INT
);
INSERTINTO @invoices
VALUES
(123456,1,17500,DATEDIFF(DAY,'2016-08-31',GETDATE()),'2016-08-31',20000),
(123457,1,300,DATEDIFF(DAY,'2016-08-25',GETDATE()),'2016-08-25',20000),
(123458,1,10000,DATEDIFF(DAY,'2016-08-25',GETDATE()),'2016-08-25',20000),
(123459,1,19000,DATEDIFF(DAY,'2016-08-25',GETDATE()),'2016-08-25',20000),
(123460,1,16000,DATEDIFF(DAY,'2016-08-26',GETDATE()),'2016-08-26',20000),
(123461,2,40000,DATEDIFF(DAY,'2016-08-26',GETDATE()),'2016-08-26',15000),
(123462,2,25000,DATEDIFF(DAY,'2016-08-26',GETDATE()),'2016-08-26',15000);
--===========================================================================
IF OBJECT_ID('tempdb..#Temp', 'U') IS NOT NULL
DROP TABLE #Temp;
SELECT
i.rowid,
i.CustomerID,
i.InvoiceNumber,
i.InvoiceTotal,
i.InvoiceAge,
i.HoldAmount,
RunningTotal = CAST(NULL AS INT)
INTO #Temp
FROM
@invoices i
CREATE CLUSTERED INDEX cix_Temp_CustomerID_InvoiceAge_InvoiceTotal ON #Temp (
CustomerID,
InvoiceAge,
InvoiceTotal
);
DECLARE
@CustomerID INT = -1,
@InvoiceTotal INT = 0;
UPDATE t SET
@InvoiceTotal = t.RunningTotal = CASE WHEN @CustomerID = t.CustomerID THEN @InvoiceTotal ELSE 0 END + t.InvoiceTotal,
@InvoiceTotal = CASE WHEN @InvoiceTotal >= t.HoldAmount THEN 0 ELSE @InvoiceTotal END,
@CustomerID = t.CustomerID
FROM
#Temp t;
SELECT
t.rowid,
t.CustomerID,
t.InvoiceNumber,
t.InvoiceTotal,
t.InvoiceAge,
t.HoldAmount,
t.RunningTotal,
HOLD = IIF(t.[RunningTotal] <= t.[HoldAmount], 1, 0)
FROM
#Temp t;
The output...
rowid CustomerID InvoiceNumber InvoiceTotal InvoiceAge HoldAmount RunningTotal HOLD
----------- ----------- ------------- ------------ ----------- ----------- ------------ -----------
1 1 123456 17500 7 20000 17500 1
5 1 123460 16000 12 20000 33500 0
2 1 123457 300 13 20000 300 1
3 1 123458 10000 13 20000 10300 1
4 1 123459 19000 13 20000 29300 0
7 2 123462 25000 12 15000 25000 0
6 2 123461 40000 12 15000 40000 0
September 7, 2016 at 4:15 pm
Could you please clarify exactly which rowIDs you want to flag for Hold?
I'm seeing them in this order:
rowid
1
5
2
3
4
7
6
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 8, 2016 at 7:06 am
That was pretty close. What I need it to do is this:
[font="Courier New"]
rowid CustomerID InvoiceNumber InvoiceTotal InvoiceAge HoldAmount RunningTotal Hold
------------------------------------------------------------------------------------------------
1 1 123456 17500 8 20000 17500 1
5 1 123460 16000 13 20000 33500 1
2 1 123457 300 14 20000 33800 0
3 1 123458 10000 14 20000 43800 0
4 1 123459 19000 14 20000 62800 0
7 2 123462 25000 13 15000 25000 1
6 2 123461 40000 13 15000 65000 0
[/font]
HoldAmount is the maximum amount to hold for a customer. I want it to hold enough invoices to cover the hold amount in a sequential order by invoiceage asc.. So in this example, customer 1 has a hold amount of 20000, I want to hold the 1st and 2nd invoice because those will cover their HoldAmount. Invoice age drives what gets held 1st. What is being held is indicated with a 1 in the Hold column.
RunningTotal resets after each customer...
September 8, 2016 at 7:29 am
If I may restate the problem:
You want to hold any invoice that had a remaining hold amount prior to the invoice, whether or not the invoice itself exceeds the hold amount.
with cte as (
Select [rowid]
,[CustomerID]
,[InvoiceNumber]
,[InvoiceTotal]
,[InvoiceAge]
,[HoldAmount]
,HoldAmount-Sum([InvoiceTotal]) Over (Partition By [CustomerID] Order By InvoiceAge, [InvoiceTotal] Rows Unbounded Preceding) As [RemainingHold]
From @invoices)
Select *, CASE WHEN LAG(RemainingHold,1,HoldAmount) OVER (Partition By [CustomerID] Order By InvoiceAge, [InvoiceTotal]) > 1 then 1 else 0 end as Hold
From CTE
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 8, 2016 at 7:30 am
If I may restate the problem:
You want to hold any invoice that had a remaining hold amount prior to the invoice, whether or not the invoice itself exceeds the hold amount.
with cte as (
Select [rowid]
,[CustomerID]
,[InvoiceNumber]
,[InvoiceTotal]
,[InvoiceAge]
,[HoldAmount]
,HoldAmount-Sum([InvoiceTotal]) Over (Partition By [CustomerID] Order By InvoiceAge, [InvoiceTotal] Rows Unbounded Preceding) As [RemainingHold]
From @invoices)
Select *, CASE WHEN LAG(RemainingHold,1,HoldAmount) OVER (Partition By [CustomerID] Order By InvoiceAge, [InvoiceTotal]) > 1 then 1 else 0 end as Hold
From CTE
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 8, 2016 at 8:03 am
Perfect! That's exactly it! Brilliant!
Thanks for the help! 😀
September 8, 2016 at 8:29 am
You're very welcome. 😀
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 8, 2016 at 10:10 am
I believe that this does the same thing, but without the CTE. It was impossible to do a performance comparison on such small data.
SELECT i.rowid, i.CustomerID, i.InvoiceNumber, i.InvoiceTotal, i.InvoiceAge, i.HoldAmount
,CASE WHEN ISNULL(SUM(i.InvoiceTotal) OVER(PARTITION BY i.CustomerID ORDER BY i.InvoiceAge, i.InvoiceTotal ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) < i.HoldAmount THEN 1 ELSE 0 END AS Hold
FROM @invoices i
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 8, 2016 at 10:32 am
Looks like it works to me, Drew.
I'd vote for your solution based on the compactness of the code. 😀
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply