Select most recent rows

Question

Select most recent rows

Colin Hobby

SSC Veteran

Points: 284
More actions
December 22, 2006 at 1:02 pm

#171415

Hi,
I was wondering how I can get this query to only return the rows from today ( these are Dr. Orders, and I just want to see the most recent ones)
SELECT     OeOrders.SourceID, OeOrders.OrderID, OeOrders.CategoryName, OeOrders.ProviderID, OeOrders.ProviderName, OeOrders.ProviderOrder,
                      OeOrders.Meal, OeOrders.OrderDateTime, OeOrders.OrderNumber, OeOrders.OrderedProcedure, OeOrders.OrderedProcedureName, OeOrders.Priority,
                      OeOrders.ProcedureNumber, OeOrders.Quantity, OeOrders.ServiceDateTime, OeOrders.Status, OeOrders.VisitID, AdmVisits.PatientID
FROM         OeOrders INNER JOIN
                      AdmVisits ON OeOrders.SourceID = AdmVisits.SourceID AND OeOrders.VisitID = AdmVisits.VisitID
WHERE     (OeOrders.VisitID = @VisitID)
ORDER BY OeOrders.OrderDateTime
The OeOrders.OrderDateTime would be the field to compare against.. Im just not sure how.

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

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

Join to a derived table that generates the most recent date:

SELECT OeOrders.SourceID, OeOrders.OrderID, OeOrders.CategoryName, OeOrders.ProviderID, OeOrders.ProviderName, OeOrders.ProviderOrder,

OeOrders.Meal, OeOrders.OrderDateTime, OeOrders.OrderNumber, OeOrders.OrderedProcedure, OeOrders.OrderedProcedureName, OeOrders.Priority,

OeOrders.ProcedureNumber, OeOrders.Quantity, OeOrders.ServiceDateTime, OeOrders.Status, OeOrders.VisitID, AdmVisits.PatientID

FROM OeOrders

INNER JOIN AdmVisits

ON OeOrders.SourceID = AdmVisits.SourceID AND

OeOrders.VisitID = AdmVisits.VisitID

INNER JOIN

(

SELECT MIN(OeOrders.OrderDateTime) As MostRecentDate

FROM OeOrders

) As dtRecent

ON dtRecent.MostRecentDate = OeOrders.OrderDateTime

WHERE (OeOrders.VisitID = @VisitID)

ORDER BY OeOrders.OrderDateTime

bdohmen SSCrazy Points: 2996 More actions · Answer 2

AND OeOrders.OrderDateTime>CONVERT(DATETIME,CONVERT(VARCHAR(10),GETDATE(),120))

This conversion will remove the time from the current date.

Brian

Colin Hobby SSC Veteran Points: 284 More actions · Answer 3

I dont get any rows returned, do I have to change anything?

PW-201837 SSC-Insane Points: 20805 More actions · Answer 4

Let's try that again. Too much eggnog ...

My derived table was wrong, should be the MAX() not MIN() for most recent.

Also, as pointed out, I neglected the time portion of the date. What is the rule for "today" ? Everything since midnight ? Or everything in past 24 hours ?

Colin Hobby SSC Veteran Points: 284 More actions · Answer 5

Ten Centuries,

THanks for you help... I forgot about this issue until today..

I guess "Today" should be within the last 24 hrs.