CODEWITHSUNDEEP
sqlsql-serverconditional-statements
Vincent Ducroquet
Vincent Ducroquet

Reputation: 924

Is it possible to return fields conditionally in SQL?

I'd like to build a query that returns if tasks are very late/late/near on time/on time.

Task status : early if -2 days

near on time if -1 day

late if 1 day

vary late if 2 days

What i've tried :

SELECT field_1, diff,
COUNT(CASE WHEN diff <= -2 THEN 1 END) onTime,
COUNT(CASE WHEN diff <= -1 THEN 1 END) nearOnTime,
Count(CASE WHEN diff >= 2 THEN 1 END) veryLate,
Count(CASE WHEN diff >= 0 THEN 1 END) Late
FROM(
     SELECT field_1, DATEDIFF(day,Max(predicted_date), realization_date) as diff
     FROM table
     Group by field_1, realization_date
     HAVING end_date is not null) as req1
GROUP BY field_1, diff)

diff : difference between a predicated date and a realization date => returns the number of day between these two dates

It returns :

field_1  |  diff  |  onTime  |  nearOnTime  |  veryLate  |  Late
---------+--------+----------+--------------+------------+-------
 task1   |   -3   |     1    |      1       |     0      |   0
 task2   |    2   |     0    |      0       |     1      |   1

I think my approach is bad, so what is or are my options to returns task status?

Upvotes: 0

Views: 58

Answers (1)

Randy
Randy

Reputation: 16677

maybe something along these lines.. ( a fiddle would help - this has not been tested)

SELECT field_1, diff,
CASE WHEN diff <= -2 THEN 'On Time',
WHEN diff <= -1 THEN 'nearOnTime',
WHEN diff >= 2 THEN 'veryLate',
WHEN diff >= 0 THEN 'Late'
else 'OK'  END as status
FROM(
     SELECT field_1, DATEDIFF(day,Max(predicted_date), realization_date) as diff
     FROM table
     Group by field_1, realization_date
     HAVING end_date is not null) as req1
GROUP BY field_1, diff)

Upvotes: 3

Related Questions