Getting Year Data - Easy Question, I think

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

SQL Server 2000 Forums

SQL Server Development (2000)

Getting Year Data - Easy Question, I think

Author

Topic

NewCents
Starting Member

19 Posts

Posted - 2006-01-16 : 02:14:08

Hmmm.can't figure this out

I'd like to get a price total for all cases in the database where year=2005 and products_ID=33

cases Table:
cases_ID, year
1, 2005
2, 2006
3, 2005

casedetail Table:
casedetail_ID, cases_ID, price, qty, product_code
7, 1, 20, 2, 1011
8, 1, 30, 1, 1024
9, 2, 15, 5, 1000

product_items Table:
product_items_ID, cat_num, products_ID
1, 1011, 33
2, 10122, 17
3, 3484, 22
4, 1024, 33

The problem I'm having, the year is stored in the cases table.

I'm using the query below to work off of. This query gets the total price of all casedetail entries which use that products_ID. It only works in one case.

Select sum(qty*price) as price
from (Select casedetail_ID, cases_ID, qty, price, (select top 1 products_id
from product_items pi where pi.cat_num=cd.product_code) as products_id from casedetail cd) T where cases_ID=1 and products_ID in ('33') group by cases_ID

So with the dummy data above, this query would produce: $70.00

I can't figure out how to join the cases table correctly, so I can add a where, like: 'where cases.year=2005'.

Any help appreciated!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-16 : 02:33:02

use INNER JOIN to join your tables

select 	sum(d.price * d.qty) as total_price
from	casedetail d inner join cases c
	on	d.cases_ID	= c.cases_ID
	inner join product_items p
	on	d.product_code	= p.cat_num
where	c.year	= 2005
and	p.products_ID	= 33

OR to group by Products_ID, Year

select 	p.products_ID, c.year, sum(d.price * d.qty) as total_price
from	casedetail d inner join cases c
	on	d.cases_ID	= c.cases_ID
	inner join product_items p
	on	d.product_code	= p.cat_num
group by p.products_ID, c.year

-----------------
'KH'

NewCents
Starting Member

19 Posts

Posted - 2006-01-16 : 12:08:12

Hmm...I still need to incorporate this:

select top 1 products_id
from product_items pi where pi.cat_num=cd.product_code

Some how because there are multiple cat_num's that equal a specific products_ID. So I just need to get the first one. If I don't grab the first one than it will try and equate for every cat_num in the system.

NewCents
Starting Member

19 Posts

Posted - 2006-01-16 : 17:22:11

Okay, think I figured it out:

Select sum(qty*price) as price
from (Select casedetail_ID, cd.cases_ID, qty, price, c.DOSyear, (select top 1 products_id
from product_items pi where pi.cat_num=cd.product_code) as products_id
from casedetail cd inner join cases c on cd.cases_ID = c.cases_ID
where DOSyear=2005) T where products_ID in ('33')

This totals all cases in 2005 that had a casedetail with products_ID 33.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources