SHARE
TWEET

Gross Revenues Vs Net Revenue by Month and Region

ahmedrahil786 Mar 21st, 2019 58 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. select
  2. A.mon,
  3. A.city,
  4. sum(A.amount) as net_revenue,
  5. sum(B.amount) as Gross_revenue
  6.  
  7. from zones z
  8.  
  9. join
  10.  
  11. (select
  12. sum(p.amount) as amount,
  13. month(CONVERT_TZ(r.start_at, '+00:00', '+8:00')) as mon,
  14. p.state as state,
  15. z.city as city,
  16. r.start_zone_id as rzid,
  17. p.reservation_id as prid
  18. from payments p
  19.  
  20. left outer join reservations r
  21. on p.reservation_id = r.id
  22.  
  23. left outer join zones z
  24. on z.id = r.start_zone_id
  25.  
  26. left outer join members m
  27. on m.id = r.member_id
  28.  
  29. where CONVERT_TZ(r.start_at, '+00:00', '+8:00') >= '2019-1-1 00:00'
  30. and CONVERT_TZ(r.start_at, '+00:00', '+8:00') < '2019-4-1'
  31. and m.imaginary = 'normal'
  32. and m.state = 'normal'
  33. and r.state IN ('completed','inuse','reserved')
  34. and p.state = 'normal'
  35. and p.paid_type = 'card'
  36.  
  37. group by mon,city) A
  38. on A.rzid = z.id
  39.  
  40. left outer join
  41.  
  42. (select
  43. sum(p.amount) as amount,
  44. month(CONVERT_TZ(r.start_at, '+00:00', '+8:00')) as mon,
  45. z.city as city,
  46. r.start_zone_id as rzid,
  47. p.state as state,
  48. p.reservation_id as prid
  49. from payments p
  50.  
  51. left outer join reservations r
  52. on p.reservation_id = r.id
  53.  
  54. left outer join zones z
  55. on z.id = r.start_zone_id
  56.  
  57. left outer join members m
  58. on m.id = r.member_id
  59.  
  60. where CONVERT_TZ(r.start_at, '+00:00', '+8:00') >= '2019-1-1 00:00'
  61. and CONVERT_TZ(r.start_at, '+00:00', '+8:00') < '2019-4-1'
  62. and m.imaginary = 'normal'
  63. and m.state = 'normal'
  64. and r.state IN ('completed','inuse','reserved')
  65. and p.state = 'normal'
  66.  
  67. group by mon,city
  68. ) B
  69. on B.rzid = z.id
  70.  
  71. Group by A.mon,A.city
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top