Sort by Start Date Desc if End Date is null, if not Sort by End Date 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.
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):


Values with a NULL EndDate will get a 0 and others will get a 1. This is used as the first sort criteria.
Then the second criteria is EndDate. 
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