Solved

Sql code example

Posted on 2011-02-24
4
444 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example, show how to take different types of Oracle backups using RMAN.
Viewers will learn how the fundamental information of how to create a table.

738 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