I have a schedule table in mysql. I want to fetch first day and last day of the table as per country code.

Table

I want result as per below :

CodePudding user response：

I think this is simply:

select COUNTRY_CODE, 
   dayname(min(str_to_date(concat(197001,DAY),"%X%V%W"))) start_working_day,
   dayname(max(str_to_date(concat(197001,DAY),"%X%V%W"))) end_working_day
from test
where IS_WORKING="yes"
group by 1

CodePudding user response：

Below solution maybe it can be simplified a little bit, but I can't think a better solution right now.

I have used twice row number and order by case including Sunday as first day and Saturday as last , in this way you can unique identify rows.

with cte as 
( select *, row_number() over( partition by COUNTRY_CODE order by  
        CASE
          WHEN Day = 'Sunday' THEN 1
          WHEN Day = 'Monday' THEN 2
          WHEN Day = 'Tuesday' THEN 3
          WHEN Day = 'Wednesday' THEN 4
          WHEN Day = 'Thursday' THEN 5
          WHEN Day = 'Friday' THEN 6
          WHEN Day = 'Saturday' THEN 7
        END ASC ) as min_row_num,
     row_number() over(partition by COUNTRY_CODE order by  
    CASE
          WHEN Day = 'Sunday' THEN 1
          WHEN Day = 'Monday' THEN 2
          WHEN Day = 'Tuesday' THEN 3
          WHEN Day = 'Wednesday' THEN 4
          WHEN Day = 'Thursday' THEN 5
          WHEN Day = 'Friday' THEN 6
          WHEN Day = 'Saturday' THEN 7
     END DESC) as max_row_num
  from test
  where IS_WORKING='yes'
) select c2.COUNTRY_CODE, START_WORKING_DAY,END_WORKING_DAY
  from   ( select COUNTRY_CODE,min(DAY) as END_WORKING_DAY
                from cte 
                where max_row_num = (select min(max_row_num) from cte)
                group by COUNTRY_CODE
          ) as c1 
  inner join 
          ( select COUNTRY_CODE,min(DAY) as START_WORKING_DAY
                from cte 
                where min_row_num = (select min(min_row_num) from cte)
                group by COUNTRY_CODE
           ) as c2 on c1.COUNTRY_CODE=c2.COUNTRY_CODE

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f9100085f28ce84195615c7add66d0e2