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.

Help to encrypt a flat file destination

I am using SSIS to create a weekly data extraction that will be emailed to an external agency. I can extract the data, create the file and email it without any problems but I want to compress and encrypt it before I send to give some measure of protection to sensitive data it would contain.

If I did this manually, I would simply use Winzip to compress and encrypt the file to be sent. How can I achieve a similar result programtically?

Set a step to run a command, and use the command line functionality of Winzip.

Help --SSIS Dataflow Task

Need help regarding ssis dataflow task
I need to create a ssis package. I want to import the data from a flat
file to a table.
Lets say, the table has 5 columns -- col1, col2, col3, col4 ,
col5.(Assume that all columns can be NULLABLE) The datafile contains
the data related to only three columns say col1, col2, col3. So when I
use dataflow task to import the data from the file to the table, I
will only get three columns, col1, col2, col3. Columns col4, col5 will
be NULL.
However, I want to populate columns col4, col5 with some values which
are stored in the variable.
IS there any way to do this'
Any help would be appreciated.
ThanksVishal wrote:
> Need help regarding ssis dataflow task
> I need to create a ssis package. I want to import the data from a flat
> file to a table.
> Lets say, the table has 5 columns -- col1, col2, col3, col4 ,
> col5.(Assume that all columns can be NULLABLE) The datafile contains
> the data related to only three columns say col1, col2, col3. So when I
> use dataflow task to import the data from the file to the table, I
> will only get three columns, col1, col2, col3. Columns col4, col5 will
> be NULL.
> However, I want to populate columns col4, col5 with some values which
> are stored in the variable.
> IS there any way to do this'
> Any help would be appreciated.
> Thanks
Suppose I have one variable which contains some value. @.[User::exp1]
Now create one more variable which contains source query.
set evaluateasexpression as true for this variable.
set expression like this
"select col1,col2,col3,'"+@.[User::exp1] + "' as col4 from a1"
In Data flow task in source select sql command from variable and select
this variable.
Regards
Amish Shah
http://shahamishm.tripod.com