Subquery help SQL Server 2

[jchewsmith]

I have a table that includes order number, item# and tax% all products, I want to create a table that has copies of info from this table and adds a line for shipping and on that line for shipping I want to include the tax % the same as the order items.

So the original tables holds this info:
Order 12345 item xyz 5.5 $10.00
Order 12345 item lmo 5.5 $12.00
Order 12346 itme abc 5.2 $15.00

I want to create a new table that includes all of the above and adds a shipping line

New Table:

Order 12345 item xyz 5.5 $10.00
Order 12345 item lmo 5.5 $12.00
Order 12345 Shipping 5.5 $2.50
Order 12346 item abc 5.2 $15.00
Order 12346 Shipping 5.2 $3.00

 

[Andrzejek]

That's really weird way to show your data, at least to me.

Is that what you have (in TableX):
[pre]
Order item tax amount
12345 xyz 5.5 $10.00
12345 lmo 5.5 $12.00
12346 abc 5.2 $15.00
[/pre]
and that's what you would like to have (in TableY):
[pre]
Order item tax amount
12345 xyz 5.5 $10.00
12345 lmo 5.5 $12.00
12346 abc 5.2 $15.00[blue]
12345 Shipping 5.5 $2.50
12346 Shipping 5.2 $3.00[/blue]
[/pre]
???

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[jchewsmith]

Sorry I have had an issue with spacing of data in the past so I try to limit the indents that I use in this format. Also I hit submit before I added my question.

My question is how to write the query (with a subquery) to create the info in the second table from the first table adding the appropriate tax to the shipping lines based on the rate that is used on the product lines.

Thanks

 

[DaveInIowa]

How do you arrive at $2.50 for 5.5% of $10 (0.55) + 5.5% of $12 (0.66) = (1.21)? Also what if the tax percentages are not equal within the same order?

 

[jchewsmith]

I apologize to all. I should have been way more specific.

I am not showing the tax amount above just the percent that should be charged.

What I am trying to accomplish is to add a shipping line and in the process find the taxrate from the original order and include that in the update process.

I have a table called OrderLine that I want to update with the Shipping info.
Order# LineType TaxRate Amt
123466 Item 5.5 50.00
124355 Item 6.0 75.00



I have another table called ShippingByOrder with the shipping info for each order. This table includes:

Order# ShippingAmt
123466 5.00

I want to add all orders from the shippingByOrder table into the OrderLine table and I want to copy the taxrate from the corresponding order# that is already in the OrderLine Table



INSERT INTO [OrderLine]
(
[LineType] = 'Shipping' ,
ShippingByOrder.[Order#] ,
[TaxRate], (This needs to be updated by linking to the order# already in the OrderLine table - there will always only be one rate per order)
ShippingByOrder.[ShippingAmt]
)

Sorry for the confusion.

Thanks

 

[Andrzejek]

an issue with spacing of data " use PRE tgml tags:

[ignore][pre][/ignore]
[pre]
Some data
123 XYZ[/pre][ignore][/pre][/ignore]

"I hit submit before I added my question" you can always EDIT your post

I would start by creating the Select statement to get the records you want to Insert:

SELECT DISTINCT Order, 'Shipping', TaxRate
FROM OrderLine

and you need to connect to ShippingByOrder table to get the other info.

And then simply use the SELECT statement in your INSERT statement:

INSERT INTO OrderLine
(Order, item, tax, amount)
SELECT DISTINCT ...

BTW - If you already have all of the data available somewhere in your data base, why copy it?
Why not create a View with the data you have? One piece of data in one place and one place ONLY.

Have fun.

---- Andy

There is a great need for a sarcasm font.