SQL statement problem

  • 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.

  • 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

  • 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

    ;

  • 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