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.
Author |
Topic |
ITTrucker
Yak Posting Veteran
64 Posts |
Posted - 2013-06-07 : 10:56:41
|
For our warehouse, I'm trying to find all the locations that have more than one item in the location, and any locations that have the same item, but more than one LOT number.Sample data:Record Location Item LOT 1 B2-06-D B0340 103050 2 B2-06-D B0076 2125 3 B2-06-G B0076 5227 4 B2-06-G B0076 2125 5 B2-06-A B0290 936396 B2-06-A B0290 936397 B2-07-C B0211 102779 8 B2-07-C B0211 102779 9 B2-07-C B0211 102779 From this sample, I'm looking to pull out records 1 and 2 because the location is the same but the item number is different, and records 3 and 4 because the items are the same item, but different LOT. Records 5 and 6 are same location, same item and same lot so I don't need them same for 7,8, and 9.This query is returning all the records that I want (I think) but it's also returning the ones that have the same item same lot.select LOC_CODE, INVT_ITEM, INVT_LOTfrom C_LOCwhere LOC_CODE in (select LOC_CODE from C_LOC group by LOC_CODE having count(LOC_CODE) >2 ) ORDER BY loc_codeI've been trying to play with the group and adding having clauses but I can't seem to catch all the ones I want without the ones I don't need. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-07 : 11:12:48
|
[code]select LOC_CODE, INVT_ITEM, INVT_LOTfrom C_LOCwhere LOC_CODE in (select LOC_CODE from C_LOC group by LOC_CODE having count(DISTINCT INVT_ITEM) >1OR (count(DISTINCT INVT_ITEM)=1AND count(DISTINCT INVT_LOT) >1))ORDER BY loc_code[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
ITTrucker
Yak Posting Veteran
64 Posts |
Posted - 2013-06-07 : 13:10:31
|
That looks like it's working, Thanks.I tried a distinct clause in the counts, but I had left my > at 2 instead of dropping it to 1 (And I didn't have the OR in there either) and it was returning 0 records.Thanks again. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-08 : 03:12:30
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|