Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- select
- A.memberid,
- A.day,
- A.ridelength ,
- A.ridelengthinminutes,
- A.startingday,
- A.startingtime,
- B.regionname,
- B.carname,
- A.rid,
- A.carid,
- B.zoneid,
- A.state,
- A.amount
- from (select
- distinct m.id as memberid,
- sum(p.amount) as amount,
- date(r.occupy_start_at + interval '8' hour) as day,
- timestampdiff(minute,r.start_at, r.end_at)/60 as ridelength,
- TIMESTAMPDIFF(minute,r.start_at + interval '8' hour,r.end_at + interval '8' hour) as ridelengthinminutes,
- weekday(r.occupy_start_at + interval '8' hour) as startingday,
- hour(r.occupy_start_at + interval '8' hour) as startingtime,
- r.id as rid,
- r.state as state,
- r.car_id as carid
- from members m
- left outer join reservations r
- on m.id = r.member_id
- left outer join payments p
- on p.reservation_id = r.id
- where CONVERT_TZ(r.start_at, '+00:00', '+8:00') >= '2019-3-1 00:00'
- and CONVERT_TZ(r.start_at, '+00:00', '+8:00') < '2019-4-1'
- and m.imaginary = 'normal'
- and m.state = 'normal'
- and r.state IN ('completed','inuse','reserved')
- and p.state = 'normal'
- and p.paid_type = 'card'
- group by m.id,r.id
- order by amount desc) A
- left outer join
- (select
- c.id as carid,
- cc.car_name as carname,
- z.region as regionname,
- z.id as zoneid
- from cars c
- left outer join car_classes cc
- on cc.id = c.car_class_id
- left outer join zones z
- on z.id = c.zone_id
- left outer join car_zone_logs as cz
- on c.id = cz.car_id
- where cz.car_state = 'Normal'
- #and z.state = 'normal'
- group by c.id) B
- on A.carid = B.carid
- group by A.memberid,B.carid,A.rid
- order by A.amount desc
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement