Advertisement
Guest User

Untitled

a guest
Aug 10th, 2018
77
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 3.67 KB | None | 0 0
  1. SELECT DISTINCT
  2.     GR_GROUP.GROUP_TYPE_KEY,
  3.     LC_COUNTY.NAME,
  4.     GR_GROUP.GROUP_ID,
  5.     GR_GROUP.NAME AS GROUP_NAME,
  6.     LC_ADDRESS.STATE_KEY,
  7.     GR_GROUP_STATUS.LABEL,
  8.     GR_GROUP_STATUS.SORT_ORDER,
  9.     LC_ADDRESS.ZIP,
  10.     GR_GROUP.CREATE_DATE,
  11.     MAX(CB_CONTACT.FIRST_NAME) AS PRODUCER_FIRST_NAME,
  12.     MAX(CB_CONTACT.LAST_NAME) AS PRODUCER_LAST_NAME,
  13.     MAX(CB_CONTACT.MIDDLE_NAME) AS PRODUCER_MIDDLE_NAME,
  14.     MAX(CB_CONTACT2.FIRST_NAME) AS SALES_REP_FIRST_NAME,
  15.     MAX(CB_CONTACT2.LAST_NAME) AS SALES_REP_LAST_NAME,
  16.     MAX(CB_CONTACT2.MIDDLE_NAME) AS SALES_REP_MIDDLE_NAME,
  17.     MAX(CB_CONTACT3.FIRST_NAME) AS ACCT_REP_FIRST_NAME,
  18.     MAX(CB_CONTACT3.LAST_NAME) AS ACCT_REP_LAST_NAME,
  19.     MAX(CB_CONTACT3.MIDDLE_NAME) AS ACCT_REP_MIDDLE_NAME
  20.  FROM
  21.     dbo.GR_GROUP GR_GROUP WITH (NOLOCK)
  22.   INNER JOIN  dbo.GR_EMPLOYER_GROUP GR_EMPLOYER_GROUP WITH (NOLOCK) ON GR_GROUP.GROUP_ID = GR_EMPLOYER_GROUP.GROUP_ID
  23.   INNER JOIN  dbo.EM_EMPLOYER EM_EMPLOYER WITH (NOLOCK) ON GR_EMPLOYER_GROUP.EMPLOYER_ID = EM_EMPLOYER.EMPLOYER_ID
  24.   LEFT OUTER JOIN  dbo.EM_EMPR_ADDR_DATA EM_EMPR_ADDR_DATA WITH (NOLOCK) ON (EM_EMPLOYER.EMPLOYER_ID = EM_EMPR_ADDR_DATA.EMPLOYER_ID) AND (EM_EMPR_ADDR_DATA.IS_PRIMARY = '1')
  25.   LEFT OUTER JOIN  dbo.LC_ADDRESS LC_ADDRESS WITH (NOLOCK) ON EM_EMPR_ADDR_DATA.ADDRESS_ID = LC_ADDRESS.ADDRESS_ID
  26.   LEFT OUTER JOIN  dbo.LC_COUNTY LC_COUNTY WITH (NOLOCK) ON LC_ADDRESS.COUNTY_ID = LC_COUNTY.COUNTY_ID
  27.   INNER JOIN  dbo.GR_GROUP_STATUS GR_GROUP_STATUS WITH (NOLOCK) ON GR_GROUP.GROUP_STATUS_ID = GR_GROUP_STATUS.GROUP_STATUS_ID
  28.  
  29.   INNER JOIN  dbo.OW_GROUP_RESOURCE OW_GROUP_RESOURCE WITH (NOLOCK) ON GR_GROUP.GROUP_ID = OW_GROUP_RESOURCE.GROUP_ID
  30.   INNER JOIN  dbo.OW_RESOURCE OW_RESOURCE WITH (NOLOCK) ON OW_GROUP_RESOURCE.RESOURCE_ID = OW_RESOURCE.RESOURCE_ID
  31.   INNER JOIN  dbo.OW_RESOURCE_OWNER OW_RESOURCE_OWNER WITH (NOLOCK) ON OW_RESOURCE.RESOURCE_ID = OW_RESOURCE_OWNER.RESOURCE_ID
  32.  
  33.   LEFT OUTER JOIN  dbo.OW_OWNER_ROLE OW_OWNER_ROLE WITH (NOLOCK) ON (OW_RESOURCE_OWNER.OWNER_ROLE_ID = OW_OWNER_ROLE.OWNER_ROLE_ID) AND (OW_OWNER_ROLE.ROLE_KEY = 'broker')
  34.   LEFT OUTER JOIN  dbo.OW_OWNER OW_OWNER WITH (NOLOCK) ON OW_OWNER_ROLE.OWNER_ID = OW_OWNER.OWNER_ID
  35.   LEFT OUTER JOIN  dbo.CB_CONTACT CB_CONTACT WITH (NOLOCK) ON OW_OWNER.CONTACT_ID = CB_CONTACT.CONTACT_ID
  36.  
  37.   LEFT OUTER JOIN  dbo.OW_OWNER_ROLE OW_OWNER_ROLE2 WITH (NOLOCK) ON (OW_RESOURCE_OWNER.OWNER_ROLE_ID = OW_OWNER_ROLE2.OWNER_ROLE_ID) AND ((OW_OWNER_ROLE2.ROLE_KEY = 'internaluser') OR (OW_OWNER_ROLE2.ROLE_KEY = 'salesmanager'))
  38.   LEFT OUTER JOIN  dbo.OW_OWNER OW_OWNER2 WITH (NOLOCK) ON OW_OWNER_ROLE2.OWNER_ID = OW_OWNER2.OWNER_ID
  39.   LEFT OUTER JOIN  dbo.CB_CONTACT CB_CONTACT2 WITH (NOLOCK) ON OW_OWNER2.CONTACT_ID = CB_CONTACT2.CONTACT_ID
  40.  
  41.   LEFT OUTER JOIN  dbo.OW_OWNER_ROLE OW_OWNER_ROLE3 WITH (NOLOCK) ON (OW_RESOURCE_OWNER.OWNER_ROLE_ID = OW_OWNER_ROLE3.OWNER_ROLE_ID) AND ((OW_OWNER_ROLE3.ROLE_KEY = 'accountrep') OR (OW_OWNER_ROLE3.ROLE_KEY = 'accountmanager'))
  42.   LEFT OUTER JOIN  dbo.OW_OWNER OW_OWNER3 WITH (NOLOCK) ON OW_OWNER_ROLE3.OWNER_ID = OW_OWNER3.OWNER_ID
  43.   LEFT OUTER JOIN  dbo.CB_CONTACT CB_CONTACT3 WITH (NOLOCK) ON OW_OWNER3.CONTACT_ID = CB_CONTACT3.CONTACT_ID
  44.  
  45.     WHERE (((GR_GROUP.NAME LIKE 'Case1533816140898') AND (GR_GROUP.GROUP_TYPE_KEY IN ('SG','IF','LG'))) AND (UPPER(GR_GROUP.GROUP_TYPE_KEY) <> 'CASE1533816140898')) AND (NOT (GR_GROUP.GROUP_ID IN (SELECT DISTINCT QT_QUOTE.GROUP_ID FROM dbo.QT_QUOTE QT_QUOTE WHERE QT_QUOTE.QUOTE_ID IS NULL)))
  46.         GROUP BY LC_COUNTY.NAME, GR_GROUP.GROUP_ID, GR_GROUP.GROUP_TYPE_KEY, GR_GROUP.NAME, LC_ADDRESS.STATE_KEY, GR_GROUP_STATUS.LABEL, GR_GROUP_STATUS.SORT_ORDER, LC_ADDRESS.ZIP, GR_GROUP.CREATE_DATE
  47.         ORDER BY GR_GROUP.NAME ASC, PRODUCER_LAST_NAME ASC, SALES_REP_LAST_NAME ASC
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement