Selecting dupllicate values

[ccshadow]

After several hours of trying to figure this out, I confess that I need help.

I have an SQL statement that performs a few joins based upon whether or not a value does not have duplicates in a combination of columns from the main table. For example, a customer account number can have several entries for any or all of three lines of business - vid, cdv, hsi. An account could be tracked once for vid and twice for cdv, but the statement would only grab the vid entry. Now that I have that ironed out, I find that I also need to test for the opposite condition - retrieving records for account numbers that have been tracked more than once for vid, cdv or hsi.

	SELECT mr.tDate, mr.agentId agentA, mr.account, mr.lob, mr.subreq, mr.reason, mr.inPkg, mr.outPkg, dip.inPkgStatus, dop.outPkgStatus, dr.reasonStatus, ISNULL(mr.change, 0) AS change, ISNULL(mr.agentChanged, 0) AS agentB, ISNULL(au.dwn, 0) AS auditInPkg
	FROM tbl_masterRetention mr
	 JOIN (SELECT  account, lob FROM tbl_masterRetention GROUP BY  account, lob HAVING COUNT(*) > 1 ) AS X
	ON X.account = mr.account AND X.lob = mr.lob
	JOIN tbl_dropInPkg dip ON mr.inPkg = dip.inPkgCode
	JOIN tbl_dropOutPkg dop ON mr.outPkg = dop.outPkgCode 
	JOIN tbl_dropReasons dr ON mr.reason = dr.reasonCode 
	LEFT OUTER JOIN tbl_audit au ON au.inPkg = mr.inPkg AND au.outPkg = mr.outPkg
	WHERE mr.subreq = 'XXX' AND mr.tDate BETWEEN (SELECT DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0)) AND GetDate() AND mr.agentId = '231' 
	ORDER BY mr.account, mr.lob, mr.tDate

While this code does give me a list of all account numbers with the line of business that were tracked more than once, the results are grouped. I've tried altering the manner in which I'm testing for multiples and I've tried various Joins, etc. Not having any luck. Can anyone help out?

 

[Catadmin]

While this code does give me a list of all account numbers with the line of business that were tracked more than once, the results are grouped. I've tried altering the manner in which I'm testing for multiples and I've tried various Joins, etc.

You don't say what you expect your results to look like. Can you provide sample data, how it is currently looking and how you would like it to look?

Thanks,



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ccshadow]

I haven't posted database values before so hopefully this is what you were referring to. I've removed some of the columns just to simplify things.

The data below is sample data that shows all entries for a specific account number. Within these results I'm interested in the entries where an lob was tracked more than once and the subreq = 'XXX'. So from this data I would only want to grab the records for 413, 423, 414, and 425 (id's from the left hand column) for lob's VID and HSI as those lob's have been tracked more than once and the subreq = 'XXX'.

413	231	2006-11-01 10:58:46.407	8498310230409638	VID	XXX       	OC        	NULL	PLT       	PLT       	CS        	
414	231	2006-11-01 11:03:56.240	8498310230409638	HSI	XXX       	OA        	NULL	CHN       	CHSI      	CA        	
415	231	2006-11-01 11:05:14.193	8498310230409638	CDV	TRN       	FPD       	NULL	CDV       	CDV       	FR        	
420	231	2006-11-01 11:32:18.260	8498310230409638	CDV	XXX       	OA        	NULL	CDV       	CDV       	CS        	
423	231	2006-11-01 11:39:09.530	8498310230409638	VID	XXX	OA        	NULL	PLT       	PLT       	CS        	NULL	NULL	
425	231	2006-11-01 11:40:53.887	8498310230409638	HSI	XXX       	OA        	NULL	CHSI      	CHN       	CA        	
426	231	2006-11-01 11:43:38.320	8498310230409638	CDV	TRN       	FPD       	NULL	CDV       	CDV       	CS

And here is the select that attempts retrieve those entries.

	SELECT mr.tDate, mr.agentId agentA, mr.account, mr.lob, mr.subreq, mr.reason, mr.inPkg, mr.outPkg, dip.inPkgStatus, dop.outPkgStatus, dr.reasonStatus, ISNULL(mr.change, 0) AS change, ISNULL(mr.agentChanged, 0) AS agentB, ISNULL(au.dwn, 0) AS auditInPkg
	FROM tbl_masterRetention mr
	 JOIN (SELECT  account, lob FROM tbl_masterRetention GROUP BY  account, lob HAVING COUNT(*) > 1 ) AS X
	ON X.account = mr.account AND X.lob = mr.lob
	JOIN tbl_dropInPkg dip ON mr.inPkg = dip.inPkgCode
	JOIN tbl_dropOutPkg dop ON mr.outPkg = dop.outPkgCode 
	JOIN tbl_dropReasons dr ON mr.reason = dr.reasonCode 
	LEFT OUTER JOIN tbl_audit au ON au.inPkg = mr.inPkg AND au.outPkg = mr.outPkg
	WHERE   mr.account = '8498310230409638' AND mr.subreq = 'XXX' AND mr.tDate BETWEEN (SELECT DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0)) AND GetDate() AND mr.agentId = '231' 
	ORDER BY mr.account, mr.lob, mr.tDate

And here is the results set.

2006-11-01 11:32:18.260	231	8498310230409638	CDV	XXX       	OA        	CDV       	CDV       	PHONE	RET	CONTROL	0	0	0
2006-11-01 11:03:56.240	231	8498310230409638	HSI	XXX       	OA        	CHN       	CHSI      	HSD	RET	CONTROL	0	0	0
2006-11-01 11:40:53.887	231	8498310230409638	HSI	XXX       	OA        	CHSI      	CHN       	HSD	RET	CONTROL	0	0	0
2006-11-01 10:58:46.407	231	8498310230409638	VID	XXX       	OC        	PLT       	PLT       	DIGITAL	SALERET	UNCONTROL	0	0	DWNSAV
2006-11-01 11:39:09.530	231	8498310230409638	VID	XXX	OA        	PLT       	PLT       	DIGITAL	SALERET	CONTROL	0	0	DWNSAV

This does correctly identify the account numbers that have more than one entry, but it doesn't cascade down to the lob or subreq since it's also grabbing lob = CDV from the first sample set data. I also need each individual entry in the results set, not grouped together.

If the > 1 in the subquery is changed to = 1 then it does return each account number that has only been tracked once for any lob - obviously just by virtue of the fact that grouping by 1 results in the individual entry. All I really got by changing the = 1 to > 1 is a list of account numbers that has more than one entry.

Does what I'm after make more sense?

 

[ccshadow]

This does correctly identify the account numbers that have more than one entry, but it doesn't cascade down to the lob or subreq since it's also grabbing lob = CDV from the first sample set data. I also need each individual entry in the results set, not grouped together.

I misstated this. It grabs the CDV when it shouldn't because the filter for subreq occurs last.

 

[ccshadow]

Taking a break from a problem always seem to help. I figured it out. Thanks for asking for more info. Gave me a reason to take a fresh look.

 

[Catadmin]

Taking a break has always been my second best solution to all SQL related problems. @=) And explaining it to someone else has also saved my butt a few times too.

Glad you were able to figure it out.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"