SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Table duplicate query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ITTrucker
Yak Posting Veteran

USA
50 Posts

Posted - 06/07/2013 :  10:56:41  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 06/07/2013 :  11:12:48  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 06/07/2013 11:13:41
Go to Top of Page

ITTrucker
Yak Posting Veteran

USA
50 Posts

Posted - 06/07/2013 :  13:10:31  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 06/08/2013 :  03:12:30  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000