June 25, 2002 at 1:45 pm
I have four tables for billing purposes, tblInvoices, trelInvoiceItes, tblItems, tblInvoicePayments. My goal it to use one sproc to return the invoice amount (SUM(tblItems.strCost)) and the amount already paid to the invoice (SUM(tblInvoicePayments.strAmount)) Hopefully you can figure this out but the invoice table is connected to the items table by trelInvoiceItems and the invoice payments table is directly connected to the invoice table with the invoice id. Any help would be appreciated. Thanks.
Joe Wright
June 25, 2002 at 2:31 pm
create a stored procedure with three parameters: @InvoiceNum, @InvoiceTotal, and @InvoicePayments
in your sproc you need to write two sql queries, one to get the Total Invoice:
SELECT @InvoiceTotal=SUM(b.strCost)
FROM trelInvoiceItes a,
tblItems b
WHERE a.Invoiceid=@InvoiceNUM
AND a.ItemId=b.ItemId
the other query is to determine the total payments made;
SELECT @InvoicePayments=SUM(a.strAmount)
FROM tblInvoicePayments a
WHERE a.InvoiceId=@InvoiceNum
June 25, 2002 at 2:33 pm
Can you post some DDL to be sure we get this right?
I think you want a single result set, correct?
select sum(a.strCost)
sum( b.strAmount)
from tblItems a
inner join tblInvoicePayments b
on a.treinvoiceitems = b.treinvoiceitems
does not work?
Steve Jones
June 25, 2002 at 3:03 pm
Sorry about being so ambiguous. I do want to have one result set. Here are the two statements i currently use:
This adds up the cost of all items for an invoice.
SELECT tblInvoices.intInvoiceID, SUM(tblItems.curPrice) AS strCost
FROM tblInvoices INNER JOIN
tblInvoiceItems ON tblInvoices.intInvoiceID = tblInvoiceItems.intInvoiceID INNER JOIN
tblItems ON tblInvoiceItems.intItemID = tblItems.intItemID
WHERE (tblInvoices.intInvoiceID = 88)
This adds up the payments.
SELECT i.intInvoiceID, SUM(ip.strAmount) AS strAmount
FROM tblInvoices i LEFT OUTER JOIN
tblInvoicePayments ip ON i.intInvoiceID = ip.intInvoiceID
WHERE (i.intInvoiceID = 88)
GROUP BY i.intInvoiceID
This is my first time using this forum and i'm starting to really appreciate it. Thanks guys.
June 25, 2002 at 3:10 pm
You could union these if you did not need the sums on the same row. If you do, then I would use each of these as a sub-select. that's the simplest. Could be more efficient, but I'm a little busy.
select a.intInvoiceID
, strCost
, strAmount
from (
SELECT
tblInvoices.intInvoiceID
, SUM(tblItems.curPrice) AS strCost
FROM tblInvoices INNER JOIN
tblInvoiceItems ON tblInvoices.intInvoiceID = tblInvoiceItems.intInvoiceID INNER JOIN
tblItems ON tblInvoiceItems.intItemID = tblItems.intItemID
WHERE (tblInvoices.intInvoiceID = 88)
) a
inner join (
SELECT i.intInvoiceID, SUM(ip.strAmount) AS strAmount
FROM tblInvoices i LEFT OUTER JOIN
tblInvoicePayments ip ON i.intInvoiceID = ip.intInvoiceID
WHERE (i.intInvoiceID = 88)
GROUP BY i.intInvoiceID
) b
on a.intInvoiceID = b.intInvoiceID
Steve Jones
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply