Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- select
- A.mon,
- A.city,
- sum(A.amount) as net_revenue,
- sum(B.amount) as Gross_revenue
- from zones z
- join
- (select
- sum(p.amount) as amount,
- month(CONVERT_TZ(r.start_at, '+00:00', '+8:00')) as mon,
- p.state as state,
- z.city as city,
- r.start_zone_id as rzid,
- p.reservation_id as prid
- from payments p
- left outer join reservations r
- on p.reservation_id = r.id
- left outer join zones z
- on z.id = r.start_zone_id
- left outer join members m
- on m.id = r.member_id
- where CONVERT_TZ(r.start_at, '+00:00', '+8:00') >= '2019-1-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 mon,city) A
- on A.rzid = z.id
- left outer join
- (select
- sum(p.amount) as amount,
- month(CONVERT_TZ(r.start_at, '+00:00', '+8:00')) as mon,
- z.city as city,
- r.start_zone_id as rzid,
- p.state as state,
- p.reservation_id as prid
- from payments p
- left outer join reservations r
- on p.reservation_id = r.id
- left outer join zones z
- on z.id = r.start_zone_id
- left outer join members m
- on m.id = r.member_id
- where CONVERT_TZ(r.start_at, '+00:00', '+8:00') >= '2019-1-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'
- group by mon,city
- ) B
- on B.rzid = z.id
- Group by A.mon,A.city
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement