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
 Table duplicate query

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 93639
6 B2-06-A B0290 93639
7 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_LOT
from C_LOC
where LOC_CODE in (select LOC_CODE
from C_LOC
group by LOC_CODE
having count(LOC_CODE) >2 )
ORDER BY loc_code

I'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_LOT
from C_LOC
where LOC_CODE in (select LOC_CODE
from C_LOC
group by LOC_CODE
having count(DISTINCT INVT_ITEM) >1
OR (count(DISTINCT INVT_ITEM)=1
AND count(DISTINCT INVT_LOT) >1)
)
ORDER BY loc_code
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-08 : 03:12:30
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -