CODEWITHSUNDEEP
mysqldatetime
Bhavesh Patel
Bhavesh Patel

Reputation: 125

Fetch first weekday and last weekday from table in MySQL

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

Table

COUNTRY_CODE IS_WORKING FROM_TIME END_TIME DAY
IN no NULL NULL Sunday
IN yes 09:00:00 18:00:00 Monday
IN yes 09:00:00 18:00:00 Tuesday
IN yes 09:00:00 18:00:00 Wednesday
IN yes 09:00:00 18:00:00 Thursday
IN yes 09:00:00 18:00:00 Friday
IN no NULL NULL Saturday
UAE yes 10:00:00 19:00:00 Sunday
UAE yes 10:00:00 19:00:00 Monday
UAE yes 10:00:00 19:00:00 Tuesday
UAE yes 10:00:00 19:00:00 Wednesday
UAE yes 10:00:00 19:00:00 Thursday
UAE no NULL NULL Friday
UAE no NULL NULL Saturday
UK yes 09:00:00 18:00:00 Sunday
UK yes 09:00:00 18:00:00 Monday
UK yes 09:00:00 18:00:00 Tuesday
UK yes 09:00:00 18:00:00 Wednesday
UK yes 09:00:00 18:00:00 Thursday
UK yes 09:00:00 18:00:00 Friday
UK no NULL NULL Saturday

I want result as per below :

COUNTRY_CODE START_WORKING_DAY END_WORKING_DAY
IN Monday Friday
UAE Sunday Thursday
UK Sunday Friday

Upvotes: 0

Views: 57

Answers (1)

ysth
ysth

Reputation: 98388

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

Upvotes: 2

Related Questions