Re: Duplicate Tests - Help Please

[Plamen Ratchev]

Assuming the Date/Time finish column has time values, here are two queries:

SELECT AssociateID,
DATEADD(DAY, DATEDIFF(DAY, 0, [Date/Time Finish]), 0) AS
date_time,
Associate,
COUNT(*) AS test_cnt
FROM Assessments
GROUP BY AssociateID,
DATEADD(DAY, DATEDIFF(DAY, 0, [Date/Time Finish]), 0),
Associate
HAVING COUNT(*) 1;

SELECT AssociateID,
[Date/Time Finish],
Associate,
[Assessment Name],
[Percentage Score]
FROM Assessments AS A
WHERE EXISTS
(SELECT *
FROM Assessments AS B
WHERE A.AssociateID = B.AssociateID
AND A.[Date/Time Finish] >= DATEADD(DAY, DATEDIFF(DAY, 0,
B.[Date/Time Finish]), 0)
AND A.[Date/Time Finish] < DATEADD(DAY, DATEDIFF(DAY, 0,
B.[Date/Time Finish]) + 1, 0)
AND A.[Date/Time Finish] <B.[Date/Time Finish]);

HTH,

Plamen Ratchev

http://www.SQLStudio.com