Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Sql code example

Posted on 2011-02-24
4
Medium Priority
?
463 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 1500 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

885 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