CODEWITHSUNDEEP
mysqlsqlrangesequence
Iwan Ross
Iwan Ross

Reputation: 354

MySql Select and Group by Open and Closing hrs in range

I have a table that stores the open and close hours for a restaurant. I need a sql query to select the days in a range by status Open or Closed for a particular business.

The table:

id tbl_index_id  dow       status starttime endtime
 1            1  Monday    Closed 
 2            1  Tuesday   Open   8.00      17.00
 3            1  Wednesday Open   8.00      17.00
 4            1  Thursday  Open   8.00      17.00
 5            1  Friday    Open   8.00      17.00
 6            1  Saturday  Open   8.00      17.00
 7            1  Sunday    Closed

The tabl_index_id is the foreign key for the restaurant. I know, funny name, but there is a reason for it.

What I need is this:

Open: Tuesday - Saturday: 08:00AM - 05:00PM Saturday and Sunday: Closed

The only solution I could come up with is this:

SELECT DoW as openDays, start_time, end_time from tbl_businesshrs WHERE 
tbl_businesshrs.tbl_index_id = 1 and status = 'Open' GROUP BY DoW

Please help. I honestly cannot figure this one out.

Upvotes: 0

Views: 238

Answers (2)

P.Salmon
P.Salmon

Reputation: 17640

A general solution is a bit tricky but just for fun here's one which uses variables to calculate block numbers and row numbers based on a derived composite 'timekey' and then joins 

drop table if exists tbl_businesshrs;
create table tbl_businesshrs(id int auto_increment primary key, 
tbl_index_id int,
dow varchar(3), status varchar(6), start_time time, end_time time);

insert into tbl_businesshrs (dow,tbl_index_id, status,start_time, end_time) values
('mon',1,'closed',null,null),
('tue',1,'open','08:00','17:00'),
('wed',1,'open','08:00','17:00'),
('thu',1,'open','05:00','17:00'),
('fri',1,'open','08:00','16:00'),
('sat',1,'open','08:00','17:00'),
('sun',1,'closed',null,null);


select
        concat('open ', 
        group_concat( 
        case when c.dow = d.dow then concat(c.dow,' ' ,c.start_time,'-',c.end_time) 
        else concat(c.dow,' to ',d.dow, ' ', c.start_time, '-',c.end_time)
        end
        )
        ) open,
        (select group_concat(dow) from tbl_businesshrs t where t.status = 'closed') closed

from
(
select * from
(
select id,dow,tbl_index_id,status,start_time,end_time, concat(start_time,end_time) timekey,
       if (concat(start_time,end_time) <> @timekey, @bn:=@bn+1,@bn:=@bn) bn,
       if (concat(start_time,end_time) <> @timekey, @rn:=1,@rn:=@rn + 1) rn,
         @timekey:=concat(coalesce(start_time,'00:00:00'),coalesce(end_time,'00:00:00')) tk
from tbl_businesshrs t
cross join
(select @bn:=0,@rn:=0,@timekey:= concat(cast('00:00:00' as time),cast('00:00:00' as time))) r
where status = 'open'
order by id
) a 
where rn = 1
) c

join
(
select b.*,
         if(b.bn <> @p ,@rn:=1,@rn:=@rn+1) rn,
         @p:=b.bn p
from
(       
select id,dow,tbl_index_id,status,start_time,end_time, concat(start_time,end_time) timekey,
       if (concat(start_time,end_time) <> @timekey1, @bn1:=@bn1+1,@bn1:=@bn1) bn,
       #if (concat(start_time,end_time) <> @timekey1, @rn1:=1,@rn1:=@rn1+1) rn,
         @timekey1:=concat(coalesce(start_time,'00:00:00'),coalesce(end_time,'00:00:00')) tk

from tbl_businesshrs t
cross join
(select @bn1:=0,
#@rn1:=0,
@timekey1:= concat(cast('00:00:00' as time),cast('00:00:00' as time))) r
where status = 'open'
order by id
) b
cross join(select @p:=0,@rn:=0) r
order by b.bn ,b.id desc
) d
on d.bn  = c.bn 
where d.rn = c.rn
;

+-----------------------------------------------------------------------------------------------------+---------+
| open                                                                                                | closed  |
+-----------------------------------------------------------------------------------------------------+---------+
| open tue to wed 08:00:00-17:00:00,thu 05:00:00-17:00:00,fri 08:00:00-16:00:00,sat 08:00:00-17:00:00 | mon,sun |
+-----------------------------------------------------------------------------------------------------+---------+
1 row in set (0.12 sec)

and with your sample data

+-----------------------------------+---------+
| open                              | closed  |
+-----------------------------------+---------+
| open tue to sat 08:00:00-17:00:00 | mon,sun |
+-----------------------------------+---------+
1 row in set (0.00 sec)

Note we are always joining block number to block number and row number = 1

Upvotes: 1

Iwan Ross
Iwan Ross

Reputation: 354

This is the solution I came up with:

SELECT  (SELECT LEFT(DoW,3) from tbl_businesshrs where tbl_businesshrs.id = 
(SELECT MIN(tbl_businesshrs.id) FROM tbl_businesshrs WHERE 
tbl_businesshrs.tbl_index_id=1 AND tbl_businesshrs.status='Open')) as 
fistDay, 
(SELECT LEFT(DoW,3) from tbl_businesshrs where tbl_businesshrs.id = (SELECT 
MAX(tbl_businesshrs.id) FROM tbl_businesshrs WHERE 
tbl_businesshrs.tbl_index_id=1 AND tbl_businesshrs.status='Open')) as 
lastDay, 
start_time, end_time from tbl_businesshrs WHERE tbl_businesshrs.tbl_index_id 
= 
1 and status = 'Open' GROUP BY status ORDER BY id ASC

And the output:

The solution

Upvotes: 0

Related Questions