SQL Date time column sorting

Posted on 2012-09-10
Last Modified: 2012-09-16
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
Question by:selva_kongu
    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    >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

    LVL 9

    Accepted Solution

    The below is working
    CASE WHEN (EndDTTM IS NULL enddttm > getutcdate()) THEN PS.StartDTTM END DESC,
    ModifiedAt DESC
    LVL 9

    Author Closing Comment

    Few records may not have the end dttm so we need to use seperate case statement.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now