query to show header, lines and footer

[akalinowski]

OK, so we are moving to a new point of sale that has a sql back end, from retail pro... we hve some import/exports that happen between our pos and erp, one of them is formated in a certain way and it would cost some $$$ to get the erp re-programmed so i was hoping some one could point me in the right direction as to how to do this:

the query would need to pull each invoice (receipt) like this:

H,Receipt_Number,Date,Store
I,Item_UPC, Quantity, PRICE
I,Item_UPC, Quantity, Price
I,..this continues for however many lines on the receipt
S, Tax%, tax$, Discount$

the H, I and S just are there to indicate if its a header, line item or 'subheader?' ( i didnt write this i inhereted it)

so a real file would look like this

H,1234,20091022,1
I,343253253454,1,19.99
I,532234424345,1,5.00
S, 8.75,2.18,0
H,1235,20091022,1
I,343253253454,1,19.99
S, 8.75,1.75,0


using sql i can query all the data i need, i just dont know how to format it in that manner

thanks.

akalinowski

http://www.olivelabs.com

 

[simian336]

I think to answer you question we would need to see some actual data from the tables involved.

Simi

 

[akalinowski]

lets say there is a recipt header table looks like this:

tb_receipt_header:
receipt_num,date, cashier, total_sale, total_tax, total_disc

and detail

tb_receipt_detail:
receipt_num, upc, price, qty, tax_perc, disc_amnt

ty

akalinowski

http://www.olivelabs.com

 

[simian336]

So you are looking for a report like the reciept or an actual receipt? Or possibly a table that looks like the receipt? Please explain...

Simi

 

[akalinowski]

yeah it looks like the receipt and has the flags of 'H' 'I' and 'S' to sort out what the row means.


i would have doen it with each row having all the info, but retail pro doesnt allow for this in its export utility 'bridge' too late now because the import utility (into a providex accounting datbase) is already programmed and i dont know pvx

akalinowski

http://www.olivelabs.com

 

[simian336]

If it was me... I would create a new table with Detail character field. I would create queries that would insert my H,I,S rows into that field as one comma delmited field.

Then you could sort your data on Line_type, Receipt_Number, Date, Store etc and export the Detail field to a file or recipt...

Line_type,Receipt_Number,Date,Store,detail

1,1234,20091022,1,'H,1234,20091022,1'
2,1234,20091022,1,'I,343253253454,1,19.99'
2,1234,20091022,1,'I,532234424345,1,5.00'
3,1234,20091022,1,'S, 8.75,2.18,0'

If that makes since.

Simi

 

[akalinowski]

i believe this may be my answer:

http://www.w3schools.com/sql/sql_union.asp

union the 2 queries

and since there are less columns in my subheader than my item i will do a '' as col4

i' havent written the query yet, but i'll post what i find.

thanks guys.

akalinowski

http://www.olivelabs.com

 

[gmmastros]

When you use UNION, you need to make sure all queries return the same number of columns, and that the data type for each column is the same. In this case, you'll probably want to use varchars. In this case, I would recommend that each query simply return a single column. It will simplify things for your.

For example:

Select 'H,'
       + Convert(VarChar(20), receipt_num) + ','
       + Convert(VarChar(20), date) + ','
       + Convert(VarChar(20), store)
From   tb_receipt_header

Union All

Select 'I,'
       + Convert(VarChar(20), receipt_num) + ','
       + upc + ','
       + price
From   tb_receipt_detail

Union All

--Another select here

The only problem you may run in to would be with ordering the rows in the output. This may or may not be important depending on your situation.

Also, you probably want to use UNION ALL (like I did in my example) instead of UNION. The difference is... UNION filters duplicates. If you don't have any duplicates because of the nature of your data, SQL will still go through the effort to make sure there aren't any. Therefore, UNION ALL would return the same results, but it would be faster.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom