Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Self join results in duplicate data counts of "paired" data Expand / Collapse
Author
Message
Posted Wednesday, May 20, 2009 4:27 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 12:15 PM
Points: 365, Visits: 678
I hope someone can help me with this one...
We keep a list of people who have had a credit check request. Management wants to see some numbers on this. They want to know the following: Of the people who have more than one credit check in a day, how many credit checks do they have in one day?

I have done a self join on the table, but get duplicate results.

Here is my table:
CREATE Table CreditCheck
(ApplicantID smallint,
CreditRequestID smallint,
RequestDate smalldatetime
)

INSERT INTO CreditCheck
(ApplicantID, CreditRequestId, RequestDate)
SELECT 1,1,'01/01/2009'
UNION ALL SELECT 1,2,'01/15/2009'
UNION ALL SELECT 1,4,'01/15/2009'
UNION ALL SELECT 1,3,'02/02/2009'
UNION ALL SELECT 2,10,'01/01/2009'
UNION ALL SELECT 2,9,'01/02/2009'
UNION ALL SELECT 2,8,'04/04/2009'
UNION ALL SELECT 2,5,'04/04/2009'
UNION ALL SELECT 3,9,'01/02/2009'
UNION ALL SELECT 3,10,'01/02/2009'
UNION ALL SELECT 3,4,'01/01/2009'
UNION ALL SELECT 3,7,'01/01/2009'
UNION ALL SELECT 3,1,'01/01/2009'
UNION ALL SELECT 3,6,'01/01/2009'

--I am only going to query for the month of January...

--Here is how the results break down:
SELECT * FROM creditcheck
WHERE RequestDate >= '01/01/2009' AND RequestDate < '02/01/2009'

ApplicantID CreditRequestID RequestDate
1 1 2009-01-01 00:00:00 don't count (one check on this day for Appl1)
1 2 2009-01-15 00:00:00 yes, count this
1 4 2009-01-15 00:00:00 yes, count this
----------------------------------------------
2 10 2009-01-01 00:00:00 don't count (one check on this day for Appl2)
2 9 2009-01-02 00:00:00 don't count (one check on this day for Appl2----------------------------------------------
3 9 2009-01-02 00:00:00 yes, count this
3 10 2009-01-02 00:00:00 yes
----------------------------------------------
3 4 2009-01-01 00:00:00 yes
3 7 2009-01-01 00:00:00 yes
3 1 2009-01-01 00:00:00 yes
3 6 2009-01-01 00:00:00 yes


--Here is what I have written (incorrectly!)
SELECT A.ApplicantID, COUNT(A.ApplicantID) As CreditChecks
FROM CreditCheck A
INNER JOIN CreditCheck B
ON A.ApplicantID = B.ApplicantID
AND A.RequestDate = B.RequestDate
AND A.CreditRequestID < B.CreditRequestID
Where A.RequestDate >= '01/01/2009'
AND A.RequestDate < '02/01/2009'
AND B.RequestDate >= '01/01/2009'
AND B.RequestDate < '02/01/2009'
GROUP BY A.ApplicantID, A.RequestDate

--My query gives me these results:
ApplicantID CreditChecks
3 6
3 1
1 1

--But I need the following results instead:
ApplicantID CreditChecks
3 4
3 2
1 2

Can anyone help with this?
Post #721012
Posted Wednesday, May 20, 2009 6:00 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 8:55 AM
Points: 1,520, Visits: 3,035
I think you've made it more complicated than necessary. The HAVING option on GROUP BY should do the trick for you without the self-join.
select ApplicantID,
count(*) as CheckCnt,
RequestDate
from CreditCheck
group by ApplicantID, RequestDate
having count(*) > 1

This returns:
ApplicantID CheckCnt    RequestDate
----------- ----------- -----------------------
3 4 2009-01-01 00:00:00
3 2 2009-01-02 00:00:00
1 2 2009-01-15 00:00:00
2 2 2009-04-04 00:00:00

(4 row(s) affected)

Edit: If needed, put the date-range desired in a WHERE clause.
Select * from Creditcheck
select ApplicantID,
count(*) as CheckCnt,
RequestDate
from #CreditCheck
Where RequestDate < '20090301'
group by ApplicantID, RequestDate
having count(*) > 1

Post #721042
Posted Tuesday, June 02, 2009 3:53 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 12:15 PM
Points: 365, Visits: 678
Yes, I guess I got carried away. K.I.S.S always works best.
Thanks for the help, John!
Post #727769
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse