Sort by Start Date Desc if End Date is null, if not Sort by End Date

Question

I have a SQL Server (RDBMS) table with a list of Activities. Each activity has a Start and End Date.

The requirement is to sort them is order such that the ones with a NULL end date display first (which suggest "current" - start date descending), but the ones with values for both start and end to be sorted at the end of the "current" rows and sorted by end_date descending.

Lastly, all values with null for start and end dates must be at the end of the result set.

Values with an end date but a null start date should be sorted in the second section of the sort among the END date desc piece.

Answer 1

You can use a CASE expression in the ORDER BY:

ORDER BY CASE WHEN EndDate IS NULL 0 ELSE 1 END ASC
    , EndDate DESC
    , StartDate DESC

Sort criteria in order (link):

1. Values with a NULL EndDate will get a 0 and others will get a 1. This is used as the first sort criteria.
2. Then the second criteria is EndDate.
3. Finally ties will be sorted by StartDate.

You can play with ASC and DESC and column order if the global order is not what you expect.

Sample data and query:

DECLARE @data TABLE(id int identity(0, 1), StartDate date, EndDate date)
INSERT INTO @data(StartDate, EndDate) VALUES
    ('20160101', '20160105')
    , ('20160101', null)
    , ('20160101', '20160107')
    , ('20160102', '20160103')
    , ('20160102', '20160105')
    , ('20160102', null)
    --, (null, '20160105')
    --, (null, null);

SELECT * 
FROM @data
ORDER BY CASE WHEN EndDate IS NULL THEN 0 ELSE 1 END ASC
    , EndDate DESC
    , StartDate DESC

Output:

id  StartDate   EndDate
5   2016-01-02  NULL
1   2016-01-01  NULL
2   2016-01-01  2016-01-07
4   2016-01-02  2016-01-05
0   2016-01-01  2016-01-05
3   2016-01-02  2016-01-03

This ORDER BY clause (link) will put double NULL (Start + End) at the end:

ORDER BY CASE 
          WHEN EndDate IS NULL AND StartDate IS NULL THEN 2 
          WHEN EndDate IS NULL THEN 0 
          ELSE 1 END ASC
    , EndDate DESC
    , StartDate DESC;

id | StartDate            | EndDate
5  | 2016-01-02 00:00:00  | NULL
1  | 2016-01-01 00:00:00  | NULL
2  | 2016-01-01 00:00:00  | 2016-01-07 00:00:00
4  | 2016-01-02 00:00:00  | 2016-01-05 00:00:00
0  | 2016-01-01 00:00:00  | 2016-01-05 00:00:00
6  | NULL                 | 2016-01-05 00:00:00
3  | 2016-01-02 00:00:00  | 2016-01-03 00:00:00
7  | NULL                 | NULL

Answer 2

Seems to me that according to the question, simply sorting by EndDate would do the trick... The OP does not specify that the results are to be returned in descending order. In fact, he does not state that the rows need to be ordered by StartDate at all. Since NULL dates will be ordered before dates with values, an ORDER BY EndDate fulfills the requirement. Otherwise, ORDER BY EndDate, StartDate should be sufficient.

Using @Julien's sample code, the final SELECT could look like this:

DECLARE @data TABLE(id int identity(0, 1), StartDate date, EndDate date)
INSERT INTO @data(StartDate, EndDate) VALUES
    ('20160101', '20160105')
    , ('20160101', null)
    , ('20160101', '20160107')
    , ('20160102', '20160103')
    , ('20160102', '20160105')
    , ('20160102', null);

SELECT * 
FROM @data
ORDER BY EndDate