0

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.

5

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
  • Beautiful! Thanks again. I wish I could upvote you one more. Last update, I swear. Updating the question... – daniel9x Mar 10 '16 at 21:00
  • null start and already mixed up with not null end. double goes to the end. null end to the beginning. is it what you want ? I don't understand where you want to put the null start with not null end. see update sample.... – Julien Vavasseur Mar 10 '16 at 21:16
  • That's exactly what I need. Thank you so much! I wasn't look at my results with the right goggles. – daniel9x Mar 11 '16 at 1:32
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
  • Right you are. Sorry I neglected to mention the specific sort direction. I have updated the question and +1'd your answer because it was technically right. – daniel9x Mar 10 '16 at 21:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.