Complex query; not sure how to go about it.

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

Complex query; not sure how to go about it.

Author

Topic

jvegastn
Starting Member

23 Posts

Posted - 2009-06-24 : 09:35:30

I have an Inventory table that I'm trying to run a query against.

2114 679145413282 D 11 EW * 0
2114 679145413299 D 11.5 EW * 0
2114 679145413305 D 12 EW * 0
2114 679145413312 D 13 EW * 0
2462 679145413329 D 7 D * 0
2462 679145413336 D 7.5 D * 0
2462 679145413343 D 8 D * 0
2462 679145413350 D 8.5 D * 0
2462 679145413367 D 9 D * 0
2462 679145413374 D 9.5 D * 1

Columns in order ItemNumber, UPC Number, Type, Size, Width, Price, Quantity. I need to write a query that will return all "F" Types and only those "D" that have available quantities. Using the above example ItemNumber 2114 would not be returned whereas 2462 would be because it has 1 remaining 9.5. So I need to sum the available quantities for all of the same ItemNumbers, if this is > 0, it needs to be returned in the query. Thanks in advance.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-06-24 : 09:42:02

quote:
Originally posted by jvegastn

I have an Inventory table that I'm trying to run a query against.

2114 679145413282 D 11 EW * 0
2114 679145413299 D 11.5 EW * 0
2114 679145413305 D 12 EW * 0
2114 679145413312 D 13 EW * 0
2462 679145413329 D 7 D * 0
2462 679145413336 D 7.5 D * 0
2462 679145413343 D 8 D * 0
2462 679145413350 D 8.5 D * 0
2462 679145413367 D 9 D * 0
2462 679145413374 D 9.5 D * 1

Columns in order ItemNumber, UPC Number, Type, Size, Width, Price, Quantity. I need to write a query that will return all "F" Types and only those "D" that have available quantities. Using the above example ItemNumber 2114 would not be returned whereas 2462 would be because it has 1 remaining 9.5. So I need to sum the available quantities for all of the same ItemNumbers, if this is > 0, it needs to be returned in the query. Thanks in advance.

Try this

select ItemNumber,SUM(Quantity) from Inventory
where Type = 'F' or (Type = 'D' and Quantity > 0)
group by ItemNumber

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-24 : 09:43:36

There are no "f" types in your sample data...
See http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx how to make yourself understood.

E 12°55'05.63"
N 56°04'39.26"

jvegastn
Starting Member

23 Posts

Posted - 2009-06-24 : 09:51:40

Thanks, I'll give it a shot!

As far as the sample data not containing any "F" in the Type which all needed to be included, I wanted to focus on the tricky part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-24 : 09:59:19

And thus, you leave us only small pieces of business rules.

E 12°55'05.63"
N 56°04'39.26"

jvegastn
Starting Member

23 Posts

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

@ vijayisonly

It works great ... to an extent. It definitely does what we are looking for except I need the query view the each ItemNumber and Size/Width run. I am assuming this can be accomplished by removing the "GROUP BY tblInventory.ItemNumber" ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-24 : 10:07:05

Using SQL Server 2005 and later:

select ItemNumber,SUM(Quantity) over (partition by itemnumber) from Inventory
where Type = 'F' or (Type = 'D' and Quantity > 0)

E 12°55'05.63"
N 56°04'39.26"

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-06-24 : 10:07:05

quote:
Originally posted by jvegastn

@ vijayisonly

It works great ... to an extent. It definitely does what we are looking for except I need the query view the each ItemNumber and Size/Width run. I am assuming this can be accomplished by removing the "GROUP BY tblInventory.ItemNumber" ?

If you remove the GROUP BY, you cannot use the SUM function.
Based on your sample data above, what is your expected output for ItemNumber 2462?

jvegastn
Starting Member

23 Posts

Posted - 2009-06-24 : 10:12:16

2462 679145413329 D 7 D * 0
2462 679145413336 D 7.5 D * 0
2462 679145413343 D 8 D * 0
2462 679145413350 D 8.5 D * 0
2462 679145413367 D 9 D * 0
2462 679145413374 D 9.5 D * 1

I need to return only those ItemNumbers whose combined Quantity are greater than 0, but I need to maintain the integrity. The previous SQL statement returns ItemNumber, Expr1 or

2462 1

But based on that return I couldn't begin to give you a size or width.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-06-24 : 10:23:23

quote:
Originally posted by jvegastn

2462 679145413329 D 7 D * 0
2462 679145413336 D 7.5 D * 0
2462 679145413343 D 8 D * 0
2462 679145413350 D 8.5 D * 0
2462 679145413367 D 9 D * 0
2462 679145413374 D 9.5 D * 1

I need to return only those ItemNumbers whose combined Quantity are greater than 0, but I need to maintain the integrity. The previous SQL statement returns ItemNumber, Expr1 or

2462 1

But based on that return I couldn't begin to give you a size or width.

I'm sorry..I dont think I got your requirement...but maybe you need this?

select * from Inventory
where ItemNumber in
(select ItemNumber from Inventory
where Type = 'F' or (Type = 'D' and Quantity > 0))

Or Peso might have a better solution.

jvegastn
Starting Member

23 Posts

Posted - 2009-06-24 : 10:29:48

Works great!

Thanks again!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-06-24 : 10:39:33

ohh..that was a surprise..welcome