daily pastebin goal
32%
SHARE
TWEET

Untitled

a guest Aug 10th, 2018 59 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  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
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