SQL - Update related table
-
CREATE TABLE #Bills
(
BillNo INT identity,
Account INT,
Amount NUMERIC(10,2),
Paid BIT
)
INSERT #Bills (Account, Amount, Paid) Values (45, 200, 0)
INSERT #Bills (Account, Amount, Paid) Values (45, 25, 0)
INSERT #Bills (Account, Amount, Paid) Values (45, 300, 0)
INSERT #Bills (Account, Amount, Paid) Values (45, 50, 0)
INSERT #Bills (Account, Amount, Paid) Values (45, 125, 0)
INSERT #Bills (Account, Amount, Paid) Values (45, 150, 0)
INSERT #Bills (Account, Amount, Paid) Values (50, 200, 0)
INSERT #Bills (Account, Amount, Paid) Values (50, 25, 0)
INSERT #Bills (Account, Amount, Paid) Values (50, 25, 0)
INSERT #Bills (Account, Amount, Paid) Values (50, 50, 0)
INSERT #Bills (Account, Amount, Paid) Values (50, 125, 0)
INSERT #Bills (Account, Amount, Paid) Values (50, 75, 0)
GO
CREATE TABLE #Payments
(
PaymentID INT identity,
Account INT,
Amount NUMERIC(10,2)
)
INSERT #Payments (Account, Amount, Paid) Values (45, 150)
INSERT #Payments (Account, Amount, Paid) Values (50, 100)
GO
My work is to update #Bills table for which bills are paid. For example Account 50 has paid value of 100. So I can update #Bills for a 75 and 25 or 25, 25, 50. I am doing this via loop. The table has about 10000 rows per month and it takes forever. Is there a easy way to do it. Data is coming in text files and uploaded every month.
Regards,
gova -
A couple of questions. Your example assumes that a payment will always equal a bill or the sum of several bills. What happens when a customer makes a partial payment. BillNo1 is $100 a payment of $75 is recieved Leaving a balance of $25 owed? Are payments applied using FIFO (first in first out) where all payments would be applied to the oldest bill until it was paid in full before payments were applied to other bills? What happens when a customer makes an overpayment. Bill = $100 and customer pays $150?
Mike
-
This query will give you a running total of a customers account
HTH Mike
SELECT b.Account As Account#,
Sum(DISTINCT b.Amount) as "Total Billed",
Sum(DISTINCT P.Amount) AS "Total Payments",
(Sum(DISTINCT b.Amount) - Sum(DISTINCT P.Amount)) AS "Balance"
From
#Bills B
INNER Join
#Payments P
ON
P.Account = B.Account
Group by
B.account
/*
Returns
Account#Total billed Total PD Balance l
-------------------------------------------
45 850.00 150.00 700.00
50 475.00 100.00 375.00
(2 row(s) affected)
*/
-
Thanks Michael.
Only bills will be paid. It is possible to make two or more bills together but not partial payments.
Finding the balance amount and paid amount for an account is easy.
The task is to generate a report stating which are the bills paid. It is allowed to mark any bill(s) which will satisfy the paid amount. (As stated $100 can be for a bill of $100 or for bills $50 + $50 or $25 + $50 + $25)
I am now checking everything in a loop. Checking single values. Then sum(2 rows) then Sum(3rows). It takes forever to complete the process. Possible solutions can be in SQL or C#. Since datafeed loads the table from a text file (BCP from external source) I am not creating a trigger.
This is really a challenging logic to develop.
Regards,
gova -
Question does the text file contain both new bills, and payments or just payments? What are the business rules for excess amounts since you do not record part payments (do you send these amounts back to the customer)? Would you post the code you are now using.
Thanks
Mike
-
Sorry for delayed replies. Memorial Day Week End
Our application is billing. We generate bills.
We then pass all our bills to an accounting application. Accounting application provides datafeeds each month in a text file. There will not be a partial from a customer. It will be paid for a bill or bills.
Current code we don't have net access in the development machines.
The logic would be
Step1 : If payment.Amount = Any of bill.Amount Update bill.paid = 1 where... 'one bil
Step2 : If payment.Amount = Sum(bill.Amount) Update bill.paid = 1 where... 'all bills
Step3 : If payment.Amount = Sum(any 2 rows of bill.Amount) Update bill.paid = 1 where... '2 bills
Step4 : If payment.Amount = Sum(any 3 rows of bill.Amount) Update bill.paid = 1 where... '3 bills
Goes until no of unpaid bills.
To be honest I can't make a 100% perfect logic after step 2. Any help in the logic would be great. Not an easy task I would say.
Regards,
gova -
It ate my post. The logic is not to bad you can use a bubble sort to find the values that are equal to a payment. (Assuming that a payment will always be equal to a bill or the sum of one or more bills.) The logic is to order the bills desc (highest to lowest) then for each payment search the list until you find a bill that is smaller or equal to payment. If the bill equals the payment mark it paid and remove it from the array. If not you make successive passes through the list adding the values until you reach one that matches the payment. Somewhere I have the code for a bubble sort that can be modified to meet your needs. Example: Bills {100,90,50,25,25,10} Payment = 110. Since this is larger than the largest value in bills add each value until you have a hit. 100 + 90 > 110 then 100+50 > 110 ...100+10 = 110 a hit mark those two bills paid leaving bills {90,50,25,25} another payment of 100: bubble through 90+50,90+25,90+25, 50+25 (here since 50+25 = 75 less than 100 we know that we will have to add three values so adding the next value 50+25+25 bingo mark thest 3 paid leaving {90} if the next payment is not 90 off goes the nasty gram to accounting. 
If you would like I will get you the code for the bubble sort. Unless and I hope they do as this will be very very slow someone else has a better answer.
Mike
-
Lost another post and I have to run but
Try this to get rid of exact matches first.
HTH Mike
DECLARE payments_cursor cursor FOR
SELECT *
FROM PAYMENTS P
INNER JOIN Bills b
on b.amount = p.amount
WHERE b.paid = 0 and p.paid = 0
OPEN PAYMENTS_CURSOR
FETCH NEXT FROM PAYMENTS_cursor
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE BILLS SET PAID = 1
WHERE CURRENT OF PAYMENTS_CURSOR
UPDATE Payments SET Paid = 1
WHERE current of PAYMENTS_CURSOR
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM PAYMENTS_cursor
END
CLOSE PAYMENTS_cursor
DEALLOCATE PAYMENTS_cursor
GO
-
Thanks MIKE. I eliminate exact matches and Sum of matches as per code below.
My code is some thing similar to this. I(Any way I convinced accounts to add a Bill field in their payments. Now the ball went to their court(Hope it will not come back). Still if there is an easy solution I will be glad to have it)
SET NOCOUNT ON
GO
DROP TABLE #Bills, #Payments
GO
CREATE TABLE #Bills
(
BillNo INT identity,
Account INT,
Amount NUMERIC(10,2),
Paid BIT
)
INSERT #Bills (Account, Amount, Paid) Values (45, 200, 0)
INSERT #Bills (Account, Amount, Paid) Values (45, 25, 0)
INSERT #Bills (Account, Amount, Paid) Values (45, 300, 0)
INSERT #Bills (Account, Amount, Paid) Values (45, 50, 0)
INSERT #Bills (Account, Amount, Paid) Values (45, 125, 0)
INSERT #Bills (Account, Amount, Paid) Values (45, 150, 0)
INSERT #Bills (Account, Amount, Paid) Values (50, 200, 0)
INSERT #Bills (Account, Amount, Paid) Values (50, 25, 0)
INSERT #Bills (Account, Amount, Paid) Values (50, 25, 0)
INSERT #Bills (Account, Amount, Paid) Values (50, 50, 0)
INSERT #Bills (Account, Amount, Paid) Values (50, 125, 0)
INSERT #Bills (Account, Amount, Paid) Values (50, 75, 0)
INSERT #Bills (Account, Amount, Paid) Values (55, 75, 0)
INSERT #Bills (Account, Amount, Paid) Values (55, 25, 0)
INSERT #Bills (Account, Amount, Paid) Values (55, 35, 0)
INSERT #Bills (Account, Amount, Paid) Values (55, 15, 0)
INSERT #Bills (Account, Amount, Paid) Values (60, 250, 0)
INSERT #Bills (Account, Amount, Paid) Values (60, 250, 0)
INSERT #Bills (Account, Amount, Paid) Values (60, 450, 0)
INSERT #Bills (Account, Amount, Paid) Values (60, 100, 0)
GO
CREATE TABLE #Payments
(
PaymentID INT identity,
Account INT,
Amount NUMERIC(10,2)
)
INSERT #Payments (Account, Amount) Values (45, 150)
INSERT #Payments (Account, Amount) Values (50, 100)
INSERT #Payments (Account, Amount) Values (55, 150)
INSERT #Payments (Account, Amount) Values (60, 250)
INSERT #Payments (Account, Amount) Values (60, 350)
GO
/* UPDATES WHEN SINGLE AMOUNT MATCHES */
UPDATE #Bills
SET
Paid = 1
FROM
(SELECT MAX(BillNo) BillNo, Account, Amount
FROM
#Bills
GROUP BY Account, Amount) Bills
JOIN #Payments
ON #Payments.Account = Bills.Account
WHERE
#Bills.BillNo = Bills.BillNo AND
#Payments.Account = Bills.Account AND
#Payments.Amount = Bills.Amount
/* UPDATES WHEN TOTAL SUM AMOUNT MATCHES */
UPDATE #Bills
SET
Paid = 1
FROM
(SELECT Bills.Account, Bills.Amount
FROM
(SELECT Account, SUM(Amount) Amount
FROM
#Bills
WHERE
Paid = 0
GROUP BY Account) Bills
JOIN #Payments
ON #Payments.Account = Bills.Account AND
#Payments.Amount = Bills.Amount ) Payments
WHERE
Payments.Account = #Bills.Account
/* UPDATES WITH SUM OF TWO ROWS */
DECLARE @account INT
DECLARE @Amount NUMERIC(10,2)
DECLARE @Ctr INT
DECLARE @CurCtr INT
DECLARE Payments_cursor CURSOR FOR
SELECT Account, Amount
FROM
#Payments
OPEN Payments_cursor
FETCH NEXT FROM Payments_cursor INTO @account, @Amount
WHILE @@FETCH_STATUS = 0
BEGIN
IF EXISTS (SELECT * FROM #Bills WHERE Account = @account AND Paid = 0)
BEGIN
PRINT STR(@Account) + ' - ' + STR(@Amount)
/* I am lost here I don't have a perfect logic to update values */
END
FETCH NEXT FROM Payments_cursor INTO @account, @Amount
END
CLOSE Payments_cursor
DEALLOCATE Payments_cursor
Regards,
gova -
Hi the following client side code (vb 6.0) will give you the results you want. Another thought is that since you must use row scans if you have a large number of rows in your bills and payments tables from previous months (paid = 1) this will cause a major hit to performance.
HTH Mike
p.s. If you test this code aganist a copy of actual data would you let me know how it works compared to your current code. Thanks Mike
***********************************
edited to add ALL the code I have got to get this cut and paste thingy under control.
***********************************
Private Sub UpdatePayments(cn As Connection, Account As Integer)
On Error GoTo ErrorHandler
Dim rs As New ADODB.Recordset
Dim SQL As String
SQL = "UPDATE Payments SET Paid = 1" _
& " WHERE Account =" & Account
rs.Open SQL, cn
Exit_sub:
Set rs = Nothing
Exit Sub
ErrorHandler:
MsgBox Err.Number & " " & Err.Description, vbOKOnly, "Error in UpdatePayments"
End Sub
Private Function Pay() As Boolean
On Error GoTo ErrorHandler
Dim cn As New ADODB.Connection
Dim SQL As String
Dim rsPayments As New ADODB.Recordset
Dim rsBills As New ADODB.Recordset
Dim TotalPayments As Currency
Dim TotalBills As Currency
Dim CurrentAccount As Integer
Dim AryBillsPaid() As Integer
Dim count As Integer
Dim x As Integer
rsPayments.CursorType = adOpenKeyset
rsPayments.LockType = adLockOptimistic
rsBills.CursorType = adOpenKeyset
rsBills.LockType = adLockOptimistic
cn.CursorLocation = adUseClient
cn.Provider = "SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Test;Data Source=MICHAEL-NIFJLS1\mikesdb"
cn.Open
SQL = "SELECT sum(amount) as pAmount, account as PAccount" _
& " FROM Payments" _
& " WHERE Paid = 0" _
& " GROUP BY Account"
rsPayments.Open SQL, cn
rsPayments.MoveFirst
While Not rsPayments.EOF Or rsPayments.BOF
TotalPayments = rsPayments.Fields("PAmount")
CurrentAccount = rsPayments.Fields("PAccount")
SQL = "SELECT max(Amount)AS Amount,BillNo,Paid" _
& " FROM Bills" _
& " WHERE Account =" & CurrentAccount _
& " AND Amount <=" & TotalPayments _
& " AND Paid = 0" _
& " GROUP BY amount,BillNo,Paid"
With rsBills
.Open SQL, cn
.MoveFirst
Do While Not .EOF Or Not .BOF
count = count + 1
ReDim Preserve AryBillsPaid(count)
AryBillsPaid(count) = .Fields("BillNo")
TotalBills = TotalBills + .Fields("Amount")
If TotalBills = TotalPayments Then
For x = 1 To count
If AryBillsPaid(x) <> 0 Then
.MoveFirst
.Find "BillNo =" & AryBillsPaid(x)
.Update "Paid", 1
End If
Next
UpdatePayments cn, rsPayments.Fields("pAccount")
rsBills.Close
Exit Do
End If
If TotalBills > TotalPayments Then
AryBillsPaid(count) = 0
TotalBills = TotalBills - .Fields("Amount")
End If
rsBills.MoveNext
Loop
End With
count = 0
TotalBills = 0
rsPayments.MoveNext
Wend
Exit_sub:
rsPayments.Close
cn.Close
Set rsPayments = Nothing
Set cn = Nothing
Exit Function
ErrorHandler:
MsgBox Err.Number & " " & Err.Description, vbOKOnly, "Error in pay"
End Function
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply