API tools faq
paste
Guest User

Untitled

a guest
Nov 9th, 2016
283
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 12.42 KB | None | 0 0
raw download clone embed print report
  1. SELECT totalseats.total_seat_count,totalseats.service_key,totalseats.source_id,totalseats.destination_id,totalseats.source_halt_minutes,totalseats.dest_halt_minutes, totalseats.boarding_day_count,
  2. CASE WHEN (COUNT(bookedseats.seat_num)>IFNULL(usedcnt.usedseatcount,0)) THEN
  3. (COUNT(bookedseats.seat_num)-(COUNT(bookedseats.seat_num)-IFNULL(usedcnt.usedseatcount,0)))
  4. WHEN (COUNT(bookedseats.seat_num)<=IFNULL(usedcnt.usedseatcount,0))
  5. THEN COUNT(bookedseats.seat_num)
  6. END AS used_seats,
  7. IFNULL(quota.quota_seats,0) AS quota_seats,
  8.  
  9. CASE WHEN (COUNT(blockedseats.seatNo)>IFNULL(resseatcnt.rescount,0)) THEN
  10. (COUNT(blockedseats.seatNo)-(COUNT(blockedseats.seatNo)-IFNULL(resseatcnt.rescount,0)))
  11. ELSE IFNULL(COUNT(blockedseats.seatNo),0)
  12. END AS res_seats,
  13.  
  14. CASE WHEN ((COUNT(bookedseats.seat_num)>IFNULL(usedcnt.usedseatcount,0)) AND
  15. (COUNT(blockedseats.seatNo)>IFNULL(resseatcnt.rescount,0))) THEN
  16. (totalseats.total_seat_count-((COUNT(bookedseats.seat_num)-(COUNT(bookedseats.seat_num)-IFNULL(usedcnt.usedseatcount,0)))
  17. +IFNULL(quota.quota_seats,0)+
  18. (COUNT(blockedseats.seatNo)-(COUNT(blockedseats.seatNo)-IFNULL(resseatcnt.rescount,0)))
  19. ))
  20. WHEN ((COUNT(bookedseats.seat_num),0)<(IFNULL(usedcnt.usedseatcount,0))
  21. AND (COUNT(blockedseats.seatNo)>IFNULL(resseatcnt.rescount,0)))THEN
  22. (totalseats.total_seat_count-(COUNT(bookedseats.seat_num)+IFNULL(quota.quota_seats,0)+
  23. (COUNT(blockedseats.seatNo)-(COUNT(blockedseats.seatNo)-IFNULL(resseatcnt.rescount,0)))
  24. ))
  25. /*WHEN ((COUNT(bookedseats.seat_num)>IFNULL(usedcnt.usedseatcount,0)) AND (COUNT(blockedseats.seatNo)<IFNULL(resseatcnt.rescount,0))) THEN
  26. (totalseats.total_seat_count-((COUNT(bookedseats.seat_num)-(COUNT(bookedseats.seat_num)-IFNULL(usedcnt.usedseatcount,0)))
  27. +IFNULL(quota.quota_seats,0)+
  28. IFNULL(COUNT(blockedseats.seatNo),0)
  29. ))*/
  30. ELSE (totalseats.total_seat_count-(IFNULL(COUNT(bookedseats.seat_num),0)+IFNULL(quota.quota_seats,0)+
  31. IFNULL(COUNT(blockedseats.seatNo),0)))
  32. END AS freeseats
  33.  
  34. FROM (
  35. SELECT total_seat_count,asr.service_key,oso.override_id,osr.source_id,osr.destination_id
  36. ,boarding_day_count ,
  37. (SELECT halt_minutes FROM test.oprs_service_halts WHERE service_id=asr.service_key AND
  38. station_id=osr.source_id AND halt_override_id=os.override_id) AS source_halt_minutes,
  39. (SELECT halt_minutes FROM test.oprs_service_halts WHERE service_id=asr.service_key AND
  40. station_id=osr.destination_id AND halt_override_id=os.override_id) AS dest_halt_minutes
  41. FROM test.oprs_service_overrides AS oso
  42. INNER JOIN abrs_service AS asr ON asr.service_key=oso.service_id AND asr.status='A'
  43. INNER JOIN test.oprs_bus_layout AS obl ON asr.layout_id=obl.layout_id
  44. INNER JOIN test.oprs_service_halt_route_fares AS osr ON osr.route_fare_override_id=oso.override_id
  45. INNER JOIN test.oprs_service_overrides AS os ON os.service_id=asr.service_key AND os.override_for='Path'
  46. AND(('2016-11-09' BETWEEN CAST(os.override_from AS DATE) AND CAST(os.override_to AS DATE))OR os.override_from IS NULL)
  47. INNER JOIN test.`oprs_service_halts` AS osh ON osh.halt_override_id =os.override_id AND osh.station_id=osr.source_id
  48. INNER JOIN test.`oprs_service_halt_bordings` AS osb ON osb.service_halt_id=osh.service_halt_id
  49. WHERE oso.override_for='fare' AND (
  50. ('2016-11-09' BETWEEN CAST(oso.override_from AS DATE) AND CAST(oso.override_to AS DATE))OR oso.override_from IS NULL
  51. )
  52. AND asr.Travel_partner_key=485 AND boarding_is_prime='Yes' AND boarding_day_count=0
  53. GROUP BY asr.service_key,oso.override_id,osr.source_id,osr.destination_id
  54.  
  55. UNION ALL
  56.  
  57. SELECT total_seat_count,asr.service_key,oso.override_id,osr.source_id,osr.destination_id
  58. ,boarding_day_count ,
  59. (SELECT halt_minutes FROM test.oprs_service_halts WHERE service_id=asr.service_key AND
  60. station_id=osr.source_id AND halt_override_id=os.override_id) AS source_halt_minutes,
  61. (SELECT halt_minutes FROM test.oprs_service_halts WHERE service_id=asr.service_key AND
  62. station_id=osr.destination_id AND halt_override_id=os.override_id) AS dest_halt_minutes
  63. FROM test.oprs_service_overrides AS oso
  64. INNER JOIN abrs_service AS asr ON asr.service_key=oso.service_id AND asr.status='A'
  65. INNER JOIN test.oprs_bus_layout AS obl ON asr.layout_id=obl.layout_id
  66. INNER JOIN test.oprs_service_halt_route_fares AS osr ON osr.route_fare_override_id=oso.override_id
  67. INNER JOIN test.oprs_service_overrides AS os ON os.service_id=asr.service_key AND os.override_for='Path'
  68. AND(('2016-11-08' BETWEEN CAST(os.override_from AS DATE) AND CAST(os.override_to AS DATE))OR os.override_from IS NULL)
  69. INNER JOIN test.`oprs_service_halts` AS osh ON osh.halt_override_id =os.override_id AND osh.station_id=osr.source_id
  70. INNER JOIN test.`oprs_service_halt_bordings` AS osb ON osb.service_halt_id=osh.service_halt_id
  71. WHERE oso.override_for='fare' AND (
  72. ('2016-11-08' BETWEEN CAST(oso.override_from AS DATE) AND CAST(oso.override_to AS DATE))OR oso.override_from IS NULL
  73. )
  74. AND asr.Travel_partner_key=485 AND boarding_is_prime='Yes' AND boarding_day_count=1
  75. GROUP BY asr.service_key,oso.override_id,osr.source_id,osr.destination_id
  76. ) AS totalseats
  77.  
  78. LEFT JOIN (
  79. SELECT COUNT(seatcnt.seat_num) AS usedseatcount,seatcnt.service_key FROM (
  80.  
  81. SELECT DISTINCT seat_num,adt.service_key,art.Source_Id,art.Destination_Id,oso.override_id,
  82. (SELECT halt_minutes FROM test.oprs_service_halts WHERE service_id=adt.service_key AND
  83. station_id=art.Source_Id AND halt_override_id=oso.override_id) AS source_halt_minutes,
  84. (SELECT halt_minutes FROM test.oprs_service_halts WHERE service_id=asr.service_key AND
  85. station_id=art.Destination_Id AND halt_override_id=oso.override_id) AS dest_halt_minutes
  86. FROM abrs_reserved_tickets_det AS adt
  87. INNER JOIN abrs_reserved_tickets AS art ON art.Ticket_key=adt.Oprs_Ticket_key
  88. INNER JOIN test.oprs_service_overrides AS oso ON oso.service_id=adt.service_key
  89. AND override_for='path' AND (
  90. ('2016-11-09' BETWEEN CAST(override_from AS DATE) AND CAST(override_to AS DATE))
  91. OR override_from IS NULL
  92. )
  93. INNER JOIN test.oprs_service_halts AS osh ON osh.halt_override_id=oso.override_id
  94. INNER JOIN abrs_service AS asr ON asr.Service_key= adt.service_key
  95. WHERE asr.Travel_partner_key=485 AND adt.journey_date='2016-11-09' AND adt.status='A' AND
  96. (adt.seat_booked_type = 'Normal' OR adt.seat_booked_type IS NULL) GROUP BY adt.service_key,seat_num
  97. ) AS seatcnt GROUP BY seatcnt.service_key
  98. ) AS usedcnt ON usedcnt.service_key=totalseats.totalseats.service_key
  99.  
  100.  
  101. LEFT JOIN (
  102. SELECT COUNT(seatcnt.seatNo) AS rescount,seatcnt.serviceId FROM (
  103.  
  104. SELECT DISTINCT seatNo,adt.serviceId,art.sourceid,art.destinationid,oso.override_id,
  105. (SELECT halt_minutes FROM test.oprs_service_halts WHERE service_id=adt.serviceId AND
  106. station_id=art.sourceid AND halt_override_id=oso.override_id) AS source_halt_minutes,
  107. (SELECT halt_minutes FROM test.oprs_service_halts WHERE service_id=asr.service_key AND
  108. station_id=art.destinationid AND halt_override_id=oso.override_id) AS dest_halt_minutes
  109. FROM test.`oprs_reserved_tickets_det_tempapi` AS adt
  110. INNER JOIN test.`oprs_reserved_tickets_tempapi` AS art ON art.Reference_key=adt.Reference_key
  111. INNER JOIN test.oprs_service_overrides AS oso ON oso.service_id=adt.serviceId
  112. AND override_for='path' AND (
  113. ('2016-11-09' BETWEEN CAST(override_from AS DATE) AND CAST(override_to AS DATE))
  114. OR override_from IS NULL
  115. )
  116. INNER JOIN test.oprs_service_halts AS osh ON osh.halt_override_id=oso.override_id
  117. INNER JOIN abrs_new.abrs_service AS asr ON asr.Service_key= adt.serviceId
  118. WHERE asr.Travel_partner_key=485 AND adt.jdate='2016-11-09' AND adt.status='A' AND
  119. (adt.seatBookedType = 'Normal' OR adt.seatBookedType IS NULL) GROUP BY adt.serviceId,seatNo
  120. ) AS seatcnt GROUP BY seatcnt.serviceId
  121. ) AS resseatcnt ON resseatcnt.serviceId=totalseats.totalseats.service_key
  122.  
  123.  
  124. LEFT JOIN (
  125. SELECT bookseat.seat_num,COUNT(bookseat.seat_num) AS tempcount,bookseat.service_key,bookseat.source_halt_minutes ,
  126. bookseat.dest_halt_minutes,bookseat.override_id
  127. FROM (
  128. SELECT DISTINCT seat_num,adt.service_key,art.Source_Id,art.Destination_Id,oso.override_id,
  129. (SELECT halt_minutes FROM test.oprs_service_halts WHERE service_id=adt.service_key AND
  130. station_id=art.Source_Id AND halt_override_id=oso.override_id) AS source_halt_minutes,
  131. (SELECT halt_minutes FROM test.oprs_service_halts WHERE service_id=asr.service_key AND
  132. station_id=art.Destination_Id AND halt_override_id=oso.override_id) AS dest_halt_minutes
  133.  
  134. FROM abrs_reserved_tickets_det AS adt
  135. INNER JOIN abrs_reserved_tickets AS art ON art.Ticket_key=adt.Oprs_Ticket_key
  136. INNER JOIN test.oprs_service_overrides AS oso ON oso.service_id=adt.service_key
  137. AND override_for='path' AND (
  138. ('2016-11-09' BETWEEN CAST(override_from AS DATE) AND CAST(override_to AS DATE))
  139. OR override_from IS NULL
  140. )
  141. INNER JOIN test.oprs_service_halts AS osh ON osh.halt_override_id=oso.override_id
  142. INNER JOIN abrs_service AS asr ON asr.Service_key= adt.service_key
  143. WHERE asr.Travel_partner_key=485 AND adt.journey_date='2016-11-09' AND adt.status='A' AND
  144. (adt.seat_booked_type = 'Normal' OR adt.seat_booked_type IS NULL )
  145. ) AS bookseat
  146. GROUP BY bookseat.service_key,bookseat.source_halt_minutes ,bookseat.dest_halt_minutes ,seat_num
  147.  
  148. ) AS bookedseats ON bookedseats.service_key=totalseats.service_key AND
  149. (bookedseats.source_halt_minutes IN (SELECT halt_minutes FROM test.oprs_service_halts WHERE service_id=bookedseats.service_key
  150. AND halt_override_id=bookedseats.override_id AND halt_minutes < totalseats.dest_halt_minutes
  151. ) AND bookedseats.source_halt_minutes < totalseats.dest_halt_minutes
  152. AND
  153. bookedseats.dest_halt_minutes IN (SELECT halt_minutes FROM test.oprs_service_halts WHERE service_id=bookedseats.service_key
  154. AND halt_override_id=bookedseats.override_id AND halt_minutes > totalseats.source_halt_minutes
  155. ))
  156.  
  157. LEFT JOIN (
  158. SELECT bookseat.seatNo,COUNT(bookseat.seatNo) AS tempblockcount,bookseat.serviceId,bookseat.source_halt_minutes ,
  159. bookseat.dest_halt_minutes,bookseat.override_id
  160. FROM (
  161. SELECT DISTINCT seatNo,adt.serviceId,art.sourceid,art.destinationid,oso.override_id,
  162. (SELECT halt_minutes FROM test.oprs_service_halts WHERE service_id=adt.serviceId AND
  163. station_id=art.sourceid AND halt_override_id=oso.override_id) AS source_halt_minutes,
  164. (SELECT halt_minutes FROM test.oprs_service_halts WHERE service_id=adt.serviceId AND
  165. station_id=art.destinationid AND halt_override_id=oso.override_id) AS dest_halt_minutes
  166.  
  167. FROM test.oprs_reserved_tickets_det_tempapi AS adt
  168. INNER JOIN test.`oprs_reserved_tickets_tempapi` AS art ON art.Reference_key=adt.Reference_key
  169. INNER JOIN test.oprs_service_overrides AS oso ON oso.service_id=adt.serviceId
  170. AND override_for='path' AND (
  171. ('2016-11-09' BETWEEN CAST(override_from AS DATE) AND CAST(override_to AS DATE))
  172. OR override_from IS NULL
  173. )
  174. INNER JOIN test.oprs_service_halts AS osh ON osh.halt_override_id=oso.override_id
  175. INNER JOIN abrs_service AS asr ON asr.Service_key= adt.serviceId
  176. WHERE asr.Travel_partner_key=485 AND adt.jdate='2016-11-09' AND
  177. (adt.seatBookedType = 'Normal' OR adt.seatBookedType IS NULL )
  178. ) AS bookseat
  179. GROUP BY bookseat.serviceId,bookseat.source_halt_minutes ,bookseat.dest_halt_minutes,seatNo
  180. ) AS blockedseats ON blockedseats.serviceId=totalseats.service_key AND
  181. (blockedseats.source_halt_minutes IN (SELECT halt_minutes FROM test.oprs_service_halts WHERE service_id=blockedseats.serviceId
  182. AND halt_override_id=blockedseats.override_id AND halt_minutes < totalseats.dest_halt_minutes
  183. ) AND blockedseats.source_halt_minutes < totalseats.dest_halt_minutes
  184. AND
  185. blockedseats.dest_halt_minutes IN (SELECT halt_minutes FROM test.oprs_service_halts WHERE service_id=blockedseats.serviceId
  186. AND halt_override_id=blockedseats.override_id AND halt_minutes > totalseats.source_halt_minutes
  187. )
  188. )
  189.  
  190. LEFT JOIN
  191. (SELECT SUM(BIT_COUNT(seat_bit)) AS quota_seats,oqs.service_id FROM test.oprs_quota_service AS oqs
  192. INNER JOIN test.oprs_quota_transaction AS oqt ON oqt.sqid=oqs.sqid
  193. INNER JOIN test.oprs_quota_seat_allocation AS oqa ON oqa.tid=oqt.tid
  194. INNER JOIN test.oprs_service_overrides AS oso ON oso.override_id=oqs.override_id
  195. INNER JOIN abrs_service AS asr ON asr.service_key=oqs.service_id
  196. WHERE asr.Travel_partner_key=485 AND quota_type IN ('Agent','Employee') AND oso.override_for IN ('Quota','EmpQuota')AND
  197. (( oso.override_from >='2016-11-09' AND oso.override_to <='2016-11-09') OR ( override_from IS NULL)
  198. ) AND day_bit&8 > 0 GROUP BY oqs.service_id
  199. ) AS quota
  200. ON totalseats.service_key = quota.service_id
  201.  
  202. WHERE totalseats.dest_halt_minutes > totalseats.source_halt_minutes
  203.  
  204. GROUP BY totalseats.service_key,totalseats.source_halt_minutes,totalseats.dest_halt_minutes
Add Comment
Please, Sign In to add comment
Public Pastes
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!