SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Self join results in duplicate data counts of "paired" data


Self join results in duplicate data counts of "paired" data

Author
Message
Marcia Q
Marcia Q
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1387 Visits: 700
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?
john.arnott
john.arnott
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5628 Visits: 3059
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


Marcia Q
Marcia Q
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1387 Visits: 700
Yes, I guess I got carried away. K.I.S.S always works best.
Thanks for the help, John!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search