2nd reservation on the same day Query - Rahil

set @row_number := 0;

Select I.* from
(SELECT
@row_number:= CASE
WHEN @customer_no = A.mid
THEN @row_number + 1
ELSE 1
END AS num,
@customer_no:= A.mid mid,
A.rid,
A.return_date as date,
A.dur as duration,
A.age as age,
A.resv_hour as start_hour,
A.region as region,
IFNULL(E.charges,0) as gross_rev,
IFNULL(F.coupon_Spent,0) as Coupon_Spent,
round((IFNULL(E.charges,0) - IFNULL(F.coupon_Spent,0)),2) as net_rev
FROM
(select
distinct r.id as rid, r.member_id as mid,YEAR(r.start_at) - YEAR(m.birthday) as age,z.region as region,hour(r.start_at + interval '8' hour) as resv_hour,
round(sum(timestampdiff(minute, r.start_at, r.end_at)/60),2) as dur,
Date(r.return_at + interval '8' hour) as return_date
from reservations r left join members m on r.member_id = m.id
left join reservation_appendixes ra on ra.reservation_id = r.id
left join zones z on z.id = r.start_zone_id
where r.state in ('completed')
and r.return_at + interval 8 hour >= @startdate1
and r.return_at + interval 8 hour <= @startdate2
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
and ra.performer not in ('admin')
group by rid) A
left join
(select c.reservation_id as rid, sum(c.amount) as charges from charges c
where c.state='normal' and c.kind in ('rent','oneway','d2d','mileage','insurance')
group by rid) E on A.rid = E.rid
left join
(select p.reservation_id as rid, month(p.created_at + interval '8' hour) as month,Year(p.created_at + interval '8' hour) as year, IFNULL(p.amount,0) as coupon_Spent from payments p
where p.state = 'normal' and p.paid_type = 'coupon'
group by p.reservation_id) F on A.rid = F.rid
ORDER BY date, mid,rid) I
where I.num in (2)
order by date desc