Select query for latest records by date

Question

Select query for latest records by date

gjoelson 29755

SSC Eights!

Points: 848
More actions
October 18, 2023 at 3:57 pm

#4310502
Hello ,
I'm trying to retrieve all the records for the most recent date. I have used Max on the [entrydate] and this column is a datetime data type, however this only returns 1 record even though there are 12 records for the day. is the datetime the issue - How do I retrieve all 12 rcords ?
```
SELECT *
FROM [Prod_ShiftSummary]
WHERE  entrydate= (
   SELECT MAX(EntryDate)
     FROM [Prod_ShiftSummary]
 where office = 'baytown'
) ;
```
thank you in advance.
- This topic was modified 1 year, 1 month ago by gjoelson 29755.

December 10, 2023 at 1:46 am

This was removed by the editor as SPAM
December 10, 2023 at 1:50 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76985 More actions · Answer 1

Presumably, the 12 records weren't all entered at exactly the same time on that date, but that is what you are checking for.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 2

SELECT *
FROM [Prod_ShiftSummary]
WHERE  entrydate >= (
   SELECT CONVERT(date, MAX(EntryDate))
     FROM [Prod_ShiftSummary]
 where office = 'baytown'
) ;

gjoelson 29755 SSC Eights! Points: 848 More actions · Answer 3

Jonathan, OMG you are kidding me..... I tried a bunch of things but not a >

Thank you so much !

drew.allen SSC Guru Points: 76985 More actions · Answer 4

Now that Jonathan has given you a solution, this likely to perform better.

WITH ShiftSummary AS
(
    SELECT *, LAST_VALUE(ss.EntryDate) OVER(ORDER BY ss.EntryDate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS LatestDatetime
    FROM Prod_ShiftSummary AS ss
)
SELECT *
FROM ShiftSummary AS ss
WHERE ss.EntryDate >= CAST(ss.LatestDatetime AS DATE);

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA