Ok. I understand. The query is a bit long, but here goes.
IF OBJECT_ID('TEMPDB..#TMP9O') IS NOT NULL DROP TABLE #TMP9O
IF OBJECT_ID('TEMPDB..#TMP10O') IS NOT NULL DROP TABLE #TMP10O
IF OBJECT_ID('TEMPDB..#TMP11O') IS NOT NULL DROP TABLE #TMP11O
--COMPLETES SERVICE ORDERS AND SRO'S--
SELECT DISTINCT
'OCR' AS TABLEIND
,C.[PRIN_OCR] AS PRIN
,C.[AGNT_OCR] AS AGENT
,C.[COMPL_DTE_OCR] AS COMPLETE_DTE
,C.[CREATE_DTE_OCR] AS CREATE_DTE
,C.[CREATE_OP_ID_OCR] AS OPID
,C.[CREATE_TERM_ID_OCR] AS TERMID
,C.[HSE_KEY_OCR] AS HSEKEY
,C.[CUST_ACCT_NO_OCR] AS CUSTACCTNO
,C.[ORDER_NO_OCR] AS ORDNO
,CASE WHEN C.[ORD_CLASS_OCR]='M' THEN 'SRO'
WHEN C.[ORD_CLASS_OCR]='S' THEN 'SERVICEORD'
WHEN C.[ORD_CLASS_OCR]='T' THEN 'TC'
ELSE ' ' END AS ORDCLASS
,CASE WHEN C.[ORD_STAT_OCR]='C' THEN 'CLOSED'
WHEN C.[ORD_STAT_OCR]='I' THEN 'INPROGRESS'
WHEN C.[ORD_STAT_OCR]='O' THEN 'OPEN'
WHEN C.[ORD_STAT_OCR]='X' THEN 'CANCELLED'
ELSE ' ' END AS ORDSTAT
,C.[SALESREP_OCR] AS SALESREP
,ISNULL(CC2.DESCR_CTD,' ') AS OrderRsn1
,ISNULL(CC3.DESCR_CTD,' ') AS OrderRsn2
,C.BILL_STOP_DTE_OCR AS BillStopDate
INTO #TMP9O
FROM [Vantage].[dbo].[OCR_ORDER_COMP] C WITH(NOLOCK) LEFT JOIN VANTAGE.DBO.CTD_DISPLAY CC2 (NOLOCK)
ON CC2.PRIN_CTD = C.PRIN_OCR AND
CC2.CDE_VALUE_CTD = SUBSTRING(C.ORD_RSN_OCR,1,2) AND
CC2.SPA_FLG_CTD = 'P' AND
CC2.CDE_TBL_NO_CTD = '03'
LEFT JOIN VANTAGE.DBO.CTD_DISPLAY CC3 (NOLOCK)
ON CC3.PRIN_CTD = C.PRIN_OCR AND
CC3.CDE_VALUE_CTD = SUBSTRING(C.ORD_RSN_OCR,3,2) AND
CC3.SPA_FLG_CTD = 'P' AND
CC3.CDE_TBL_NO_CTD = '03'
WHERE C.PRIN_OCR IN (7500,7400,6000) AND
C.[CREATE_DTE_OCR] BETWEEN @STARTDATE AND @ENDDATE
SELECT DISTINCT A.*,O.SUB_ACCT_NO_OHI AS SubAcctNo
,SBB.RES_NAME_SBB AS CustomerName
,SBB.VIP_FLG_SBB AS VipFlag
,SBB.HOME_PHONE_SBB AS ResPhone
,SBB.BUS_PHONE_SBB AS BusPhone
INTO #TMP10O
FROM #TMP9O A WITH(NOLOCK) INNER JOIN VANTAGE.DBO.OHI_HIST_ITEM O (NOLOCK)
ON A.PRIN = O.PRIN_OHI AND
A.AGENT = O.AGNT_OHI AND
A.ORDNO = O.ORDER_NO_OHI
LEFT JOIN VANTAGE.DBO.SBB_BASE SBB (NOLOCK)
ON O.PRIN_OHI = SBB.PRIN_SBB AND
O.AGNT_OHI = SBB.AGNT_SBB AND
O.SUB_ACCT_NO_OHI = SBB.SUB_ACCT_NO_SBB
SELECT DISTINCT CONVERT(VARCHAR(8), GETDATE(), 1) AS ReportDate,
A.Prin,
A.Agent,
A.SubAcctNo,
A.CUSTACCTNO AS CustAcctNo,
A.ORDNO AS OrderNo,
A.HSEKEY AS HseKey,
A.CustomerName,
A.VipFlag,
CT2.DESCR_CTD AS VipFlagDesc,
A.ResPhone,
A.BusPhone,
H.ADDR1_HSE AS ServiceAddress,
H.RES_ADDR_2_HSE AS ServiceAptAndNumber,H.RES_CITY_HSE AS ServiceCity,
H.RES_STATE_HSE AS ServiceState,H.POSTAL_CDE_HSE AS ServiceZipAndZip4,
ISNULL(CUS.[E_MAIL_ADDR_CUS],' ') AS EmailAddr,
ISNULL(CONVERT(VARCHAR(10),A.CREATE_DTE,120),' ') as CreateDate,
ISNULL(CONVERT(VARCHAR(10),J.SCHED_DTE_OJB,120),' ') as ScheduleDate,
ISNULL(CONVERT(VARCHAR(10),A.COMPLETE_DTE,120),' ') as CompleteDate,
ISNULL(CONVERT(VARCHAR(10),A.BillStopDate,120),' ') as BillStopDate,
DATEDIFF(d, A.BillStopDate, A.COMPLETE_DTE) AS [DIFFERENCE],
ISNULL(J.JOB_NO_OJB,' ') AS JobNumber,
ISNULL(J.IR_TECH_OJB,' ') AS AssignedTech,
ISNULL(J.JOB_TYP_OJB,' ') AS JobType,
ISNULL(CC.DESCR_CTD,' ') AS JobTypeDesc,
A.ORDCLASS AS OrderClass,
A.ORDSTAT AS OrderStatus,
A.OrderRsn1,
A.OrderRsn2,
A.TERMID AS TermID,
A.SALESREP AS SalesRepID,
OP.PerNr,
A.OPID AS CSG_OpID,
ISNULL(OP.[FullName],' ') AS OP_NAME,
ISNULL(OP.[Title],' ') AS OP_TITLE,
OP.DEPARTMENT,
ISNULL(SP.[SUPERVISOR_NAME],' ') AS SUP_NAME,
ISNULL(OP.[Manager],' ') AS MGR_NAME,
ISNULL(OP.[Director],' ') AS DIR_NAME,
ISNULL(NULLIF(ISNULL(J.[LS_CHG_OP_ID_OJB],'ZZO'),J.[LS_CHG_OP_ID_OJB]),'ZZQ') AS LSCHG
INTO #TMP11O
FROM #TMP10O A WITH(NOLOCK) LEFT JOIN Vantage.dbo.OJB_JOBS J (NOLOCK)
ON A.Prin = J.PRIN_OJB AND
A.Agent = J.AGNT_OJB AND
A.ORDNO = J.ORDER_NO_OJB
LEFT JOIN VANTAGE.DBO.CTD_DISPLAY CC (NOLOCK)
ON CC.PRIN_CTD = J.PRIN_OJB AND
CC.CDE_VALUE_CTD = J.JOB_TYP_OJB AND
CC.SPA_FLG_CTD = 'P' AND
CC.CDE_TBL_NO_CTD = '32'
INNER JOIN Vantage.dbo.HSE_BASE H (NOLOCK)
ON A.Prin = H.PRIN_HSE AND
A.Agent = H.AGNT_HSE AND
A.HSEKEY = H.HSE_KEY_HSE
LEFT JOIN Vantage.DBO.SBB_BASE SBB (NOLOCK)
ON A.PRIN = SBB.PRIN_SBB AND
A.AGENT = SBB.AGNT_SBB AND
A.CUSTACCTNO = SBB.CUST_ACCT_NO_SBB AND
A.HSEKEY = SBB.HSE_KEY_SBB
LEFT JOIN VANTAGE.DBO.CTD_DISPLAY CT2 (NOLOCK)
ON SBB.PRIN_SBB = CT2.PRIN_CTD AND
CT2.CDE_TBL_NO_CTD = '24' AND CT2.CDE_VALUE_CTD = SBB.VIP_FLG_SBB AND CT2.SPA_FLG_CTD = 'P'
LEFT JOIN [Vantage].[dbo].[CUS_BASE] CUS (NOLOCK)
ON A.PRIN = CUS.[PRIN_CUS] AND
A.CustAcctNo = CUS.[CUST_ACCT_NO_CUS]
LEFT JOIN [Reporting].[dbo].[UserBaseOpIds] OP (NOLOCK)
ON A.OpID = OP.[CsgOpId]
LEFT JOIN CCBIS.adm.SapPersonnel SP (NOLOCK)
ON OP.PerNr = SP.PERNR
WHERE OP.Manager <> 'Doe, John'
AND OP.Director like 'General%'
AND SBB.EXT_STAT_SBB = ' '
AND A.ORDSTAT LIKE ('CLOSE%')
SELECT B.CSG_OpID, B.OP_NAME, B.SubAcctNo, count(B.SubAcctNo) as Multiple_Act FROM #TMP11O AS B
WHERE B.[DIFFERENCE] > 0 AND B.[DIFFERENCE] < 41000
group by B.CSG_OpID, B.OP_NAME, B.SubAcctNo, B.SubAcctNo, B.CreateDate
Having count(B.SubAcctNo) > 1
Order By B.SubAcctNo, B.OP_NAME
Please let me know if you need anything else. I appreciate you taking the time to help me with this. Thank you.
