Don't select ID\Term\Class if has more than 1 record on a day & depends on the last record's rea 1

[kernal]

Table example for just 1 ID but there are thousands and thousands of records:

ID TERM CLASSNBR REASON DATE TIME
1 1106 4444 Enrl 05/01/2010 11:00:00
1 1106 4444 Drop 05/01/2010 11:50:00

1 1106 4445 Enrl 05/01/2010 12:01:00
1 1106 4445 Drop 05/01/2010 12:11:00
1 1106 4445 Enrl 05/01/2010 20:50:00
1 1106 4445 Drop 05/01/2010 21:50:00

1 1106 5555 Enrl 05/01/2010 12:01:00
1 1106 5555 Drop 05/01/2010 12:11:00
1 1106 5555 Enrl 05/01/2010 20:50:00

1 1114 6666 Enrl 01/10/2011 17:00:00

Results wanted:

1 1106 5555 Enrl 05/01/2010 20:50:00
1 1114 6666 Enrl 01/10/2011 17:00:00

Don't want the other records because the last record for the day was Drop for ID\Term\Class.

Help is very appreciated!

 

[feherke]

Hi

[b]select[/b]
[teal]*[/teal]

[b]from[/b] kernal

[b]where[/b] [teal]([/teal]classnbr[teal],[/teal] date[teal],[/teal] time[teal])[/teal] [b]in[/b] [teal]([/teal]
    [b]select[/b]
    classnbr[teal],[/teal] date[teal],[/teal] max[teal]([/teal]time[teal])[/teal]

    [b]from[/b] kernal

    [b]group by[/b] classnbr[teal],[/teal] date
[teal])[/teal]
[b]and[/b] reason [teal]!=[/teal] [i][green]'Drop'[/green][/i]

Feherke.
feherke.github.io

 

[kernal]

Hey Feherke,

Thank you so much!!!