Posted on 2012-03-19
I need to write a query to update a table Billed with all activity for that record and any records previous to that date.
BillNO date Allow Payrec ADjAmt tot Type
1 1/1/2012 45.94 0 0 45 INV
1 1/10/2012 0 27.46 0 18.48 Chk
1 1/10/2012 0 18.48 0 0 Chk
2 1/2/2012 60.00 0 0 60.00 INV
2 1/5/2012 0 0 15.00 0 45.00 ADJ
2 1/6/2012 15.00 0 0 60.00 ADJ
2 2/7/2012 0 40.00 0 20.00 Chk
I need to take any entries in Allow and subtract any amounts in Payrec and AdjAmt and insert the total into tot. Then on the subsequent records take the Tot from the previous record and add or subtract the Allow, Payrec and AdjAmt to obtain a new total using Date to sort by.
How do I go about doing this? I believe I need a recursive query, but I don’t know how to go about writing this?
As a first attempt I wanted to just print out the records before I tried to accumulate totals:
This is what I wrote:
WITH BaseBillNO (BillNO_NoDash, Allowed, Payrec, AdjustAmt,DateBilled, HierarchyLevel) AS
-- Base case
1 as HierarchyLevel
WHERE Descript = 'INV'
and BillNO_NoDash = 56793 and DELFLAG = 0
-- Recursive step
pr.HierarchyLevel + 1 AS HierarchyLevel
FROM (Select *
where Descript <> 'INV' and BillNO_NoDash = 56793
INNER JOIN BaseBillNO pr ON
B.BillNO_NoDash = pr.BillNO_NODash
where BillNO_NoDash = 56793 and HierarchyLevel < 10
ORDER BY BillNO_NoDash, HierarchyLevel, Allowed, Payrec, AdjustAmt,DateBilled
I am getting an error: The maximum recursion 100 has been exhausted before statement completion.
Any thoughts would be greatly appreciated.