Second date for each group item

[jilanis]

Hi guys.
I would really appreciate your help on this please.
I am using access 2010 and need sql.
One table: tblAttendance
Attid = unique key
casesNumber: text field
AttendedDate: Date

Each case attends multiple dates.
Requirement: I need second attended date for each caseNumber.
Note: some case have only one attended dates that need to be excluded from this query.

Only second attended dates for all casesNumber. Is it possible?

Much appreciated. Regards

 

[isladogs]

There are various ways you could do this. For example
Create an aggregate query grouping by case number and restrict to SELECT TOP 1
Then create an unmatched query to identify all records not in TOP 1 query.
Once again restrict that using SELECT TOP 1 to get the second date for each case number (where it exists)

Also welcome to AWF

 

[arnelgp]

SELECT [I]yourTableNameHere[/I].AttID, 
       [I]yourTableNameHere[/I].CaseNumber, 
       [I]yourTableNameHere[/I].AttendedDate 
FROM [I][yourTableNameHere][/I] 
WHERE (SELECT COUNT("1") FROM [[I]yourTableNameHere[/I]] AS T 
             WHERE T.CaseNumber=[I]yourTableNameHere[/I].CaseNumber And 
             T.[AttendedDate] <= [I]yourTableNameHere[/I].AttendedDate) = 2

 

[isladogs]

@jilanis
Arnel's approach does this in one query (using a subquery) whereas mine uses two.
I'd be interested in knowing which is faster to complete if you have time to test both

 

[jilanis]

There are various ways you could do this. For example
Create an aggregate query grouping by case number and restrict to SELECT TOP 1
Then create an unmatched query to identify all records not in TOP 1 query.
Once again restrict that using SELECT TOP 1 to get the second date for each case number (where it exists)

OMG it works. Thank you thank you!
1) As per your advice I created a normal query with Min(AttendedDate) and group by on [CaseNumber] (i had to use min(Attid) though just to avoid group by on Attid field). This gives me all the min/first attendance dates for each CaseNumber.
2) I then created an "Unmatched query" between tblAttendance and my new query, and matched tblAttendance.Attid against [myNewQuery].Attid. This gives me all the attendance dates except the first/min dates that we have excluded via this unmatched query.
3) All I need now is select/extract the first/min date from "unmatchedQuery". So I entered Min under Totals for [unmatchedQuery].[AttendedDate]. (again i had to use Min under Attid field to avoid group by, which is fine).

And there it is... in less than a second I have got my second attended date for each CaseNumber.

Thank you so much my friend.

 

[jilanis]

SELECT [I]yourTableNameHere[/I].AttID, 
       [I]yourTableNameHere[/I].CaseNumber, 
       [I]yourTableNameHere[/I].AttendedDate 
FROM [I][yourTableNameHere][/I] 
WHERE (SELECT COUNT("1") FROM [[I]yourTableNameHere[/I]] AS T 
             WHERE T.CaseNumber=[I]yourTableNameHere[/I].CaseNumber And 
             T.[AttendedDate] <= [I]yourTableNameHere[/I].AttendedDate) = 2

Thank you, your solution works too. But it was very slow and crashed my Access a couple of times but looking at the top records on screen it seemed to be extracting accurate [second attended dates]. It probably because I have got 17,000 records in the tblAttendance.
Your solution would work perfectly for smaller tables.
Thank you!

 

[isladogs]

Many thanks for providing feedback
I knew both methods would work but subqueries can be slow so I tend to avoid them where there is another valid approach.
Intrigued by the changes you made to each query in my suggestion - can you post the SQL for each.

 

[jilanis]

Many thanks for providing feedback
I knew both methods would work but subqueries can be slow so I tend to avoid them where there is another valid approach.
Intrigued by the changes you made to each query in my suggestion - can you post the SQL for each.

Yes of course:
1) First query to extract Min attendance date for each caseNumber (group):

SELECT Min(tblAttendance.AttendedDate) AS MinOfAttendedDate, tblAttendance.CaseNumber, Min(tblAttendance.AttID) AS MinOfAttID
FROM tblAttendance
GROUP BY tblAttendance.CaseNumber
ORDER BY Min(tblAttendance.AttendedDate);

Query name: MinAttendanceDate

2) "Unmatched query" between tblAttendance and my new query (1):

SELECT Min(tblAttendance.AttID) AS MinOfAttID1, Min(tblAttendance.AttendedDate) AS MinOfAttendedDate1, tblAttendance.CaseNumber
FROM tblAttendance LEFT JOIN MinAttendanceDate ON tblAttendance.[AttID] = MinAttendanceDate.[MinOfAttID]
GROUP BY tblAttendance.CaseNumber, MinAttendanceDate.MinOfAttID
HAVING (((MinAttendanceDate.MinOfAttID) Is Null));
---------

Thanks again.