Solved

Sql code example

Posted on 2011-02-24
4
432 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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks.
0
 

Author Comment

by:tesla764
Comment Utility
Thanks again. This has been very helpful.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

771 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

12 Experts available now in Live!

Get 1:1 Help Now