Find unique number of days

One possible approach to simplify the statement in the question(fiddle), is to use VALUES table value constructor and appropriate joins:

SELECT    t.emp_id,   SUM(CASE       WHEN d1.day_no = d2.day_no THEN 1      ELSE d2.day_no - d1.day_no   END) AS no_of_daysFROM times tJOIN (VALUES ('monday', 1), ('tuesday', 2), ('wednesday', 3), ('thursday', 4), ('friday', 5)) d1 (day, day_no)    ON t.start_day = d1.dayJOIN (VALUES ('monday', 1), ('tuesday', 2), ('wednesday', 3), ('thursday', 4), ('friday', 5)) d2 (day, day_no)    ON t.end_day = d2.dayGROUP BY t.emp_id

But if you want to count the distinct days, the statement is different. You need to find all days between the start_day and end_day range and count the distinct days:

;WITH daysCTE (day, day_no) AS (   SELECT 'monday', 1 UNION ALL   SELECT 'tuesday', 2 UNION ALL   SELECT 'wednesday', 3 UNION ALL   SELECT 'thursday', 4 UNION ALL   SELECT 'friday', 5 )SELECT t.emp_id, COUNT(DISTINCT d3.day_no)FROM times tJOIN daysCTE d1 ON t.start_day = d1.dayJOIN daysCTE d2 ON t.end_day = d2.dayJOIN daysCTE d3 ON d3.day_no BETWEEN d1.day_no AND d2.day_noGROUP BY t.emp_id

You need to basically find the intersection of the days worked by each emp_id on each task with all the days of the week, and then count the distinct days:

with days_num as (  SELECT *  FROM (    VALUES ('monday', 1), ('tuesday', 2), ('wednesday', 3), ('thursday', 4), ('friday', 5)  ) AS d (day, day_no)),emp_day_nums as (  select emp_id, d1.day_no AS start_day_no, d2.day_no AS end_day_no  from times t  join days_num d1 on d1.day = t.start_day  join days_num d2 on d2.day = t.end_day)select emp_id, count(distinct d.day_no) AS distinct_daysfrom emp_day_nums ejoin days_num d on d.day_no between e.start_day_no and e.end_day_nogroup by emp_id

Output:

emp_id  distinct_days1       42       5

Demo on SQLFiddle

with cte as (Select id, start_day as day   group by id, start_day union  Select id, end_day as day   group by id, end_day)select id, count(day)from ctegroup by id