tesla764
asked on
Sql code example
I am looking for perhaps a more professional way to code this...
Given the following tables and sample data:
create table Company ( CompanyID int, CompanyName varchar(30) )
insert Company values (1,'Acme Construction')
insert Company values (2,'AAA Buildings')
create table Item ( ItemID int, ItemName varchar(10), UnitCost money )
insert Item values ( 1, 'Widget', 1.00) -- each unit $1
insert Item values ( 2, 'Sprocket', 2.00) -- each unit $2
create table ItemOrder ( ItemOrderID int, CompanyID int, ItemID int, OrderDate datetime, UnitsOrdered int )
insert ItemOrder values ( 1, 1, 1, '10/5/05', 3 ) -- Acme orders 3 widgets on 10/5
insert ItemOrder values ( 2, 2, 2, '11/1/05', 1 ) -- AAA Buildings orders 1 sprocket on 11/1
My manager says: I want a full listing of all orders for the month of October 2005.
The result set should be:
CompanyName, ItemName, OrderDate, UnitsOrdered, TotalOrderCost
Can anyone show me example code on a way to achieve this? I am learning and would love to see an experienced way of doing this.
Thanks.
Given the following tables and sample data:
create table Company ( CompanyID int, CompanyName varchar(30) )
insert Company values (1,'Acme Construction')
insert Company values (2,'AAA Buildings')
create table Item ( ItemID int, ItemName varchar(10), UnitCost money )
insert Item values ( 1, 'Widget', 1.00) -- each unit $1
insert Item values ( 2, 'Sprocket', 2.00) -- each unit $2
create table ItemOrder ( ItemOrderID int, CompanyID int, ItemID int, OrderDate datetime, UnitsOrdered int )
insert ItemOrder values ( 1, 1, 1, '10/5/05', 3 ) -- Acme orders 3 widgets on 10/5
insert ItemOrder values ( 2, 2, 2, '11/1/05', 1 ) -- AAA Buildings orders 1 sprocket on 11/1
My manager says: I want a full listing of all orders for the month of October 2005.
The result set should be:
CompanyName, ItemName, OrderDate, UnitsOrdered, TotalOrderCost
Can anyone show me example code on a way to achieve this? I am learning and would love to see an experienced way of doing this.
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks.
ASKER
Thanks again. This has been very helpful.
c.CompanyName,
i.ItemName,
io.OrderDate,
SUM(io.UnitsOrdered) [UnitsOrdered],
io.UnitsOrdered * i.UnitCost [TotalOrderCost]
FROM
dbo.Company c JOIN dbo.ItemOrder io
ON c.CompanyID = io.CompanyID JOIN dbo.Item i
ON io.ItemID = i.ItemID
WHERE
io.OrderDate >= '11/1/05'
AND io.OrderDate < '12/1/05'
GROUP BY
c.CompanyName,
i.ItemName,
io.OrderDate,
io.UnitsOrdered,
i.UnitCost