Select Maximum Dates only

Transact-SQL
1

Hey All,

So we have multiple people, completing multiple tests over many years.

I am trying to find people who's latest test was within a specified range, however there may be more than one name; so we need to look at those who's name, company & birth date match.

i.e.
Name Company DOB Test Date
Callum a abc 02/18
Callum a def 02/18
Terry a ghi 02/18
Mike b jkl 01/18
Sean b mno 01/18
Paul b pqr 01/18
Callum c stu 12/17
Lee c vwx 12/17
Galvin c yza 12/17
Mike b jkl 01/17
Sean b mno 01/17
Paul b pqr 01/17
Callum a abc 02/17
Lee c vwx 12/16
Galvin c yza 12/16

So If I wanted to see all those who had their most recent tests in 2018, I'd get:
Callum a abc 02/18
Callum a def 02/18
Terry a ghi 02/18
Mike b jkl 01/18
Sean b mno 01/18
Paul b pqr 01/18

So I have -
SELECT
Candidates.Fullname
,Clients.CompanyName
,JobCandidates.DateOfBirth
,Certificates.Date

FROM
etc etc.

WHERE ?

I just don't know where to go. Any help appreciated.

Thanks,

Callum

2

WHERE Certificates.Date >= '20180101'

3

Hey,

This would certainly give me the desired answer in this instance, however; if I wanted to view who's last test was in 2017, it would show me everyone who completed a test in 2017, including those who also took another test in 2018, but I only want the ones who completed a test in 2017 & then haven't completed a test since.

In essence, I want to be able to see who needs to be re-tested, so I can see e.g. who had a test over a year ago and hasn't had one since

Thanks,

4

Easy enough to do if you know the column names and know the query rules -- is the date by Cert type or just for any/all Certs -- but you haven't given any of there here.

I'll guess just to give you the general structure:

SELECT
...
FROM ...
WHERE Certificates.Date >= '20170101' AND Certificates.Date <= '20180101'
AND NOT EXISTS(
SELECT 1 FROM Certificates C2 WHERE C2.<join_col> = Certificates.<join_col> WHERE Date >= '20180101'
...)

5

You could also look at using ROW_NUMBER() to identify the latest date/time that each user was tested.

ROW_NUMBER() OVER(PARTITION BY Name, Company, DOB Order By TestDate DESC)

This would (or should - depends on the actual grouping/partition you have) identify the latest test date for each person. If you put that in a derived table or common-table expression - you could then select from that to get all members where the latest test date is in 2017.

WITH memberPartition
AS (
SELECT ...
     , ROW_NUMBER() OVER(PARTITION BY Name, Company, DOB Order By TestDate DESC) AS rowNumber
       )
SELECT ...
FROM memberPartition
WHERE rowNumber = 1
AND TestDate >= '20170101'
AND TestDate < '20180101'
6

OK, maybe it would help if I give you my full query, it has a little more going on than my example. ScottPletcher & jeffw8713 - both your methods make sense in theory but I'm honest I don't have a lot of SQl knowledge and am struggling to make either work.

SELECT
Clients.CompanyName
,Clients.CanBookJobsOnline
,Clients.IsKeyAccount
,Clients.Active
,ClientContacts.Fullname AS [ClientContacts Fullname]
,ClientContacts.Email
,ClientContacts.Telephone
,ClientContacts.Mobile
,Candidates.DateOfBirth AS [Candidates DateOfBirth]
,Candidates.Fullname AS [Candidates Fullname]
,ServiceTypes.Description AS [ServiceTypes Description]
,ServiceTypes.CertificateExpiryCalculationID
,Certificates.ExpiryDate
,Tests.PriceToClient
,Appointments.StartDateTime
,TestIdentifiers.Description AS [TestIdentifiers Description]
,TestStatuses.Description AS [TestStatuses Description]
,JobStatuses.Description AS [JobStatuses Description]
,JobServices.JobID
,Jobs.DateCreated
,JobDocuments.PublishedDate
,JobCandidates.DateOfBirth AS [JobCandidates DateOfBirth]
,FinalResults.Description AS [FinalResults Description]
FROM
Tests
INNER JOIN Appointments
ON Tests.AppointmentID = Appointments.ID
INNER JOIN ServiceTypes
ON Tests.ServiceTypeID = ServiceTypes.ID
INNER JOIN JobServices
ON ServiceTypes.ID = JobServices.ServiceTypeID
INNER JOIN Jobs
ON JobServices.JobID = Jobs.ID
INNER JOIN JobStatuses
ON Jobs.JobStatusID = JobStatuses.ID
INNER JOIN JobDocuments
ON Jobs.ID = JobDocuments.JobID
INNER JOIN Clients
ON Jobs.ClientID = Clients.ID
INNER JOIN ClientContacts
ON Clients.ID = ClientContacts.ClientID AND Jobs.ClientContactID = ClientContacts.ID
INNER JOIN TestIdentifiers
ON Tests.TestIdentifierID = TestIdentifiers.ID
INNER JOIN TestStatuses
ON Tests.TestStatusID = TestStatuses.ID
INNER JOIN Certificates
ON Tests.ID = Certificates.TestID AND Tests.CurrentCertificateID = Certificates.ID
INNER JOIN FinalResults
ON Certificates.FinalResultID = FinalResults.ID
INNER JOIN JobCandidates
ON Tests.JobCandidateID = JobCandidates.ID AND Jobs.ID = JobCandidates.JobID
INNER JOIN Candidates
ON JobCandidates.CandidateID = Candidates.ID

WHERE
ServiceTypes.CertificateExpiryCalculationID IS NOT NULL
AND Certificates.ExpiryDate BETWEEN dateadd(DAY, datediff(day, 0, @StartDate),0) AND dateadd(DAY,1, dateadd(DAY, datediff(day, 0, @EndDate),0) )

Thank you