API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Oct 16th, 2019
93
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 8.21 KB | None | 0 0
raw download clone embed print report
  1. SELECT * FROM (
  2.  
  3.  
  4. SELECT d.ProgramCD, p.ApplicableTo, d.BreakByLevelID, d.BreakByLevelName, d.Revenue, d.Quantity, p.Descr
  5. FROM DMSAimTMKProgramBreakDownDetail AS d
  6. INNER JOIN DMSAimTMKTProgram AS p ON p.ProgramCD = d.ProgramCD
  7. INNER JOIN DMSAimCustomer AS c ON c.CustomerCD = 'C000023306' AND c.LocationCD = 'MAIN' AND d.Revenue <= c.Avg3M
  8. WHERE p.ApplicableTo <> 'U'
  9. AND d.ProgramCD IN (SELECT ProgramCD FROM (SELECT DISTINCT p.*, CASE WHEN rd.ProgramCD IS NULL THEN 0 ELSE 1 END IsRegister, CASE WHEN rd.VisitID = 'SR000608C00002330616a9145220000' THEN 1 ELSE 0 END IsReRegisterInSameVisit, CASE WHEN p.RegisterType = 'U' THEN 1 ELSE 0 END IsFromHO, CASE WHEN r.Status = 'R' THEN 1 ELSE 0 END IsReject FROM (SELECT * FROM DMSAimTMKTProgram WHERE UserName= 'SR000608'AND ( ApplicableTo = 'A' OR ( ApplicableTo = 'L' AND ProgramCD IN ( SELECT ProgramCD FROM DMSAimTMKTProgramApplicableTo WHERE DMSAimTMKTProgramApplicableTo.UserName='SR000608' AND GroupCD IN ( SELECT DMSAimTMKTCustomerGroupList.GroupCD FROM DMSAimTMKTCustomerGroupList WHERE DMSAimTMKTCustomerGroupList.UserName= 'SR000608'AND DMSAimTMKTCustomerGroupList.LocationCD= 'MAIN'AND DMSAimTMKTCustomerGroupList.CustomerCD= 'C000023306' ))) OR ( ApplicableTo = 'G' AND ProgramCD IN ( SELECT ProgramCD FROM DMSAimTMKTProgramApplicableTo WHERE DMSAimTMKTProgramApplicableTo.UserName='SR000608' AND GroupCD IN ( SELECT DMSAimTMKTCustomerGroupAttribute.GroupCD FROM DMSAimTMKTCustomerGroupAttribute WHERE (DMSAimTMKTCustomerGroupAttribute.Attribute0= '1075' OR DMSAimTMKTCustomerGroupAttribute.Attribute0= '0')AND (DMSAimTMKTCustomerGroupAttribute.Attribute1= '' OR DMSAimTMKTCustomerGroupAttribute.Attribute1= '0')AND (DMSAimTMKTCustomerGroupAttribute.Attribute2= '1085' OR DMSAimTMKTCustomerGroupAttribute.Attribute2= '0')AND (DMSAimTMKTCustomerGroupAttribute.Attribute3= '1098' OR DMSAimTMKTCustomerGroupAttribute.Attribute3= '0')AND (DMSAimTMKTCustomerGroupAttribute.Attribute4= '1094' OR DMSAimTMKTCustomerGroupAttribute.Attribute4= '0')AND (DMSAimTMKTCustomerGroupAttribute.Attribute5= '' OR DMSAimTMKTCustomerGroupAttribute.Attribute5= '0')AND (DMSAimTMKTCustomerGroupAttribute.Attribute6= '1106' OR DMSAimTMKTCustomerGroupAttribute.Attribute6= '0')AND (DMSAimTMKTCustomerGroupAttribute.Attribute7= '1108' OR DMSAimTMKTCustomerGroupAttribute.Attribute7= '0')AND (DMSAimTMKTCustomerGroupAttribute.Attribute8= '1110' OR DMSAimTMKTCustomerGroupAttribute.Attribute8= '0')AND (DMSAimTMKTCustomerGroupAttribute.Attribute9= '' OR DMSAimTMKTCustomerGroupAttribute.Attribute9= '0') ))) OR ( ApplicableTo = 'U' AND ProgramCD IN ( SELECT p.ProgramCD FROM DMSAimTMKTProgram p JOIN DMSAimTMKTProgramRegister r ON r.ProgramCD = p.ProgramCD WHERE p.ApplicableTo = 'U' AND r.CustomerCD = 'C000023306' AND r.LocationCD = 'MAIN'))) AND ( ApplyTo = 'A' OR ( ApplyTo = 'R' AND ProgramCD IN ( SELECT ProgramCD FROM DMSAimTMKTProgramApplyTo WHERE DMSAimTMKTProgramApplyTo.UserName='SR000608' AND DMSAimTMKTProgramApplyTo.ApplyToCD = '0')) OR ( ApplyTo = 'P' AND ProgramCD IN ( SELECT ProgramCD FROM DMSAimTMKTProgramApplyTo WHERE DMSAimTMKTProgramApplyTo.UserName='SR000608' AND DMSAimTMKTProgramApplyTo.ApplyToCD = '113')) OR ( ApplyTo = 'D' AND ProgramCD IN ( SELECT ProgramCD FROM DMSAimTMKTProgramApplyTo WHERE DMSAimTMKTProgramApplyTo.UserName='SR000608' AND DMSAimTMKTProgramApplyTo.ApplyToCD = '1269')) OR ( ApplyTo = 'T' AND ProgramCD IN ( SELECT ProgramCD FROM DMSAimTMKTProgramApplyTo WHERE DMSAimTMKTProgramApplyTo.UserName='SR000608' AND DMSAimTMKTProgramApplyTo.ApplyToCD = '373')) )) p LEFT JOIN DMSAimTMKTProgramRegister r ON p.ProgramCD = r.ProgramCD AND r.CustomerCD = 'C000023306' AND r.LocationCD = 'MAIN' LEFT JOIN DMSAimTMKTProgramRegistered rd ON p.ProgramCD = rd.ProgramCD AND rd.CustomerCD = 'C000023306' AND rd.LocationCD = 'MAIN' LEFT JOIN DMSAimTMKTReason ra ON r.ReasonID = ra.ReasonID WHERE (p.RegisterType = 'P' or (p.RegisterType = 'U' and r.IsRegister = 0)) AND (r.ProgramCD IS NULL OR r.Status = 'U' OR r.IsRegister = 0 OR (r.IsRegister = 1 AND r.Status = 'R')) AND DATE(p.StartRegisterDate) <= '2019-10-16' AND DATE(p.EndRegisterDate) >= '2019-10-16'))
  10. UNION
  11. SELECT d.ProgramCD, p.ApplicableTo, d.BreakByLevelID, d.BreakByLevelName, r.RegisterAmt as Revenue, d.Quantity, p.Descr
  12. FROM DMSAimTMKProgramBreakDownDetail AS d
  13. INNER JOIN DMSAimTMKTProgram AS p ON p.ProgramCD = d.ProgramCD
  14. INNER JOIN DMSAimTMKTProgramRegister AS r ON r.CustomerCD = 'C000023306' AND r.LocationCD = 'MAIN' AND r.LevelID = d.BreakByLevelID and r.ProgramCD = p.ProgramCD
  15. WHERE d.ProgramCD IN (SELECT ProgramCD FROM (SELECT DISTINCT p.*, CASE WHEN rd.ProgramCD IS NULL THEN 0 ELSE 1 END IsRegister,
  16. CASE WHEN rd.VisitID = 'SR000608C00002330616a9145220000' THEN 1 ELSE 0 END IsReRegisterInSameVisit,
  17. CASE WHEN p.RegisterType = 'U' THEN 1 ELSE 0 END IsFromHO, CASE WHEN r.Status = 'R' THEN 1 ELSE 0 END IsReject
  18. FROM (SELECT * FROM DMSAimTMKTProgram WHERE UserName= 'SR000608'AND ( ApplicableTo = 'A' OR ( ApplicableTo = 'L' AND ProgramCD IN
  19. ( SELECT ProgramCD FROM DMSAimTMKTProgramApplicableTo WHERE DMSAimTMKTProgramApplicableTo.UserName='SR000608' AND GroupCD IN ( SELECT DMSAimTMKTCustomerGroupList.GroupCD FROM DMSAimTMKTCustomerGroupList WHERE DMSAimTMKTCustomerGroupList.UserName= 'SR000608'AND DMSAimTMKTCustomerGroupList.LocationCD= 'MAIN'AND DMSAimTMKTCustomerGroupList.CustomerCD= 'C000023306' ))) OR ( ApplicableTo = 'G' AND ProgramCD IN ( SELECT ProgramCD FROM DMSAimTMKTProgramApplicableTo WHERE DMSAimTMKTProgramApplicableTo.UserName='SR000608' AND GroupCD IN ( SELECT DMSAimTMKTCustomerGroupAttribute.GroupCD FROM DMSAimTMKTCustomerGroupAttribute WHERE (DMSAimTMKTCustomerGroupAttribute.Attribute0= '1075' OR DMSAimTMKTCustomerGroupAttribute.Attribute0= '0')AND (DMSAimTMKTCustomerGroupAttribute.Attribute1= '' OR DMSAimTMKTCustomerGroupAttribute.Attribute1= '0')AND (DMSAimTMKTCustomerGroupAttribute.Attribute2= '1085' OR DMSAimTMKTCustomerGroupAttribute.Attribute2= '0')AND (DMSAimTMKTCustomerGroupAttribute.Attribute3= '1098' OR DMSAimTMKTCustomerGroupAttribute.Attribute3= '0')AND (DMSAimTMKTCustomerGroupAttribute.Attribute4= '1094' OR DMSAimTMKTCustomerGroupAttribute.Attribute4= '0')AND (DMSAimTMKTCustomerGroupAttribute.Attribute5= '' OR DMSAimTMKTCustomerGroupAttribute.Attribute5= '0')AND (DMSAimTMKTCustomerGroupAttribute.Attribute6= '1106' OR DMSAimTMKTCustomerGroupAttribute.Attribute6= '0')AND (DMSAimTMKTCustomerGroupAttribute.Attribute7= '1108' OR DMSAimTMKTCustomerGroupAttribute.Attribute7= '0')AND (DMSAimTMKTCustomerGroupAttribute.Attribute8= '1110' OR DMSAimTMKTCustomerGroupAttribute.Attribute8= '0')AND (DMSAimTMKTCustomerGroupAttribute.Attribute9= '' OR DMSAimTMKTCustomerGroupAttribute.Attribute9= '0') ))) OR ( ApplicableTo = 'U' AND ProgramCD IN ( SELECT p.ProgramCD FROM DMSAimTMKTProgram p JOIN DMSAimTMKTProgramRegister r ON r.ProgramCD = p.ProgramCD WHERE p.ApplicableTo = 'U' AND r.CustomerCD = 'C000023306' AND r.LocationCD = 'MAIN'))) AND ( ApplyTo = 'A' OR ( ApplyTo = 'R' AND ProgramCD IN ( SELECT ProgramCD FROM DMSAimTMKTProgramApplyTo WHERE DMSAimTMKTProgramApplyTo.UserName='SR000608' AND DMSAimTMKTProgramApplyTo.ApplyToCD = '0')) OR ( ApplyTo = 'P' AND ProgramCD IN ( SELECT ProgramCD FROM DMSAimTMKTProgramApplyTo WHERE DMSAimTMKTProgramApplyTo.UserName='SR000608' AND DMSAimTMKTProgramApplyTo.ApplyToCD = '113')) OR ( ApplyTo = 'D' AND ProgramCD IN ( SELECT ProgramCD FROM DMSAimTMKTProgramApplyTo WHERE DMSAimTMKTProgramApplyTo.UserName='SR000608' AND DMSAimTMKTProgramApplyTo.ApplyToCD = '1269')) OR ( ApplyTo = 'T' AND ProgramCD IN ( SELECT ProgramCD FROM DMSAimTMKTProgramApplyTo WHERE DMSAimTMKTProgramApplyTo.UserName='SR000608' AND DMSAimTMKTProgramApplyTo.ApplyToCD = '373')) )) p LEFT JOIN DMSAimTMKTProgramRegister r ON p.ProgramCD = r.ProgramCD AND r.CustomerCD = 'C000023306' AND r.LocationCD = 'MAIN' LEFT JOIN DMSAimTMKTProgramRegistered rd ON p.ProgramCD = rd.ProgramCD AND rd.CustomerCD = 'C000023306' AND rd.LocationCD = 'MAIN' LEFT JOIN DMSAimTMKTReason ra ON r.ReasonID = ra.ReasonID WHERE (p.RegisterType = 'P' or (p.RegisterType = 'U' and r.IsRegister = 0)) AND (r.ProgramCD IS NULL OR r.Status = 'U' OR r.IsRegister = 0 OR (r.IsRegister = 1 AND r.Status = 'R')) AND DATE(p.StartRegisterDate) <= '2019-10-16' AND DATE(p.EndRegisterDate) >= '2019-10-16'))
  20. ) t ORDER BY ProgramCD, Revenue DESC, Quantity DESC
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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!