June 28, 2012 at 10:31 am
Hi,
Im trying to get some code working but iv been at it for ages and cant get it going.
Im trying to list all unpaid invoices with the calculated total of all items bought on it.
It keeps listing all the records from the invoice details file of each individual item. I have a few
versions but this works and may show you what im doing.
SELECT tblInvoiceMain.Ship1 AS Expr2, tblInvoiceMain.OrgID AS Expr6, tblInvoiceMain.SalesPerson AS Expr4, tblInvoiceMain.InvoiceNumber AS Expr1,
tblInvoiceMain.InvoiceDate AS Expr3, tblInvoiceMain.DateShipped AS Expr5, tblInvoiceMain.Paid AS Expr7,
(tblInvoiceDetails.Quantity * tblInvoiceDetails.UnitPrice) * (1 - tblInvoiceDetails.Discount) AS cDis,
(tblInvoiceDetails.Quantity * tblInvoiceDetails.UnitPrice) * (1 - tblInvoiceDetails.Discount) * tblInvoiceDetails.GST AS cGST,
(tblInvoiceDetails.Quantity * tblInvoiceDetails.UnitPrice) * (1 - tblInvoiceDetails.Discount) * tblInvoiceDetails.HST AS cHST,
tblInvoiceMain.Misccharges AS cMis, tblInvoiceMain.ShippingHandling AS cShip, (tblInvoiceDetails.Quantity * tblInvoiceDetails.UnitPrice)
* (1 - tblInvoiceDetails.Discount) + (tblInvoiceDetails.Quantity * tblInvoiceDetails.UnitPrice) * (1 - tblInvoiceDetails.Discount)
* tblInvoiceDetails.HST AS cTotal
FROM tblInvoiceMain LEFT OUTER JOIN
tblInvoiceDetails ON tblInvoiceMain.InvoiceNumber = tblInvoiceDetails.InvoiceNumber
WHERE (tblInvoiceMain.Paid = 0) AND (tblInvoiceMain.OrgID = 2556) AND (tblInvoiceMain.SalesPerson <> N'HI')
Its a mess i know but i think i may need a subquery to get the sum of all the items in tblinvoicedetails for a specific invoicenumber and then list it in a column. It should only print one row for each invoice.
June 28, 2012 at 12:24 pm
i think you need to use a sum and group by
Simplified version below:-
select SUM(d.Amount),
H.InvoiceNumber
from InvoiceDetail d
join InvoiceHeader H on d.ID = h.ID
group by H.InvoiceNumber
regards
david
June 28, 2012 at 12:40 pm
select
x.*,
y.*
from
(
SELECT *
FROM tblInvoiceMain m
WHERE (m.Paid = 0) AND (m.OrgID = 2556) AND (m.SalesPerson <> N'HI')
) as x
LEFT JOIN
(
select
d.InvoiceNumber,
sum((d.Quantity * d.UnitPrice) * (1 - d.Discount)) AS cDis,
sum((d.Quantity * d.UnitPrice) * (1 - d.Discount) * d.GST) AS cGST,
sum((d.Quantity * d.UnitPrice) * (1 - d.Discount) * d.HST) AS cHST,
sum((d.Quantity * d.UnitPrice) * (1 - d.Discount) + (d.Quantity * d.UnitPrice) * (1 - d.Discount) * d.HST) AS cTotal
from
tblInvoiceDetails d
group by
d.InvoiceNumber
) as y
ON x.InvoiceNumber = y.InvoiceNumber
;
June 28, 2012 at 2:06 pm
Thanks, Il give that a try in when Im free.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy