May 20, 2009 at 4:27 pm
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:00yes, count this
1 4 2009-01-15 00:00:00yes, 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:00yes, count this
3 10 2009-01-02 00:00:00yes
----------------------------------------------
3 4 2009-01-01 00:00:00yes
3 7 2009-01-01 00:00:00yes
3 1 2009-01-01 00:00:00yes
3 6 2009-01-01 00:00:00yes
--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:
ApplicantIDCreditChecks
3 6
3 1
1 1
--But I need the following results instead:
ApplicantIDCreditChecks
3 4
3 2
1 2
Can anyone help with this?
May 20, 2009 at 6:00 pm
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 1
June 2, 2009 at 3:53 pm
Yes, I guess I got carried away. K.I.S.S always works best.
Thanks for the help, John!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply