SQL Date time column sorting

Hi Experts,
I want to sort the below data with following rules

1) Active record (end dttm is null or enddttm > current date) should be listed first with recent startdttm

2) Next Recent enddttm should be listed second

Note: To identify the recent record by using the modifiedat clolumn

Sample Data: Attached Excel -> sample data sheet

expected result: refer the attached excel expected result sheet

Thanks in advance


Note: I try to achive this by using temp table

like below

create table #one(oid bigint, startdttm datetime,enddttm datetime,modifiedat datetime)

delete from #one

insert into #one
      select oid,StartDTTM, EndDTTM,ModifiedAt from tableone where  (enddttm is null  or enddttm > getutcdate())
      order by startdttm desc, modifiedat desc
      
insert into #one
select oid,StartDTTM, EndDTTM,ModifiedAt from tableone where enddttm is not null
order by enddttm desc, modifiedat desc


select * from #one


Please provide solution using single select statement
SelvaS
LVL 9
selva_konguAsked:
Who is Participating?
 
selva_konguAuthor Commented:
The below is working
ORDER BY
CASE WHEN (EndDTTM IS NULL enddttm > getutcdate()) THEN PS.StartDTTM END DESC,
CASE WHEN EndDTTM IS NOT NULL THEN EndDTTM END DESC,
ModifiedAt DESC
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Note: I try to achive this by using temp table
a SELECT without ORDER BY will not return the data necessarily in the "order" it was inserted.
only a ORDER BY  will do so, like this, without a temp table, actually:
      select oid,StartDTTM, EndDTTM,ModifiedAt 
  from tableone 
 order by case when  (enddttm is null  or enddttm > getutcdate()) 
    then startdttm  else enddttm  end desc, modifiedat desc

Open in new window

0
 
selva_konguAuthor Commented:
Few records may not have the end dttm so we need to use seperate case statement.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.