PLEASE HELP! A few query problems!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

PLEASE HELP! A few query problems!

Author

Topic

omar3550
Starting Member

5 Posts

Posted - 2009-03-24 : 02:01:56

Hey guys, please refer to the following database diagram:

[url]http://img7.imageshack.us/img7/2808/database.jpg[/url]

I need the following:

1. SQL code to retrieve total value of sales made during Feb. 2009 disregarding the Discount field.
2. SQL code to find the OrderID of any records in the Orders table for which there are no matching records in the Order Details table.

If you could please help me out, I need this data base setup and running pretty soon, I'd appreciate it. Please see my follow up comments on what I have already tried, thanks!

Btw. i am using MS SQL Server 2005

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2009-03-24 : 04:01:11

is this a homework question? why not show us what you tried and we'll help you from there

Em

omar3550
Starting Member

5 Posts

Posted - 2009-03-24 : 04:11:24

quote:
Originally posted by elancaster

is this a homework question? why not show us what you tried and we'll help you from there

Em

No this is a database i am building for my work place i am an intern. I had 4 points originally of which 2 were solved by me already the above are the only ones left, I have no idea how to start. I tried the <> (not equal) on point 2 to see if a comparison can be made and it gave me an error. For the first one I used an inner join, couldn't get it to work- it involves several tables and adding attribute fields. I got it to take Price * UnitCost AS 'Total Value' but that would only calculate it for each record separately the problem calls for ALL records in February 2009. Anybody got any ideas?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2009-03-24 : 04:28:12

there is obviously more than 1 way to do this, below is just 1 way. you were on the right track using unit price * quantity, but you need to sum() them to get a total for a specific period, you could also group it by month then just pick out february afterwards.


declare @orderdetails table (orderID int, unitprice int, quantity int)
insert into @orderdetails (orderid,unitprice,quantity)
select	1,3,2 union all
select	2,4,1

declare @orders table (orderid int, orderdate datetime)
insert into @orders (orderid, orderdate)
select	1,'20090201' union all
select	2,'20090101' union all
select	3,'20090301' 

select sum((unitprice * quantity)) as total
from @orders o 
	join @orderdetails od
		on od.orderid = o.orderid
where orderdate between '20090201' and '20090228'

select o.orderid 
from @orders o
	left join @orderdetails od 
		on o.orderid = od.orderid
where od.orderid is null

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-03-24 : 05:34:43

u also use expect to find out the difference between tables,,,,,,,

omar3550
Starting Member

5 Posts

Posted - 2009-03-24 : 09:34:30

quote:
Originally posted by elancaster

there is obviously more than 1 way to do this, below is just 1 way. you were on the right track using unit price * quantity, but you need to sum() them to get a total for a specific period, you could also group it by month then just pick out february afterwards.

declare @orderdetails table (orderID int, unitprice int, quantity int)
insert into @orderdetails (orderid,unitprice,quantity)
select 1,3,2 union all
select 2,4,1

declare @orders table (orderid int, orderdate datetime)
insert into @orders (orderid, orderdate)
select 1,'20090201' union all
select 2,'20090101' union all
select 3,'20090301'

select sum((unitprice * quantity)) as total
from @orders o
join @orderdetails od
on od.orderid = o.orderid
where orderdate between '20090201' and '20090228'

select o.orderid
from @orders o
left join @orderdetails od
on o.orderid = od.orderid
where od.orderid is null

Em

EM would this work on MS SQL Service 05 as well??

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2009-03-24 : 10:01:06

yes it will work on 2005, the code i gave you uses table variables to just dummy up your structure and some data so you can see it working. you'll need to change the table names to the real ones of course

omar3550
Starting Member

5 Posts

Posted - 2009-03-24 : 10:34:34

quote:
Originally posted by elancaster

yes it will work on 2005, the code i gave you uses table variables to just dummy up your structure and some data so you can see it working. you'll need to change the table names to the real ones of course

Em

Thank you much! I will try it when i get to work what about the second point, where the query need to compare the records to see if they match and show the ones that do not match.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2009-03-24 : 10:44:19

my example code has both queries in it

Em

omar3550
Starting Member

5 Posts

Posted - 2009-03-24 : 16:21:44

quote:
Originally posted by elancaster

my example code has both queries in it

Em

That was pretty good EM! YOU DA MAN!

One day I'd like to be as good as you sensei! Teach me! lol

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2009-03-25 : 07:45:12

quote:
Originally posted by omar3550

quote:
Originally posted by elancaster

my example code has both queries in it

Em

That was pretty good EM! YOU DA MAN!

One day I'd like to be as good as you sensei! Teach me! lol

i'm not 'da man' ... i'm 'da girl'!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources