Solved

Sql code example

Posted on 2011-02-24
4
436 Views
Last Modified: 2012-08-14
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.
0
Comment
Question by:tesla764
  • 2
4 Comments
 
LVL 8

Accepted Solution

by:
infolurk earned 500 total points
ID: 34976225
select c.CompanyName, i.ItemName, o.OrderDate, o.UnitsOrdered, o.OrderedUnits * i.UnitCost as TotalOrderCost
from Company c
join
ItemOrder o
on c.CompanyId = o.CompanyID
Join
Item i
on o.ItemID = i.ItemID

If the unitcost changes over time and you want to record the total order cost at the time of the transaction, you may want to store TotalOrderCost in the ItemOrder table.
0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 34976281
SELECT
      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                
0
 

Author Closing Comment

by:tesla764
ID: 34977514
Thanks.
0
 

Author Comment

by:tesla764
ID: 34977540
Thanks again. This has been very helpful.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now