where oc.create_dte_ocr <= dateadd(day,-3,date)

where oc.create_dte_ocr >= dateadd(day,-3,date)

IF OBJECT_ID('TEMPDB..#TMPCSGACCT1') IS NOT NULL DROP TABLE #TMPCSGACCT1
IF OBJECT_ID('TEMPDB..#TMP1C') IS NOT NULL DROP TABLE #TMP1C

CREATE TABLE #TMPCSGACCT1 (
      [ITEM#] NUMERIC (7,0),
      [SUB_ACCT_NO] VARCHAR (MAX),
      [CUST_ACCT_NO] VARCHAR (MAX),
      [ORDER_NUMB] VARCHAR (MAX)
)
INSERT INTO #TMPCSGACCT1 (ITEM#,SUB_ACCT_NO,CUST_ACCT_NO,ORDER_NUMB)

SELECT ITEM#,
	   rtrim(ltrim([SUB_ACCT_NO])) as SUB_ACCT_NO,
	   rtrim(ltrim([CUST_ACCT_NO])) as CUST_ACCT_NO,
	   rtrim(ltrim([ORDER_NUMB])) as ORDER_NUMB
	   
FROM AuditGroup.dbo.Order_Num_Data3 (NOLOCK)

SELECT A.ITEM#, A.SUB_ACCT_NO, A.CUST_ACCT_NO, A.ORDER_NUMB, B.PRIN_OJB, B.AGNT_OJB,
B.JOB_TYP_OJB, B.COMPL_CDE_OJB, B.LS_CHG_OP_ID_OJB, B.LS_CHG_DTE_OJB, B.HSE_KEY_OJB

INTO #TMP1C

FROM #TMPCSGACCT1 AS A (NOLOCK) INNER JOIN Vantage.dbo.OJB_JOBS AS B (NOLOCK)

ON A.ORDER_NUMB = B.ORDER_NO_OJB

AND B.LS_CHG_DTE_OJB = (SELECT MAX(LS_CHG_DTE_OJB) AS LS_CHG_DTE_OJB FROM
	  Vantage.dbo.OJB_JOBS AS C WHERE C.LS_CHG_DTE_OJB = B.LS_CHG_DTE_OJB)
	  
--AND A.ITEM# = 1
GROUP BY A.ITEM#, A.SUB_ACCT_NO, A.CUST_ACCT_NO, A.ORDER_NUMB, B.PRIN_OJB, B.AGNT_OJB,
B.JOB_TYP_OJB, B.COMPL_CDE_OJB, B.LS_CHG_OP_ID_OJB, B.LS_CHG_DTE_OJB, B.HSE_KEY_OJB


SELECT DISTINCT C.ITEM#, C.SUB_ACCT_NO, C.CUST_ACCT_NO, --MAX(OC.CUST_SEQ_NO_OCR) AS CUST_SEQ_NO_OCR,
C.ORDER_NUMB AS PREV_ORDER_NUMB, MAX(OC.ORDER_NO_OCR) AS CURR_ORDER_NUMB, 
CASE WHEN OC.ORD_CLASS_OCR = 'M' THEN 'SRO'
	 WHEN OC.ORD_CLASS_OCR = 'S' THEN 'SERVICE_ORD'
	 WHEN OC.ORD_CLASS_OCR = 'T' THEN 'TC'
	 ELSE ' ' END AS 'ORD_CLASS',
CASE WHEN OC.ORD_STAT_OCR = 'C' THEN 'CLOSED'
	 WHEN OC.ORD_STAT_OCR = 'I' THEN 'IN_PROGRESS'
	 WHEN OC.ORD_STAT_OCR = 'O' THEN 'OPEN'
	 WHEN OC.ORD_STAT_OCR = 'X' THEN 'CANCELLED'
	 ELSE ' ' END AS 'ORD_STAT',
ISNULL(CD2.DESCR_CTD,' ') AS ORDER_RSN1,
--ISNULL(CD3.DESCR_CTD,' ') AS ORDER_RSN2,
ISNULL(C.JOB_TYP_OJB,' ') AS JOB_TYPE,
ISNULL(CD.DESCR_CTD,' ') AS JOB_TYPE_DESC,
OC.PRIN_OCR, OC.CREATE_DTE_OCR, OC.COMPL_DTE_OCR, OC.LS_CHG_DTE_OCR, --OC.COMPL_TME_OCR, 
C.LS_CHG_OP_ID_OJB, C.LS_CHG_DTE_OJB, 
C.COMPL_CDE_OJB, OC.COMPL_CDE_OCR
,CD.DESCR_CTD


FROM #TMP1C AS C (NOLOCK) INNER JOIN Vantage.dbo.OCR_ORDER_COMP AS OC (NOLOCK)

ON C.CUST_ACCT_NO = OC.CUST_ACCT_NO_OCR
AND C.HSE_KEY_OJB = OC.HSE_KEY_OCR

AND OC.COMPL_DTE_OCR = (SELECT MAX(COMPL_DTE_OCR) AS COMPL_DTE_OCR 
						FROM Vantage.dbo.OCR_ORDER_COMP AS D WHERE D.COMPL_DTE_OCR = OC.COMPL_DTE_OCR)

--AND OC.LS_CHG_DTE_OCR = (SELECT MAX(LS_CHG_DTE_OCR) AS LS_CHG_DTE_OCR 
--						 FROM Vantage.dbo.OCR_ORDER_COMP AS E WHERE E.LS_CHG_DTE_OCR = OC.LS_CHG_DTE_OCR)
	

INNER JOIN Vantage.dbo.OCR_ORDER_COMP AS OD (NOLOCK)
ON OC.CUST_ACCT_NO_OCR = OD.CUST_ACCT_NO_OCR

LEFT OUTER JOIN Vantage.dbo.CTD_DISPLAY AS CD (NOLOCK)
ON	CD.PRIN_CTD = C.PRIN_OJB
AND CD.CDE_VALUE_CTD = C.JOB_TYP_OJB
AND CD. SPA_FLG_CTD = 'P'
AND CD. CDE_TBL_NO_CTD = '32' AND CD.PRIN_CTD IN (6000,7500)

LEFT OUTER JOIN Vantage.dbo.CTD_DISPLAY AS CD2 (NOLOCK)
ON  CD2.PRIN_CTD = OC.PRIN_OCR
AND CD2.CDE_VALUE_CTD = SUBSTRING(OC.ORD_RSN_OCR,1,2)
AND CD2.CDE_TBL_NO_CTD = '05'
AND CD2.SPA_FLG_CTD = 'P' AND CD2.PRIN_CTD IN (6000,7500)



WHERE OC.PRIN_OCR IN (6000,7500)
--AND C.JOB_TYP_OJB = 'HC'
AND OD.CREATE_DTE_OCR > OC.CREATE_DTE_OCR
AND OC.ORD_CLASS_OCR <> 'M'
AND OC.CREATE_DTE_OCR <= DATEADD(DAY,-3,OD.COMPL_DTE_OCR)
AND CD.DESCR_CTD LIKE ('%FAILED%')

--where b.LS_CHG_DTE_OJB > b.COMPL_DTE_OJB

GROUP BY C.ITEM#, C.SUB_ACCT_NO, C.CUST_ACCT_NO, --OC.CUST_SEQ_NO_OCR, 
C.ORDER_NUMB, OC.ORDER_NO_OCR,
OC.ORD_CLASS_OCR, OC.ORD_STAT_OCR,
CD2.DESCR_CTD,
--CD3.DESCR_CTD,
C.JOB_TYP_OJB,
CD.DESCR_CTD,
OC.PRIN_OCR, OC.CREATE_DTE_OCR, OC.COMPL_DTE_OCR, OC.LS_CHG_DTE_OCR, OC.COMPL_TME_OCR, C.LS_CHG_OP_ID_OJB, C.LS_CHG_DTE_OJB, 
C.COMPL_CDE_OJB, OC.COMPL_CDE_OCR
,CD.DESCR_CTD

ORDER BY C.ITEM#