CODEWITHSUNDEEP
sql-servert-sqlsql-order-by
xl0911
xl0911

Reputation: 108

Sort Asc and desc in the same column

I have a date column in a table, and I want to create a SELECT query that the result order of that query will be that all the dates that is bigger or equal to today's date will be first and the order should be ASC , and all the dates that is smaller than today's date will be second and the order will be DESC

The order should look like this:

notes:

  1. In the following list the date format is YYYY-MM-DD
  2. Today's date is 08 in Feb 2021
  3. My date's data type is date
2021-02-08
2021-02-09
2021-05-18
2022-06-29<----This is the last bigger and equal then today
2021-02-07<----This is the first smaller then today
2021-02-06
2021-01-03
2020-12-06
2020-10-08

    ;with cte
    as
    (
    select 
        CustomrId
        ,CustomrName
        ,SubscriptionDate
        ,SubscriptionInDays
        ,datediff(day, dateadd(DAY, SubscriptionInDays, SubscriptionDate), CAST(GETDATE() AS Date )) as DaysToEnd
    from 
        TBL 
)
select 
    CustomrId
    ,CustomrName
    ,SubscriptionDate
    ,SubscriptionInDays
    ,-iif(DaysToEnd > 0, 0 ,DaysToEnd) as DaysToEnd
from 
    cte
order by 
        case 
            when DaysToEnd = 0 then 0
            when DaysToEnd > 0 then 1 
        end
        ,DaysToEnd



In this approach this is the result order I'm getting:
2021-02-08
2021-02-09
2021-05-18
2022-06-29<----This is the last bigger and equal then today
2020-10-08
2020-12-06
2021-01-03
2021-02-06
2021-02-07<----This is the first smaller then today

Upvotes: 1

Views: 210

Answers (1)

forpas
forpas

Reputation: 164089

You need conditional sorting:

SELECT *
FROM tablename
ORDER BY 
  CASE WHEN datecolumn >= CONVERT(DATE, GETDATE()) THEN 1 ELSE 2 END,
  ABS(DATEDIFF(day, CONVERT(DATE, GETDATE()), datecolumn))

See the demo.
Results:

datecolumn
2021-02-08
2021-02-09
2021-05-18
2022-06-29
2021-02-07
2021-02-06
2021-01-03
2020-12-06
2020-10-08

Upvotes: 2

Related Questions