Guest User

Untitled

a guest
Oct 16th, 2019
112
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT *
  2. FROM (
  3. SELECT d.ProgramCD,
  4. p.ApplicableTo,
  5. d.BreakByLevelID,
  6. d.BreakByLevelName,
  7. d.Revenue,
  8. d.Quantity,
  9. p.Descr
  10. FROM DMSAimTMKProgramBreakDownDetail AS d
  11. INNER JOIN
  12. DMSAimTMKTProgram AS p ON p.ProgramCD = d.ProgramCD
  13. INNER JOIN
  14. DMSAimCustomer AS c ON c.CustomerCD = 'C000023306' AND
  15. c.LocationCD = 'MAIN' AND
  16. d.Revenue <= c.Avg3M
  17. WHERE p.ApplicableTo <> 'U' AND
  18. d.ProgramCD IN (
  19. SELECT ProgramCD
  20. FROM (
  21. SELECT DISTINCT p.*,
  22. CASE WHEN rd.ProgramCD IS NULL THEN 0 ELSE 1 END IsRegister,
  23. CASE WHEN rd.VisitID = 'SR000608C00002330616a9145220000' THEN 1 ELSE 0 END IsReRegisterInSameVisit,
  24. CASE WHEN p.RegisterType = 'U' THEN 1 ELSE 0 END IsFromHO,
  25. CASE WHEN r.Status = 'R' THEN 1 ELSE 0 END IsReject
  26. FROM (
  27. SELECT *
  28. FROM DMSAimTMKTProgram
  29. WHERE UserName = 'SR000608' AND
  30. (ApplicableTo = 'A' OR
  31. (ApplicableTo = 'L' AND
  32. ProgramCD IN (
  33. SELECT ProgramCD
  34. FROM DMSAimTMKTProgramApplicableTo
  35. WHERE DMSAimTMKTProgramApplicableTo.UserName = 'SR000608' AND
  36. GroupCD IN (
  37. SELECT DMSAimTMKTCustomerGroupList.GroupCD
  38. FROM DMSAimTMKTCustomerGroupList
  39. WHERE DMSAimTMKTCustomerGroupList.UserName = 'SR000608' AND
  40. DMSAimTMKTCustomerGroupList.LocationCD = 'MAIN' AND
  41. DMSAimTMKTCustomerGroupList.CustomerCD = 'C000023306'
  42. )
  43. )
  44. ) OR
  45. (ApplicableTo = 'G' AND
  46. ProgramCD IN (
  47. SELECT ProgramCD
  48. FROM DMSAimTMKTProgramApplicableTo
  49. WHERE DMSAimTMKTProgramApplicableTo.UserName = 'SR000608' AND
  50. GroupCD IN (
  51. SELECT DMSAimTMKTCustomerGroupAttribute.GroupCD
  52. FROM DMSAimTMKTCustomerGroupAttribute
  53. WHERE (DMSAimTMKTCustomerGroupAttribute.Attribute0 = '1075' OR
  54. DMSAimTMKTCustomerGroupAttribute.Attribute0 = '0') AND
  55. (DMSAimTMKTCustomerGroupAttribute.Attribute1 = '' OR
  56. DMSAimTMKTCustomerGroupAttribute.Attribute1 = '0') AND
  57. (DMSAimTMKTCustomerGroupAttribute.Attribute2 = '1085' OR
  58. DMSAimTMKTCustomerGroupAttribute.Attribute2 = '0') AND
  59. (DMSAimTMKTCustomerGroupAttribute.Attribute3 = '1098' OR
  60. DMSAimTMKTCustomerGroupAttribute.Attribute3 = '0') AND
  61. (DMSAimTMKTCustomerGroupAttribute.Attribute4 = '1094' OR
  62. DMSAimTMKTCustomerGroupAttribute.Attribute4 = '0') AND
  63. (DMSAimTMKTCustomerGroupAttribute.Attribute5 = '' OR
  64. DMSAimTMKTCustomerGroupAttribute.Attribute5 = '0') AND
  65. (DMSAimTMKTCustomerGroupAttribute.Attribute6 = '1106' OR
  66. DMSAimTMKTCustomerGroupAttribute.Attribute6 = '0') AND
  67. (DMSAimTMKTCustomerGroupAttribute.Attribute7 = '1108' OR
  68. DMSAimTMKTCustomerGroupAttribute.Attribute7 = '0') AND
  69. (DMSAimTMKTCustomerGroupAttribute.Attribute8 = '1110' OR
  70. DMSAimTMKTCustomerGroupAttribute.Attribute8 = '0') AND
  71. (DMSAimTMKTCustomerGroupAttribute.Attribute9 = '' OR
  72. DMSAimTMKTCustomerGroupAttribute.Attribute9 = '0')
  73. )
  74. )
  75. ) OR
  76. (ApplicableTo = 'U' AND
  77. ProgramCD IN (
  78. SELECT p.ProgramCD
  79. FROM DMSAimTMKTProgram p
  80. JOIN
  81. DMSAimTMKTProgramRegister r ON r.ProgramCD = p.ProgramCD
  82. WHERE p.ApplicableTo = 'U' AND
  83. r.CustomerCD = 'C000023306' AND
  84. r.LocationCD = 'MAIN'
  85. )
  86. ) ) AND
  87. (ApplyTo = 'A' OR
  88. (ApplyTo = 'R' AND
  89. ProgramCD IN (
  90. SELECT ProgramCD
  91. FROM DMSAimTMKTProgramApplyTo
  92. WHERE DMSAimTMKTProgramApplyTo.UserName = 'SR000608' AND
  93. DMSAimTMKTProgramApplyTo.ApplyToCD = '0'
  94. )
  95. ) OR
  96. (ApplyTo = 'P' AND
  97. ProgramCD IN (
  98. SELECT ProgramCD
  99. FROM DMSAimTMKTProgramApplyTo
  100. WHERE DMSAimTMKTProgramApplyTo.UserName = 'SR000608' AND
  101. DMSAimTMKTProgramApplyTo.ApplyToCD = '113'
  102. )
  103. ) OR
  104. (ApplyTo = 'D' AND
  105. ProgramCD IN (
  106. SELECT ProgramCD
  107. FROM DMSAimTMKTProgramApplyTo
  108. WHERE DMSAimTMKTProgramApplyTo.UserName = 'SR000608' AND
  109. DMSAimTMKTProgramApplyTo.ApplyToCD = '1269'
  110. )
  111. ) OR
  112. (ApplyTo = 'T' AND
  113. ProgramCD IN (
  114. SELECT ProgramCD
  115. FROM DMSAimTMKTProgramApplyTo
  116. WHERE DMSAimTMKTProgramApplyTo.UserName = 'SR000608' AND
  117. DMSAimTMKTProgramApplyTo.ApplyToCD = '373'
  118. )
  119. ) )
  120. )
  121. p
  122. LEFT JOIN
  123. DMSAimTMKTProgramRegister r ON p.ProgramCD = r.ProgramCD AND
  124. r.CustomerCD = 'C000023306' AND
  125. r.LocationCD = 'MAIN'
  126. LEFT JOIN
  127. DMSAimTMKTProgramRegistered rd ON p.ProgramCD = rd.ProgramCD AND
  128. rd.CustomerCD = 'C000023306' AND
  129. rd.LocationCD = 'MAIN'
  130. LEFT JOIN
  131. DMSAimTMKTReason ra ON r.ReasonID = ra.ReasonID
  132. WHERE (p.RegisterType = 'P' OR
  133. (p.RegisterType = 'U' AND
  134. r.IsRegister = 0) ) AND
  135. (r.ProgramCD IS NULL OR
  136. r.Status = 'U' OR
  137. r.IsRegister = 0 OR
  138. (r.IsRegister = 1 AND
  139. r.Status = 'R') ) AND
  140. DATE(p.StartRegisterDate) <= '2019-10-16' AND
  141. DATE(p.EndRegisterDate) >= '2019-10-16'
  142. )
  143. )
  144. UNION
  145. SELECT d.ProgramCD,
  146. p.ApplicableTo,
  147. d.BreakByLevelID,
  148. d.BreakByLevelName,
  149. r.RegisterAmt AS Revenue,
  150. d.Quantity,
  151. p.Descr
  152. FROM DMSAimTMKProgramBreakDownDetail AS d
  153. INNER JOIN
  154. DMSAimTMKTProgram AS p ON p.ProgramCD = d.ProgramCD
  155. INNER JOIN
  156. DMSAimTMKTProgramRegister AS r ON r.CustomerCD = 'C000023306' AND
  157. r.LocationCD = 'MAIN' AND
  158. r.LevelID = d.BreakByLevelID AND
  159. r.ProgramCD = p.ProgramCD
  160. WHERE d.ProgramCD IN (
  161. SELECT ProgramCD
  162. FROM (
  163. SELECT DISTINCT p.*,
  164. CASE WHEN rd.ProgramCD IS NULL THEN 0 ELSE 1 END IsRegister,
  165. CASE WHEN rd.VisitID = 'SR000608C00002330616a9145220000' THEN 1 ELSE 0 END IsReRegisterInSameVisit,
  166. CASE WHEN p.RegisterType = 'U' THEN 1 ELSE 0 END IsFromHO,
  167. CASE WHEN r.Status = 'R' THEN 1 ELSE 0 END IsReject
  168. FROM (
  169. SELECT *
  170. FROM DMSAimTMKTProgram
  171. WHERE UserName = 'SR000608' AND
  172. (ApplicableTo = 'A' OR
  173. (ApplicableTo = 'L' AND
  174. ProgramCD IN (
  175. SELECT ProgramCD
  176. FROM DMSAimTMKTProgramApplicableTo
  177. WHERE DMSAimTMKTProgramApplicableTo.UserName = 'SR000608' AND
  178. GroupCD IN (
  179. SELECT DMSAimTMKTCustomerGroupList.GroupCD
  180. FROM DMSAimTMKTCustomerGroupList
  181. WHERE DMSAimTMKTCustomerGroupList.UserName = 'SR000608' AND
  182. DMSAimTMKTCustomerGroupList.LocationCD = 'MAIN' AND
  183. DMSAimTMKTCustomerGroupList.CustomerCD = 'C000023306'
  184. )
  185. )
  186. ) OR
  187. (ApplicableTo = 'G' AND
  188. ProgramCD IN (
  189. SELECT ProgramCD
  190. FROM DMSAimTMKTProgramApplicableTo
  191. WHERE DMSAimTMKTProgramApplicableTo.UserName = 'SR000608' AND
  192. GroupCD IN (
  193. SELECT DMSAimTMKTCustomerGroupAttribute.GroupCD
  194. FROM DMSAimTMKTCustomerGroupAttribute
  195. WHERE (DMSAimTMKTCustomerGroupAttribute.Attribute0 = '1075' OR
  196. DMSAimTMKTCustomerGroupAttribute.Attribute0 = '0') AND
  197. (DMSAimTMKTCustomerGroupAttribute.Attribute1 = '' OR
  198. DMSAimTMKTCustomerGroupAttribute.Attribute1 = '0') AND
  199. (DMSAimTMKTCustomerGroupAttribute.Attribute2 = '1085' OR
  200. DMSAimTMKTCustomerGroupAttribute.Attribute2 = '0') AND
  201. (DMSAimTMKTCustomerGroupAttribute.Attribute3 = '1098' OR
  202. DMSAimTMKTCustomerGroupAttribute.Attribute3 = '0') AND
  203. (DMSAimTMKTCustomerGroupAttribute.Attribute4 = '1094' OR
  204. DMSAimTMKTCustomerGroupAttribute.Attribute4 = '0') AND
  205. (DMSAimTMKTCustomerGroupAttribute.Attribute5 = '' OR
  206. DMSAimTMKTCustomerGroupAttribute.Attribute5 = '0') AND
  207. (DMSAimTMKTCustomerGroupAttribute.Attribute6 = '1106' OR
  208. DMSAimTMKTCustomerGroupAttribute.Attribute6 = '0') AND
  209. (DMSAimTMKTCustomerGroupAttribute.Attribute7 = '1108' OR
  210. DMSAimTMKTCustomerGroupAttribute.Attribute7 = '0') AND
  211. (DMSAimTMKTCustomerGroupAttribute.Attribute8 = '1110' OR
  212. DMSAimTMKTCustomerGroupAttribute.Attribute8 = '0') AND
  213. (DMSAimTMKTCustomerGroupAttribute.Attribute9 = '' OR
  214. DMSAimTMKTCustomerGroupAttribute.Attribute9 = '0')
  215. )
  216. )
  217. ) OR
  218. (ApplicableTo = 'U' AND
  219. ProgramCD IN (
  220. SELECT p.ProgramCD
  221. FROM DMSAimTMKTProgram p
  222. JOIN
  223. DMSAimTMKTProgramRegister r ON r.ProgramCD = p.ProgramCD
  224. WHERE p.ApplicableTo = 'U' AND
  225. r.CustomerCD = 'C000023306' AND
  226. r.LocationCD = 'MAIN'
  227. )
  228. ) ) AND
  229. (ApplyTo = 'A' OR
  230. (ApplyTo = 'R' AND
  231. ProgramCD IN (
  232. SELECT ProgramCD
  233. FROM DMSAimTMKTProgramApplyTo
  234. WHERE DMSAimTMKTProgramApplyTo.UserName = 'SR000608' AND
  235. DMSAimTMKTProgramApplyTo.ApplyToCD = '0'
  236. )
  237. ) OR
  238. (ApplyTo = 'P' AND
  239. ProgramCD IN (
  240. SELECT ProgramCD
  241. FROM DMSAimTMKTProgramApplyTo
  242. WHERE DMSAimTMKTProgramApplyTo.UserName = 'SR000608' AND
  243. DMSAimTMKTProgramApplyTo.ApplyToCD = '113'
  244. )
  245. ) OR
  246. (ApplyTo = 'D' AND
  247. ProgramCD IN (
  248. SELECT ProgramCD
  249. FROM DMSAimTMKTProgramApplyTo
  250. WHERE DMSAimTMKTProgramApplyTo.UserName = 'SR000608' AND
  251. DMSAimTMKTProgramApplyTo.ApplyToCD = '1269'
  252. )
  253. ) OR
  254. (ApplyTo = 'T' AND
  255. ProgramCD IN (
  256. SELECT ProgramCD
  257. FROM DMSAimTMKTProgramApplyTo
  258. WHERE DMSAimTMKTProgramApplyTo.UserName = 'SR000608' AND
  259. DMSAimTMKTProgramApplyTo.ApplyToCD = '373'
  260. )
  261. ) )
  262. )
  263. p
  264. LEFT JOIN
  265. DMSAimTMKTProgramRegister r ON p.ProgramCD = r.ProgramCD AND
  266. r.CustomerCD = 'C000023306' AND
  267. r.LocationCD = 'MAIN'
  268. LEFT JOIN
  269. DMSAimTMKTProgramRegistered rd ON p.ProgramCD = rd.ProgramCD AND
  270. rd.CustomerCD = 'C000023306' AND
  271. rd.LocationCD = 'MAIN'
  272. LEFT JOIN
  273. DMSAimTMKTReason ra ON r.ReasonID = ra.ReasonID
  274. WHERE (p.RegisterType = 'P' OR
  275. (p.RegisterType = 'U' AND
  276. r.IsRegister = 0) ) AND
  277. (r.ProgramCD IS NULL OR
  278. r.Status = 'U' OR
  279. r.IsRegister = 0 OR
  280. (r.IsRegister = 1 AND
  281. r.Status = 'R') ) AND
  282. DATE(p.StartRegisterDate) <= '2019-10-16' AND
  283. DATE(p.EndRegisterDate) >= '2019-10-16'
  284. )
  285. )
  286. )
  287. t
  288. ORDER BY ProgramCD,
  289. Revenue DESC,
  290. Quantity DESC;
RAW Paste Data