January 8, 2009 at 12:53 pm
I need to join the order header and order detail tables; and create a new column calc based on header value which is divided by number of detail records for that order number.
I would like an example to list the result, and another to list the summary of the new calculated column.
Ex: If the order header credit amount is $50; and there are 5 detail records for that order number, the new value would be 50/5 = $10.
January 8, 2009 at 1:11 pm
You extinguished my desire to help you by the fact that you did not provide sample scripts.
And this is not the first time you are coming on this forum ....
Anyways - here is my take on what you need
Select myNewColumn, h.order_number
From order_header h
join order_detail d
on h.order_id=c
join (select order_id , count(order_id) as myNewColumn from order_header group by order_id) temp
on temp.order_id = d.order_id
January 8, 2009 at 1:39 pm
Here is a working and tested example. Please post your data next time, it is really a lot longer for us to help you when we cannot test what we write.
IF EXISTS (SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].orderHeader')
AND TYPE IN (N'U'))
DROP TABLE [dbo].orderHeader
CREATE TABLE orderHeader (
orderHeaderId INT IDENTITY ( 1 , 1 ),
rebate MONEY)
INSERT INTO orderHeader
(rebate)
SELECT 50
UNION ALL
SELECT 25
UNION ALL
SELECT 20
UNION ALL
SELECT 10
IF EXISTS (SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].orderDetail')
AND TYPE IN (N'U'))
DROP TABLE [dbo].orderDetail
CREATE TABLE orderDetail (
orderHeaderID INT,
orderDetailID INT IDENTITY ( 1 , 1 ),
orderDesc NVARCHAR(50))
INSERT INTO OrderDetail
(orderHeaderId,
ORderDesc)
SELECT 1,
'Socks'
UNION ALL
SELECT 1,
'Skis'
UNION ALL
SELECT 1,
'Poles'
UNION ALL
SELECT 1,
'Boots'
UNION ALL
SELECT 2,
'Bra'
UNION ALL
SELECT 2,
'Thongs'
UNION ALL
SELECT 3,
'VolleyBall'
UNION ALL
SELECT 3,
'BasketBall'
UNION ALL
SELECT 3,
'Football'
UNION ALL
SELECT 3,
'Hockey Puck'
SELECT d.orderHeaderID,
orderDetailID,
orderDesc,
rebate AS RebateBeforeDivision,
rebate
/ TotalDetails AS RebateAppliedToEachLine
FROM ORderDetail d
INNER JOIN OrderHeader h
ON d.OrderHeaderId = h.OrderHeaderID
INNER JOIN (SELECT orderHeaderID,
COUNT(* ) AS TotalDetails
FROM OrderDetail
GROUP BY orderHeaderID) amount
ON d.OrderHeaderID = amount.orderHeaderID
Cheers,
J-F
Cheers,
J-F
January 8, 2009 at 1:49 pm
January 8, 2009 at 1:55 pm
No worries, my script was not even working until I got some test data, 😉
And I also forgot to divide by the count, hehe!
Cheers,
J-F
Cheers,
J-F
January 8, 2009 at 2:22 pm
Yes, this is not my first time here and yes I will do a better effort to provide both data and script.
I just really did not know how to begin writing the script for this issue; I was assuming this was a common issue and someone would have an example.
I really appreciate everyone's help on this..thanks again!!
January 8, 2009 at 4:01 pm
When we say "script", we aren't referring to the script to solve your problem, although if you would like to show what you've attempted thus far, we can help you understand where you went wrong (if you went wrong at all).
We mean we want a "script" that creates a table that mimics yours and loads sample data into it. See the article in my signature on how to provide sample data for a full explanation of what this entails.
It's a step that you can do regardless of how little an understanding you have of what needs to be done, and it makes it much easier on us to work with your problem. In some cases, the code is really simple, but even when we could write it in our sleep, we'll make a simple mistake when we can't test it to make sure it's working (my latest one is leaving the group by's out of my derived tables, grrrr).
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply