CODEWITHSUNDEEP
sqlsql-servert-sqlquery-performance
Deepak
Deepak

Reputation: 123

Query to get latest datetime for 2 columns individually

create table Orders
(
city varchar(5)
,orderid int
,OrderedDateTime datetime
,ThresholdDatetime datetime
)

insert into Orders values ('Texas',23,'2017-08-24 12:20:56.560','2017-08-24 20:00:23.000')
insert into Orders values ('Texas',23,'2017-08-24 13:20:45.560','2017-08-24 22:20:23.000')
insert into Orders values ('Texas',23,'2017-08-24 16:20:45.560','2017-08-24 20:00:23.000')
insert into Orders values ('Texas',23,'2017-08-24 17:20:23.560','2017-08-24 22:00:23.000')
insert into Orders values ('Texas',23,'2017-08-23 12:20:23.560','2017-08-23 14:00:23.000')
insert into Orders values ('Texas',23,'2017-08-23 13:20:23.560','2017-08-23 21:20:23.000')
insert into Orders values ('Texas',23,'2017-08-23 16:20:23.560','2017-08-23 20:00:23.000')
insert into Orders values ('Texas',23,'2017-08-23 18:20:23.560','2017-08-23 20:00:23.000')
insert into Orders values ('Texas',23,'2017-08-22 12:20:23.560','2017-08-22 14:00:23.000')
insert into Orders values ('Texas',23,'2017-08-22 13:20:23.560','2017-08-22 21:20:23.000')
insert into Orders values ('Texas',23,'2017-08-22 16:20:23.560','2017-08-22 20:00:23.000')
insert into Orders values ('Texas',23,'2017-08-22 19:20:23.560','2017-08-22 20:00:23.000')

Given data as below[![][1]]2

The requirement is to get latest datetime from the 'orderedDateTime' column and latest datetime from 'ThresholdDateTime' column which should return a record for each day as mentioned in the output image. I already have a solution for this, but seeking better answers than mine , as i think my query is having performance issues 

select city,orderid, max(OrderedDateTime) as MaxOrderedDateTime ,max(ThresholdDatetime) as MaxThresholdTime
,day(ThresholdDatetime) as [UniqueDay]
from dbo.Orders 
where OrderedDateTime<=ThresholdDatetime
and convert(date,OrderedDateTime)=convert(date,ThresholdDatetime)
group by city,orderid,day(ThresholdDatetime)

Upvotes: 1

Views: 69

Answers (1)

Jason A. Long
Jason A. Long

Reputation: 4442

Try coding like this:

SELECT 
    o.city,
    o.orderid,
    MaxOrderedDateTime = MAX(o.OrderedDateTime),
    MaxThresholdTime = MAX(o.ThresholdDatetime)
FROM
    #Orders o
GROUP BY
    o.city,
    o.orderid,
    CAST(o.OrderedDateTime AS DATE);

Upvotes: 1

Related Questions