identifing missing data - SQL Server Forums

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

identifing missing data

Author

Topic

Henshin1
Starting Member

2 Posts

Posted - 2010-10-28 : 21:54:12

Hi all

I have a table with many rows. Attributes consist of a customer id, version number, and an activity code.

for every customer, there can be several version numbers and for every version number there should be 3 rows where the activity code is x, y and z.

For example

Cust_ID Ver_No Act_Cd
1111 1 x
1111 1 y
1111 1 z
1111 2 x
1111 2 y
1111 2 z
2222 1 x
2222 1 y
2222 1 z
2222 2 x
2222 2 y
2222 2 z

I want to find out, which customer id's have version numbers that do not have all 3 Activity codes. For example

3333 1 x
3333 2 x
3333 2 y
3333 2 z

This would be a customer number I need to identify as version number 1 only has code x, but not y and z.

Any help would be appreciated.
Thanks alot!

Devart
Posting Yak Master

102 Posts

Posted - 2010-10-29 : 02:23:45

Hello,

For example:

select
t1.cust_id,
t1.ver_no,
t2.Act_Cd
from
(
select
cust_id,
ver_no
from (select distinct * from <your_table_name>) s
group by
cust_id,
ver_no
having
count(*)<3
) t1
inner join <your_table_name> t2
on t1.cust_id=t2.cust_id and t1.ver_no=t2.ver_no

Best regards,

Devart,
SQL Server Tools:
dbForge Schema Compare
dbForge Data Compare
dbForge Query Builder

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-10-29 : 02:29:15

[code]
SELECT C.Cust_ID, C.Ver_No, C.Act_CD
FROM Cust C
INNER JOIN
(
SELECT Cust_ID, Ver_No, COUNT(distinct Act_CD) cnt FROM Cust
GROUP BY Cust_ID, Ver_No HAVING COUNT(distinct Act_CD) < 3
) A
ON C.Cust_ID = A.Cust_ID AND C.Ver_No = A.Ver_No

[/code]

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-10-29 : 02:31:06

Devart and mine is almost same solution
bit difference of sub query.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Henshin1
Starting Member

2 Posts

Posted - 2010-10-29 : 02:32:44

Thanks alot for the help Devart and Vaibhav T!
Problem solved. Much appreciated

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-10-29 : 02:49:45

@Henshin :