Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT DISTINCT
- GR_GROUP.GROUP_TYPE_KEY,
- LC_COUNTY.NAME,
- GR_GROUP.GROUP_ID,
- GR_GROUP.NAME AS GROUP_NAME,
- LC_ADDRESS.STATE_KEY,
- GR_GROUP_STATUS.LABEL,
- GR_GROUP_STATUS.SORT_ORDER,
- LC_ADDRESS.ZIP,
- GR_GROUP.CREATE_DATE,
- MAX(CB_CONTACT.FIRST_NAME) AS PRODUCER_FIRST_NAME,
- MAX(CB_CONTACT.LAST_NAME) AS PRODUCER_LAST_NAME,
- MAX(CB_CONTACT.MIDDLE_NAME) AS PRODUCER_MIDDLE_NAME,
- MAX(CB_CONTACT2.FIRST_NAME) AS SALES_REP_FIRST_NAME,
- MAX(CB_CONTACT2.LAST_NAME) AS SALES_REP_LAST_NAME,
- MAX(CB_CONTACT2.MIDDLE_NAME) AS SALES_REP_MIDDLE_NAME,
- MAX(CB_CONTACT3.FIRST_NAME) AS ACCT_REP_FIRST_NAME,
- MAX(CB_CONTACT3.LAST_NAME) AS ACCT_REP_LAST_NAME,
- MAX(CB_CONTACT3.MIDDLE_NAME) AS ACCT_REP_MIDDLE_NAME
- FROM
- dbo.GR_GROUP GR_GROUP WITH (NOLOCK)
- INNER JOIN dbo.GR_EMPLOYER_GROUP GR_EMPLOYER_GROUP WITH (NOLOCK) ON GR_GROUP.GROUP_ID = GR_EMPLOYER_GROUP.GROUP_ID
- INNER JOIN dbo.EM_EMPLOYER EM_EMPLOYER WITH (NOLOCK) ON GR_EMPLOYER_GROUP.EMPLOYER_ID = EM_EMPLOYER.EMPLOYER_ID
- 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')
- LEFT OUTER JOIN dbo.LC_ADDRESS LC_ADDRESS WITH (NOLOCK) ON EM_EMPR_ADDR_DATA.ADDRESS_ID = LC_ADDRESS.ADDRESS_ID
- LEFT OUTER JOIN dbo.LC_COUNTY LC_COUNTY WITH (NOLOCK) ON LC_ADDRESS.COUNTY_ID = LC_COUNTY.COUNTY_ID
- INNER JOIN dbo.GR_GROUP_STATUS GR_GROUP_STATUS WITH (NOLOCK) ON GR_GROUP.GROUP_STATUS_ID = GR_GROUP_STATUS.GROUP_STATUS_ID
- INNER JOIN dbo.OW_GROUP_RESOURCE OW_GROUP_RESOURCE WITH (NOLOCK) ON GR_GROUP.GROUP_ID = OW_GROUP_RESOURCE.GROUP_ID
- INNER JOIN dbo.OW_RESOURCE OW_RESOURCE WITH (NOLOCK) ON OW_GROUP_RESOURCE.RESOURCE_ID = OW_RESOURCE.RESOURCE_ID
- INNER JOIN dbo.OW_RESOURCE_OWNER OW_RESOURCE_OWNER WITH (NOLOCK) ON OW_RESOURCE.RESOURCE_ID = OW_RESOURCE_OWNER.RESOURCE_ID
- 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')
- LEFT OUTER JOIN dbo.OW_OWNER OW_OWNER WITH (NOLOCK) ON OW_OWNER_ROLE.OWNER_ID = OW_OWNER.OWNER_ID
- LEFT OUTER JOIN dbo.CB_CONTACT CB_CONTACT WITH (NOLOCK) ON OW_OWNER.CONTACT_ID = CB_CONTACT.CONTACT_ID
- 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'))
- LEFT OUTER JOIN dbo.OW_OWNER OW_OWNER2 WITH (NOLOCK) ON OW_OWNER_ROLE2.OWNER_ID = OW_OWNER2.OWNER_ID
- LEFT OUTER JOIN dbo.CB_CONTACT CB_CONTACT2 WITH (NOLOCK) ON OW_OWNER2.CONTACT_ID = CB_CONTACT2.CONTACT_ID
- 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'))
- LEFT OUTER JOIN dbo.OW_OWNER OW_OWNER3 WITH (NOLOCK) ON OW_OWNER_ROLE3.OWNER_ID = OW_OWNER3.OWNER_ID
- LEFT OUTER JOIN dbo.CB_CONTACT CB_CONTACT3 WITH (NOLOCK) ON OW_OWNER3.CONTACT_ID = CB_CONTACT3.CONTACT_ID
- 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)))
- 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
- 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