Intercity Bookings- Rahil and Shiva

set @startdate1 = '2017-03-01';
set @startdate2 = '2020-05-01';
use socar_malaysia;
select date_format(convert_tz(r.return_at, '+00:00', '+8:00'),'%Y-%m-%d') as date,
count(if(r.way in('oneway','onewayReturn' ), r.id, null)) as res_oneway_Intercity

from reservations r, zones z, members m, reservation_appendixes ra

Where
    r.start_zone_id = z.id
    and r.member_id = m.id
    and r.id = ra.reservation_id
    and m.imaginary in ('sofam', 'normal')
    and r.member_id not in ('125', '127')
    and z.id not in (2, 3, 101)
    and r.start_zone_id in (391, 966, 28, 941, 1312, 7, 740, 89, 126, 954, 1281, 849, 822, 1068, 792, 1165, 471)
	and r.end_zone_id not in (244, 245)
    and r.state in ('completed')
    and r.return_at + interval 8 hour >= @startdate1
    and r.return_at + interval 8 hour <= @startdate2

group by date
order by date desc