Help with aggregate query - Cant get it right!

Hi,

I have we have a client who gives their invoices in a flat file format, we import it into a SQL Server table.

Nothing is normalized – everything is repeated in every record. The fields are:

customerNumber
Invoice_number
PO_number
Qty
Description
Line_number
Line_total
Freight
Tax
Invoice_date

So an if an order has 10 line items, the header information (invoice number, PO number, ivoice date) are repeated on each of the lines

I am writing a query to show the following

Order number, Invoice total, Date

select invoice_no, sum(line_total + freight + tax) as invoiceTotal, customerNumber, Invoice_date from invoices group by invoice_no, Invoice, customerNumber

This works great - for each invoice I get the invoice number, InvoiceTotal, and Date

Then I was asked to add the PO Number – this is where I can't get it right.

When I added "PO_number" to the query, I got two lines for each invoice

select invoice_no, sum(line_total + freight + tax) as invoiceTotal, customerNumber, Invoice,PO_number from invoices group by invoice_no, Invoice, Sold_To_Cust_No,PO_number

Please help - I need to end up with: invoice_no, invoiceTotal, customerNumber, Invoice_date and PO_number (sequence does not matter)

Thanks

Hello my friend,

I see the problem. You want to get a field value without it causing other data to repeat. The solution is to use a function to get the one value and put this in the query like so: -

CREATE FUNCTION fn_GetPONumberByInvoice
(
@.InvoiceNumber AS VARCHAR(50) -- if it is an INT or whatever then change this line
)
RETURNS VARCHAR(80)

AS

BEGIN
DECLARE @.PONumber AS VARCHAR(80)

SET @.PONumber = (SELECT TOP 1 PO_Number FROM Invoices WHERE Invoice_Number = @.InvoiceNumber)

RETURN @.PONumber
END

Then in your query use the following: -

SELECT invoice_no,
sum(line_total + freight + tax) as invoiceTotal, customerNumber,
Invoice_date,
dbo.fn_GetPONumberByInvoice(invoice_no) AS 'PO_Number'
FROM invoices
GROUP BY invoice_no, Invoice, customerNumber

Kind regards

Scotty

|||

Scotty, thanks for your reply.

I am not well versed in SQL, so I have a question:

How can I use the query you wrote to create a view? I did not mention that earlier (sorry), but the objective is to create a view with header information for each invoice (invoice number, date, total amount and PO number)

Hope you can help.

Again, thanks.

|||

Hello again my friend,

You should be able to do it like this: -

CREATE VIEW vw_Invoices

AS

SELECT invoice_no,
sum(line_total + freight + tax) as invoiceTotal, customerNumber,
Invoice_date,
dbo.fn_GetPONumberByInvoice(invoice_no) AS 'PO_Number'
FROM invoices
GROUP BY invoice_no, Invoice, customerNumber

Kind regards

Scotty

|||

Scotty, THANK YOU.