Josh Graham Josh Graham - 19 days ago 5
SQL Question

Order by all dates below some value descending then by all dates above said value ascending

Given the following data:

Effective Date
--------------
2014-01-01
2015-01-01
2016-01-01
2017-01-01
2018-01-01


I want to order by date relative to (for example)
2016-06-01
, where all descending values below
2016-06-01
come before all ascending values above
2016-06-01
.

Relative to date: 2016-06-01 the desired ordering is

Effective Date
--------------
2016-01-01
2015-01-01
2014-01-01
2017-01-01
2018-01-01


The best way I have come up with to achieve the desired ordering is:

CREATE TABLE #Dates
(
EffectiveDate DATETIME2
);

DECLARE @CurrentDate DATETIME2 = '2016-06-01';
DECLARE @MinDate DATETIME2 = '0001-01-01';

INSERT INTO #Dates (EffectiveDate) VALUES ('2014-01-01');
INSERT INTO #Dates (EffectiveDate) VALUES ('2015-01-01');
INSERT INTO #Dates (EffectiveDate) VALUES ('2016-01-01');
INSERT INTO #Dates (EffectiveDate) VALUES ('2017-01-01');
INSERT INTO #Dates (EffectiveDate) VALUES ('2018-01-01');

SELECT
*
FROM
#Dates
ORDER BY
CASE
WHEN #Dates.EffectiveDate < @CurrentDate
THEN DATEDIFF(DAY, #Dates.EffectiveDate, @CurrentDate)
ELSE
DATEDIFF(DAY, @CurrentDate, #Dates.EffectiveDate) - DATEDIFF(DAY, @CurrentDate, @MinDate)
END;

DROP TABLE #Dates;


Is there a better way to achieve the desired ordering?

DVT DVT
Answer

If you have 2012 and up.

SELECT
    *
FROM
    #Dates
ORDER BY
    IIF(#Dates.EffectiveDate <= @CurrentDate, 0,1) ASC
    , ABS(DATEDIFF(day,#Dates.EffectiveDate, @CurrentDate)) ASC;

If you have 2008:

SELECT
    *
FROM
    #Dates
ORDER BY
    CASE 
        WHEN #Dates.EffectiveDate <= @CurrentDate THEN  0
        ELSE 1 END ASC
    , ABS(DATEDIFF(day,#Dates.EffectiveDate, @CurrentDate)) ASC;
Comments