[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 496
  • Last Modified:

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
0
selva_kongu
Asked:
selva_kongu
  • 2
2 Solutions
 
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:
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
 
selva_konguAuthor Commented:
Few records may not have the end dttm so we need to use seperate case statement.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now