INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Subquery help SQL Server

Subquery help SQL Server

(OP)
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


RE: Subquery help SQL Server

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

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

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Subquery help SQL Server

(OP)
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

RE: Subquery help SQL Server

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?

RE: Subquery help SQL Server

(OP)
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

RE: Subquery help SQL Server

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

[pre]
Some  data
123    XYZ 
[/pre]

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

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close