Link to home
Start Free TrialLog in
Avatar of maximus1974
maximus1974

asked on

Query pinning SQL Server at 100%

When running the following query, it never completes and pins the SQL Server at 100%. Found that if I comment out this below, it works fine:

AND (WOS.TAT_STOP_DATE_CUST_V2 IS NULL OR (DATEDIFF(MONTH, WOS.TAT_STOP_DATE_CUST_V2,GETDATE())<=36)) --AND WOS.TAT_STOP_DATE_CUST_V2 >= WOS.TAT_START_DATE AND WOS.TAT_STOP_DATE_CUST_V2<=GETDATE()))

I cannot find a problem with the data in these columns.

SELECT
	data_query.*,
	SUM(PROFIT.REVENUE) REV,
	SUM(PROFIT.COGS_LABOR+PROFIT.COGS_PARTS+PROFIT.COGS_SUBCONTRACTING+PROFIT.COGS_REWORKS+PROFIT.COGS_SRU_EXCH+PROFIT.COGS_MISC) COGS, 
	SUM(PROFIT.REVENUE) - sum(PROFIT.COGS_LABOR+PROFIT.COGS_PARTS+PROFIT.COGS_SUBCONTRACTING+PROFIT.COGS_REWORKS+PROFIT.COGS_SRU_EXCH+PROFIT.COGS_MISC) MARGIN_WO
		
FROM (

	SELECT WOS.WO_NUMBER,
		WOS.WO_TYPE,
		WOS.ACCT_COMPANY,
		WOS.DEPT_NAME,
		CASE WHEN WON_AUTO_KEY=2 THEN 'GSTE'
			WHEN WON_AUTO_KEY=7 THEN 'OTHER/NOT CLASSIFIED' --'AF WH - DORAL 2'
			WHEN WON_AUTO_KEY=9 THEN 'DORAL 1'
			WHEN WON_AUTO_KEY=10 THEN 'MIAMI'
			WHEN WON_AUTO_KEY=11 THEN 'DORAL 2'
			WHEN WON_AUTO_KEY=12 THEN 'ATLANTA'
			WHEN WON_AUTO_KEY=13 THEN 'PHOENIX'
			WHEN WON_AUTO_KEY=14 THEN 'LOUISVILLE'
			WHEN WON_AUTO_KEY=32 THEN 'OTHER/NOT CLASSIFIED' --'BIC POOL - DORAL 2'
			ELSE  'OTHER/NOT CLASSIFIED'
		END LOCATION,
		WOS.SHOP_TYPE,
		WOS.MFG_CODE,
		ISNULL(TECHNO.ATTRIBUTE_VALUE, '_NOT CLASSIFIED') "TECHNOLOGY",
		ISNULL(PROD_GROUP.ATTRIBUTE_VALUE, '_NOT CLASSIFIED') "PRODUCT GROUP",
		ISNULL(SUB_GROUP.ATTRIBUTE_VALUE, '_NOT CLASSIFIED') "SUB GROUP",
		WOS.PN,
		WOS.DESCRIPTION,
		MANUF.DESCRIPTION AS OEM,
		WOS.SERIAL_NUMBER,
		WOS.CUSTOMER_NAME,
		WOS.CUSTOMER_PO,
		WOS.PBH_CUSTOMER,
		CASE WHEN PBH_CUSTOMER='N' THEN 'SINGLE EVENT'
			WHEN PBH_CUSTOMER='Y' THEN 
				CASE WHEN WO_TYPE='EXTERNAL' THEN 'PBH - CLOSE LOOP'
					WHEN WO_TYPE='INTERNAL' THEN 'PBH - POOL'
				END
		END CONTRACT_TYPE,
		WOS.WORK_REQUESTED,
		WOS.WORK_PERFORMED,
		WOS.STATUS,
		WOS.SO_NUMBER,
		WOS.EXCHANGE_DATE,
		WOS.EXCHANGE_TYPE_CODE,
		WOS.RO_NUMBER,
		WOS.LAST_RO,
		WOS.SHIPPING_ORDER,
		ISNULL(WOS.CONTRACT_TAT, 21) CONTRACT_TAT,
		WOS.TAT_START_DATE,
		WOS.RECEIVED_DATE,
		WOS.ENTRY_DATE,
		
		CASE WHEN (WOS.TAT_STOP_DATE_CUST_V2 IS NOT NULL) AND (WOS.TAT_STOP_DATE_CUST_V2 >= WOS.TAT_START_DATE) AND (WOS.TAT_START_DATE <= WOS.ENTRY_DATE)
				THEN DATEDIFF(day,WOS.TAT_START_DATE, WOS.ENTRY_DATE) - ISNULL(WOS.RC_EXCUSABLE_DELAY_V2,0) 
			ELSE 0
		END RECEIVING_TAT,
		
		CASE WHEN (WOS.TAT_STOP_DATE_CUST_V2 IS NOT NULL) AND (WOS.TAT_STOP_DATE_CUST_V2 >= WOS.TAT_START_DATE) AND ((WOS.RECEIVED_DATE IS NULL) OR (WOS.RECEIVED_DATE > WOS.ENTRY_DATE)) THEN 1
			ELSE 0
		END INCONSISTENT_RECEIVING_DATE,
						
		CASE WHEN (WOS.TAT_STOP_DATE_CUST_V2 IS NOT NULL) AND (WOS.TAT_STOP_DATE_CUST_V2 >= WOS.TAT_START_DATE) THEN ISNULL(WOS.RC_EXCUSABLE_DELAY_V2,0)
			ELSE 0
		END EXCUSABLE_DELAY_RC,
		
		WOS.DUE_DATE,
		WOS.DATE_MOVED_TO_QUOTING,
		WOS.FIRST_QUOTE_SENT,
		WOS.LAST_QUOTE_SENT,
		WOS.QUOTE_SENT_OVERRIDE,
		
		CASE WHEN (WOS.TAT_STOP_DATE_CUST_V2 IS NOT NULL) AND (WOS.TAT_STOP_DATE_CUST_V2 >= WOS.TAT_START_DATE) AND (WOS.PBH_CUSTOMER='N') AND (ISNULL(WOS.QUOTE_SENT_OVERRIDE, WOS.FIRST_QUOTE_SENT) IS NOT NULL)
			AND (DATEDIFF(DAY,WOS.ENTRY_DATE, ISNULL(WOS.QUOTE_SENT_OVERRIDE, WOS.FIRST_QUOTE_SENT)) > ISNULL(WOS.WO_EXCUSABLE_DELAY_V2,0)) THEN 
				DATEDIFF(DAY,WOS.ENTRY_DATE, ISNULL(WOS.QUOTE_SENT_OVERRIDE, WOS.FIRST_QUOTE_SENT)) - ISNULL(WOS.WO_EXCUSABLE_DELAY_V2,0)
			ELSE 0
		END QUOTING_TAT,
		
		CASE WHEN (WOS.TAT_STOP_DATE_CUST_V2 IS NOT NULL) AND (WOS.TAT_STOP_DATE_CUST_V2 >= WOS.TAT_START_DATE) AND (WOS.PBH_CUSTOMER='N') AND (WOS.QUOTE_SENT_OVERRIDE IS NOT NULL) THEN 1
			ELSE 0
		END CORRECTED_QUOTE_DATE,
		
		CASE WHEN (WOS.TAT_STOP_DATE_CUST_V2 IS NOT NULL) AND (WOS.TAT_STOP_DATE_CUST_V2 >= WOS.TAT_START_DATE) AND (WOS.PBH_CUSTOMER='N') AND (WOS.QUOTE_APPROVED_OVERRIDE IS NOT NULL) THEN 1
			ELSE 0
		END CORRECTED_QUOTE_APPROVED_DATE,
		
		WOS.LAST_QUOTE_APPROVED,
		WOS.QUOTE_APPROVED_OVERRIDE,
		WOS.RELEASE_DATE,
		WOS.CAA_DATE,
		WOS.CLOSE_DATE_ORIG,
		WOS.CLOSE_DATE,
		-- BA_WO_EXCUSABLE_DELAY = BA_WO_EXCUSABLE_DELAY(p_woo,SYSDATE,ENTRY_DATE,CLOSE_DATE_ORIG)
		-- BA_WO_WQ_EXCUSABLE_DELAY = BA_WO_WQ_EXCUSABLE_DELAY(p_woo,SYSDATE,ENTRY_DATE,CLOSE_DATE_ORIG)
		-- BA_WQ_EXCUSABLE_DELAY = BA_WQ_EXCUSABLE_DELAY(p_woo,SYSDATE,ENTRY_DATE,CLOSE_DATE_ORIG)
		-- BA_WO_EXCUSABLE_DELAY_V2 = BA_WO_EXCUSABLE_DELAY - BA_WQ_EXCUSABLE_DELAY + BA_WO_WQ_EXCUSABLE_DELAY
		
		CASE WHEN (WOS.TAT_STOP_DATE_CUST_V2 IS NOT NULL) AND (WOS.TAT_STOP_DATE_CUST_V2 >= WOS.TAT_START_DATE) THEN
				CASE WHEN WOS.PBH_CUSTOMER='Y' THEN
					CASE WHEN (EXCHANGE_TYPE_CODE=5) AND (EXCHANGE_DATE IS NOT NULL) THEN
						DATEDIFF(DAY,WOS.ENTRY_DATE,WOS.TAT_STOP_DATE_CUST_V2)
						ELSE DATEDIFF(DAY,WOS.ENTRY_DATE,WOS.CLOSE_DATE_ORIG)
					END
					- ISNULL(WOS.WO_EXCUSABLE_DELAY_V2,0)
				ELSE
					CASE WHEN (EXCHANGE_TYPE_CODE=5) AND (EXCHANGE_DATE IS NOT NULL) THEN
						DATEDIFF(DAY,ISNULL(ISNULL(WOS.QUOTE_SENT_OVERRIDE, WOS.FIRST_QUOTE_SENT),WOS.ENTRY_DATE),WOS.TAT_STOP_DATE_CUST_V2)
						ELSE DATEDIFF(DAY,ISNULL(ISNULL(WOS.QUOTE_SENT_OVERRIDE, WOS.FIRST_QUOTE_SENT),WOS.ENTRY_DATE),WOS.CLOSE_DATE_ORIG)
					END
					-
					CASE WHEN ISNULL(DATEDIFF(DAY,WOS.ENTRY_DATE, ISNULL(WOS.QUOTE_SENT_OVERRIDE, WOS.FIRST_QUOTE_SENT)),0) < ISNULL(WOS.WO_EXCUSABLE_DELAY_V2,0) THEN
						ISNULL(WOS.WO_EXCUSABLE_DELAY_V2,0) - ISNULL(DATEDIFF(DAY,WOS.ENTRY_DATE, ISNULL(WOS.QUOTE_SENT_OVERRIDE, WOS.FIRST_QUOTE_SENT)),0)
						ELSE 0
					END
				END
				-
				ISNULL(WOS.WQ_EXCUSABLE_DELAY,0)
			ELSE 0
		END SHOP_REPAIR_TAT,  -- TAT BETWEEN QUOTE_DATE AND CLOSE_DATE SUBSTRACTING PART_SHORTAGE

		CASE WHEN (WOS.TAT_STOP_DATE_CUST_V2 IS NOT NULL) AND (WOS.TAT_STOP_DATE_CUST_V2 >= WOS.TAT_START_DATE) THEN ISNULL(PART_SHORT.TAT,0)
			ELSE 0
		END PART_SHORTAGE_TAT,
			
		CASE WHEN (WOS.TAT_STOP_DATE_CUST_V2 IS NOT NULL) AND (WOS.TAT_STOP_DATE_CUST_V2 >= WOS.TAT_START_DATE) THEN (ISNULL(WOS.WQ_EXCUSABLE_DELAY,0)+ISNULL(WOS.WO_EXCUSABLE_DELAY_V2,0))
			ELSE 0
		END EXCUSABLE_DELAY_WO,
		
		CASE WHEN (WOS.TAT_STOP_DATE_CUST_V2 IS NOT NULL) AND (WOS.TAT_STOP_DATE_CUST_V2 >= WOS.TAT_START_DATE) THEN
			CASE WHEN ISNULL(DATEDIFF(DAY,WOS.ENTRY_DATE, ISNULL(WOS.QUOTE_SENT_OVERRIDE, WOS.FIRST_QUOTE_SENT)),0) < ISNULL(WOS.WO_EXCUSABLE_DELAY_V2,0) THEN
				ISNULL(DATEDIFF(DAY,WOS.ENTRY_DATE, ISNULL(WOS.QUOTE_SENT_OVERRIDE, WOS.FIRST_QUOTE_SENT)),0)
				ELSE ISNULL(WOS.WO_EXCUSABLE_DELAY_V2,0)
			END
			ELSE 0
		END WO_QUOTING_EXCUSABLE, --HYPOTHESIS: EXCUSABLE OTHER THAN QUOTE ARE MOSTLY LINLED WITH DISCREPENCIES ON INCOMING UNIT (THUS ALLOCATED AGAINST QUOTING TIME)
		
		CASE WHEN (WOS.TAT_STOP_DATE_CUST_V2 IS NOT NULL) AND (WOS.TAT_STOP_DATE_CUST_V2 >= WOS.TAT_START_DATE) THEN
			ISNULL(WOS.WQ_EXCUSABLE_DELAY,0)
			+
			CASE WHEN ISNULL(DATEDIFF(DAY,WOS.ENTRY_DATE, ISNULL(WOS.QUOTE_SENT_OVERRIDE, WOS.FIRST_QUOTE_SENT)),0) < ISNULL(WOS.WO_EXCUSABLE_DELAY_V2,0) THEN
				ISNULL(WOS.WO_EXCUSABLE_DELAY_V2,0) - ISNULL(DATEDIFF(DAY,WOS.ENTRY_DATE, ISNULL(WOS.QUOTE_SENT_OVERRIDE, WOS.FIRST_QUOTE_SENT)),0)
			ELSE 0
			END
		END WO_AFTER_QUOTING_EXCUSABLE, --HYPOTHESIS: EXCUSABLE OTHER THAN QUOTE ARE MOSTLY LINLED WITH DISCREPENCIES ON INCOMING UNIT (THUS ALLOCATED AGAINST QUOTING TIME)

		WOS.SHIP_DATE,
		WOS.TAT_STOP_DATE_V2 TAT_STOP_DATE,
		WOS.TAT_STOP_DATE_CUST_V2 TAT_STOP_DATE_CUST,
		
		CASE WHEN (WOS.TAT_STOP_DATE_CUST_V2 IS NOT NULL) AND (WOS.TAT_STOP_DATE_CUST_V2 >= WOS.TAT_START_DATE) AND NOT ((EXCHANGE_TYPE_CODE=5) AND (EXCHANGE_DATE IS NOT NULL)) THEN
				DATEDIFF(day,WOS.CLOSE_DATE_ORIG, WOS.TAT_STOP_DATE_CUST_V2) - ISNULL(WOS.SHIPPING_EXCUSABLE_DELAY_V2,0) 
			ELSE 0
		END SHIPPING_TAT, --SHIP_DATE CHANGED TO TAT_STOP_DATE_CUST_V2
		
		CASE WHEN (WOS.TAT_STOP_DATE_CUST_V2 IS NOT NULL) AND (WOS.TAT_STOP_DATE_CUST_V2 >= WOS.TAT_START_DATE) THEN ISNULL(WOS.SHIPPING_EXCUSABLE_DELAY_V2,0)
			ELSE 0
		END EXCUSABLE_DELAY_SH,
		
		
		CASE WHEN (WOS.TAT_STOP_DATE_CUST_V2 IS NOT NULL) AND (WOS.TAT_STOP_DATE_CUST_V2 >= WOS.TAT_START_DATE) THEN 
			DATEDIFF(DAY, WOS.TAT_START_DATE,WOS.TAT_STOP_DATE_CUST_V2) 
			- ISNULL(WOS.RC_EXCUSABLE_DELAY_V2,0) - ISNULL(WOS.SHIPPING_EXCUSABLE_DELAY_V2,0)
			- ISNULL(WOS.WQ_EXCUSABLE_DELAY,0) - ISNULL(WOS.WO_EXCUSABLE_DELAY_V2,0)
			ELSE 0
		END SPT_CUSTOMER, 
			
		WOS.TAT_CORRECTION MANUAL_TAT_ADJUSTMENT,
		
		CASE WHEN (WOS.TAT_STOP_DATE_CUST_V2 IS NOT NULL) AND (WOS.TAT_STOP_DATE_CUST_V2 >= WOS.TAT_START_DATE) THEN WOS.NET_TAT_V2
			ELSE 0
		END  SPT,   --USES TAT_STOP_DATE_V2 NOT STOP_DATE_CUST
		
		WOS.INVC_DATE,

		CASE WHEN (WOS.TAT_STOP_DATE_CUST_V2 IS NULL) OR (WOS.TAT_STOP_DATE_CUST_V2 < WOS.TAT_START_DATE) THEN NULL
			 WHEN ( DATEDIFF(DAY, WOS.TAT_START_DATE,WOS.TAT_STOP_DATE_CUST_V2) 
			 - ISNULL(WOS.RC_EXCUSABLE_DELAY_V2,0) - ISNULL(WOS.SHIPPING_EXCUSABLE_DELAY_V2,0)
			 - ISNULL(WOS.WQ_EXCUSABLE_DELAY,0) - ISNULL(WOS.WO_EXCUSABLE_DELAY_V2,0) ) <= ISNULL(WOS.CONTRACT_TAT,21) THEN 1
		  	 ELSE 0
		END ON_TIME_CUSTOMER,
		
		CASE WHEN (ISNULL(WOS.GROSS_TAT,0) - ISNULL(WOS.RC_EXCUSABLE_DELAY_V2,0) - ISNULL(WOS.WQ_EXCUSABLE_DELAY,0) - ISNULL(WOS.WO_EXCUSABLE_DELAY_V2,0) - ISNULL(WOS.SHIPPING_EXCUSABLE_DELAY_V2,0)) <= ISNULL(WOS.CONTRACT_TAT,21) THEN 1
			ELSE 0
		END ON_TIME,   --USES TAT_STOP_DATE_V2 NOT STOP_DATE_CUST
		
		CASE WHEN WOS.TAT_STOP_DATE_CUST_V2 IS NULL THEN NULL
			WHEN DATEDIFF(WEEK, WOS.TAT_STOP_DATE_CUST_V2,GETDATE()) = 0 THEN 'W'
			ELSE CONCAT('W-',RIGHT(CONCAT('0',DATEDIFF(WEEK, WOS.TAT_STOP_DATE_CUST_V2,GETDATE())),2))
		END AS W_DIFF,
		
		CASE WHEN WOS.TAT_STOP_DATE_CUST_V2 IS NULL THEN NULL
			WHEN DATEDIFF(MONTH, WOS.TAT_STOP_DATE_CUST_V2,GETDATE()) = 0 THEN 'M'
			ELSE CONCAT('M-',RIGHT(CONCAT('0',DATEDIFF(MONTH, WOS.TAT_STOP_DATE_CUST_V2,GETDATE())),2))
		END AS M_DIFF,
		
		CASE WHEN WOS.TAT_STOP_DATE_CUST_V2 IS NULL THEN NULL
            WHEN DATEDIFF(MONTH, WOS.TAT_STOP_DATE_CUST_V2,GETDATE())=0 THEN 'Y'
			WHEN DATEDIFF(MONTH, WOS.TAT_STOP_DATE_CUST_V2,GETDATE()) <= 12 AND DATEDIFF(MONTH, WOS.TAT_STOP_DATE_CUST_V2,GETDATE())>=1 THEN 'Y-1'
			WHEN DATEDIFF(MONTH, WOS.TAT_STOP_DATE_CUST_V2,GETDATE()) <= 24 THEN 'Y-2'
			ELSE 'Y-2 +'
		END AS Y_DIFF,

		CASE WHEN (WOS.TAT_STOP_DATE_CUST_V2 IS NOT NULL) AND (WOS.TAT_STOP_DATE_CUST_V2 >= WOS.TAT_START_DATE) THEN DATEDIFF(DAY,WOS.TAT_START_DATE, WOS.TAT_STOP_DATE_CUST_V2)
			ELSE 0
		END TAT_CUSTOMER,

		CASE WHEN (WOS.TAT_STOP_DATE_CUST_V2 IS NOT NULL) AND (WOS.TAT_STOP_DATE_CUST_V2 >= WOS.TAT_START_DATE) THEN WOS.GROSS_TAT
			ELSE 0
		END TAT,  --USES TAT_STOP_DATE_V2 NOT STOP_DATE_CUST

		CASE WHEN (WOS.TAT_STOP_DATE_CUST_V2 IS NOT NULL) AND (WOS.TAT_STOP_DATE_CUST_V2 >= WOS.TAT_START_DATE) AND (EXCHANGE_TYPE_CODE=5) AND (EXCHANGE_DATE IS NOT NULL) THEN 1
			ELSE 0
		END STANDARD_EXCHANGE_PROVIDED,
		
		CASE WHEN (WOS.TAT_STOP_DATE_CUST_V2 IS NOT NULL) AND (WOS.TAT_STOP_DATE_CUST_V2 >= WOS.TAT_START_DATE) AND (WOS.CAA_DATE IS NOT NULL) THEN 1
			ELSE 0
		END CAA_DATE_USED,
		
		CASE WHEN (WOS.TAT_STOP_DATE_CUST_V2 IS NOT NULL) AND (WOS.TAT_STOP_DATE_CUST_V2 >= WOS.TAT_START_DATE) AND CS_PARTS.TAT > 0 THEN 1
			ELSE 0
		END CS_CUST_PARTS_USED,
		
		CASE WHEN (WOS.TAT_STOP_DATE_CUST_V2 IS NOT NULL) AND (WOS.TAT_STOP_DATE_CUST_V2 >= WOS.TAT_START_DATE) AND CS_PARTS.TAT > 0 THEN CS_PARTS.TAT
			ELSE null
		END CS_CUST_PARTS_TAT,
		
		CASE WHEN (WOS.TAT_STOP_DATE_CUST_V2 IS NOT NULL) AND (WOS.TAT_STOP_DATE_CUST_V2 >= WOS.TAT_START_DATE) AND CS_AWT_APP.TAT > 0 THEN 1
			ELSE 0
		END CS_AWT_APP_USED,
		
		CASE WHEN (WOS.TAT_STOP_DATE_CUST_V2 IS NOT NULL) AND (WOS.TAT_STOP_DATE_CUST_V2 >= WOS.TAT_START_DATE) AND CS_AWT_APP.TAT > 0 THEN CS_AWT_APP.TAT
			ELSE null
		END CS_AWT_APP_TAT,
		
		CASE WHEN (WOS.TAT_STOP_DATE_CUST_V2 IS NOT NULL) AND (WOS.TAT_STOP_DATE_CUST_V2 >= WOS.TAT_START_DATE) AND CS_HOLD.TAT > 0 THEN 1
			ELSE 0
		END CS_CUST_HOLD_USED,
		
		CASE WHEN (WOS.TAT_STOP_DATE_CUST_V2 IS NOT NULL) AND (WOS.TAT_STOP_DATE_CUST_V2 >= WOS.TAT_START_DATE) AND CS_HOLD.TAT > 0 THEN CS_HOLD.TAT
			ELSE null
		END CS_CUST_HOLD_TAT,
		
		CASE WHEN (WOS.TAT_STOP_DATE_CUST_V2 IS NOT NULL) AND (WOS.TAT_STOP_DATE_CUST_V2 >= WOS.TAT_START_DATE) AND CS_DISC.TAT > 0 THEN 1
			ELSE 0
		END CS_DISC_USED,
		
		CASE WHEN (WOS.TAT_STOP_DATE_CUST_V2 IS NOT NULL) AND (WOS.TAT_STOP_DATE_CUST_V2 >= WOS.TAT_START_DATE) AND CS_DISC.TAT > 0 THEN CS_DISC.TAT
			ELSE null
		END CS_DISC_TAT,
		
		CASE WHEN (WOS.TAT_STOP_DATE_CUST_V2 IS NOT NULL) AND (WOS.TAT_STOP_DATE_CUST_V2 >= WOS.TAT_START_DATE) AND FIN_HOLD.TAT > 0 THEN 1
			ELSE 0
		END FIN_HOLD_USED,
		
		CASE WHEN (WOS.TAT_STOP_DATE_CUST_V2 IS NOT NULL) AND (WOS.TAT_STOP_DATE_CUST_V2 >= WOS.TAT_START_DATE) AND FIN_HOLD.TAT > 0 THEN FIN_HOLD.TAT
			ELSE null
		END FIN_HOLD_TAT,
		
		CASE WHEN (WOS.TAT_STOP_DATE_CUST_V2 IS NOT NULL) AND (WOS.TAT_STOP_DATE_CUST_V2 >= WOS.TAT_START_DATE) AND CAA_S.TAT > 0 THEN 1
			ELSE 0
		END CAA_S_USED,
		
		CASE WHEN (WOS.TAT_STOP_DATE_CUST_V2 IS NOT NULL) AND (WOS.TAT_STOP_DATE_CUST_V2 >= WOS.TAT_START_DATE) AND CAA_S.TAT > 0 THEN CAA_S.TAT
			ELSE null
		END CAA_S_TAT,
		
		CASE 
			WHEN WOS.TAT_STOP_DATE_CUST_V2 IS NOT NULL THEN NULL
			WHEN (DATEDIFF(DAY, WOS.TAT_START_DATE, GETDATE()) 
				- ISNULL(WOS.RC_EXCUSABLE_DELAY_V2,0) - ISNULL(WOS.SHIPPING_EXCUSABLE_DELAY_V2,0)
				- ISNULL(WOS.WQ_EXCUSABLE_DELAY,0) - ISNULL(WOS.WO_EXCUSABLE_DELAY_V2,0)
				) <= ISNULL(WOS.CONTRACT_TAT,21) THEN 1 	
			ELSE 0
		END WIP_ON_TIME_CUSTOMER,
		
		CASE 
			WHEN WOS.TAT_STOP_DATE_CUST_V2 IS NOT NULL THEN NULL
			WHEN WOS.NET_TAT_V2 <= ISNULL(WOS.CONTRACT_TAT,21) THEN 1 	
			ELSE 0
		END WIP_ON_TIME
		
	FROM BA_VIEW_WO_SUMMARY WOS
		LEFT JOIN (SELECT PC1.PNM_AUTO_KEY, PC1.ATTRIBUTE_VALUE FROM BA_VIEW_PARTS_CLASSIFICATION PC1 WHERE PC1.UDA_DESCRIPTION='TECHNOLOGY') TECHNO ON TECHNO.PNM_AUTO_KEY=WOS.PNM_AUTO_KEY
		LEFT JOIN (SELECT PC2.PNM_AUTO_KEY, PC2.ATTRIBUTE_VALUE FROM BA_VIEW_PARTS_CLASSIFICATION PC2 WHERE PC2.UDA_DESCRIPTION='PRODUCT GROUPS') PROD_GROUP ON PROD_GROUP.PNM_AUTO_KEY=WOS.PNM_AUTO_KEY
		LEFT JOIN (SELECT PC3.PNM_AUTO_KEY, PC3.ATTRIBUTE_VALUE FROM BA_VIEW_PARTS_CLASSIFICATION PC3 WHERE PC3.UDA_DESCRIPTION='SUB GROUP') SUB_GROUP ON SUB_GROUP.PNM_AUTO_KEY=WOS.PNM_AUTO_KEY 
		LEFT JOIN (SELECT WO_NUMBER, WOO_AUTO_KEY, STATUS STATUS_NAME, SUM(TAT) TAT FROM BA_VIEW_WO_TIME_IN_STATUS WHERE BA_VIEW_WO_TIME_IN_STATUS.STATUS = 'PARTS SHORTAGE' GROUP BY WO_NUMBER, STATUS, WOO_AUTO_KEY) PART_SHORT ON WOS.WOO_AUTO_KEY=PART_SHORT.WOO_AUTO_KEY
		LEFT JOIN (SELECT WO_NUMBER, WOO_AUTO_KEY, STATUS STATUS_NAME, SUM(TAT) TAT FROM BA_VIEW_WO_TIME_IN_STATUS WHERE BA_VIEW_WO_TIME_IN_STATUS.STATUS = 'CS CUST PARTS' GROUP BY WO_NUMBER, STATUS, WOO_AUTO_KEY) CS_PARTS ON WOS.WOO_AUTO_KEY=CS_PARTS.WOO_AUTO_KEY
		LEFT JOIN (SELECT WO_NUMBER, WOO_AUTO_KEY, STATUS STATUS_NAME, SUM(TAT) TAT FROM BA_VIEW_WO_TIME_IN_STATUS WHERE BA_VIEW_WO_TIME_IN_STATUS.STATUS = 'CS CUST HOLD' GROUP BY WO_NUMBER, STATUS, WOO_AUTO_KEY) CS_HOLD ON WOS.WOO_AUTO_KEY=CS_HOLD.WOO_AUTO_KEY
		LEFT JOIN (SELECT WO_NUMBER, WOO_AUTO_KEY, STATUS STATUS_NAME, SUM(TAT) TAT FROM BA_VIEW_WO_TIME_IN_STATUS WHERE BA_VIEW_WO_TIME_IN_STATUS.STATUS = 'CS DISCREPANCY' GROUP BY WO_NUMBER, STATUS, WOO_AUTO_KEY) CS_DISC ON WOS.WOO_AUTO_KEY=CS_DISC.WOO_AUTO_KEY
		LEFT JOIN (SELECT WO_NUMBER, WOO_AUTO_KEY, STATUS STATUS_NAME, SUM(TAT) TAT FROM BA_VIEW_WO_TIME_IN_STATUS WHERE BA_VIEW_WO_TIME_IN_STATUS.STATUS = 'FIN HOLD' GROUP BY WO_NUMBER, STATUS, WOO_AUTO_KEY) FIN_HOLD ON WOS.WOO_AUTO_KEY=FIN_HOLD.WOO_AUTO_KEY
		LEFT JOIN (SELECT WO_NUMBER, WOO_AUTO_KEY, STATUS STATUS_NAME, SUM(TAT) TAT FROM BA_VIEW_WO_TIME_IN_STATUS WHERE BA_VIEW_WO_TIME_IN_STATUS.STATUS = 'CS AWT APPROVAL' GROUP BY WO_NUMBER, STATUS, WOO_AUTO_KEY) CS_AWT_APP ON WOS.WOO_AUTO_KEY=CS_AWT_APP.WOO_AUTO_KEY
		LEFT JOIN (SELECT WO_NUMBER, WOO_AUTO_KEY, STATUS STATUS_NAME, SUM(TAT) TAT FROM BA_VIEW_WO_TIME_IN_STATUS WHERE BA_VIEW_WO_TIME_IN_STATUS.STATUS = 'CAA' GROUP BY WO_NUMBER, STATUS, WOO_AUTO_KEY) CAA_S ON WOS.WOO_AUTO_KEY=CAA_S.WOO_AUTO_KEY
		LEFT JOIN (SELECT MFG_CODE, DESCRIPTION FROM MANUFACTURER) MANUF ON MANUF.MFG_CODE=WOS.MFG_CODE
		

	WHERE NOT(CUSTOMER_NAME LIKE 'AERO%GIES%'  AND (WON_AUTO_KEY=9 OR WON_AUTO_KEY=11)) 
		AND NOT(CUSTOMER_NAME  LIKE 'BARFIELD AERO%'  AND (WON_AUTO_KEY=9 OR WON_AUTO_KEY=11)) 
		AND NOT(CUSTOMER_NAME  LIKE '%PRECISION%ELE%'  AND WON_AUTO_KEY=12) 
		AND NOT(CUSTOMER_NAME  LIKE '%BARFIELD%INC%'  AND (WON_AUTO_KEY=10 OR WON_AUTO_KEY=13 OR WON_AUTO_KEY=14)) 
		AND WOS.TEARDOWN = 'N'
		AND STATUS <> 'CANCELLED'
		AND WOS.WO_NUMBER NOT LIKE '%-%' AND WOS.WO_NUMBER NOT LIKE '%_CORE%'
		AND WOS.WO_NUMBER LIKE 'WO%' AND WOS.WO_NUMBER NOT LIKE '%ADMIN%'
		AND SHOP_TYPE IN ('IN-HOUSE','SUB','GSTE')
		AND (WOS.TAT_STOP_DATE_CUST_V2 IS NULL) OR (DATEDIFF(MONTH, WOS.TAT_STOP_DATE_CUST_V2,GETDATE())<=36 AND WOS.TAT_STOP_DATE_CUST_V2 >= WOS.TAT_START_DATE AND WOS.TAT_STOP_DATE_CUST_V2<=GETDATE())

	GROUP BY WOS.WO_NUMBER,
			WOS.WO_TYPE,
			WOS.ACCT_COMPANY,
			WOS.DEPT_NAME,
            WOS.WON_AUTO_KEY,
			WOS.SHOP_TYPE,
			WOS.ENTRY_DATE,
			WOS.MFG_CODE,
			TECHNO.ATTRIBUTE_VALUE,
			PROD_GROUP.ATTRIBUTE_VALUE,
			SUB_GROUP.ATTRIBUTE_VALUE,
			WOS.PN,
			WOS.DESCRIPTION,
			MANUF.DESCRIPTION,
			WOS.SERIAL_NUMBER,
			WOS.CUSTOMER_NAME,
			WOS.CUSTOMER_PO,
			WOS.PBH_CUSTOMER,
			CONTRACT_TYPE,
			WOS.WORK_REQUESTED,
			WOS.WORK_PERFORMED,
			WOS.STATUS,
			WOS.SO_NUMBER,
			WOS.EXCHANGE_DATE,
			WOS.EXCHANGE_TYPE_CODE,
			WOS.RO_NUMBER,
			WOS.LAST_RO,
			WOS.CONTRACT_TAT,
			WOS.RECEIVED_DATE,
			WOS.DUE_DATE,
			WOS.RC_EXCUSABLE_DELAY_V2,
			WOS.DATE_MOVED_TO_QUOTING,
			WOS.FIRST_QUOTE_SENT,
			WOS.LAST_QUOTE_SENT,
			WOS.QUOTE_SENT_OVERRIDE,
			WOS.LAST_QUOTE_APPROVED,
			WOS.LAST_QUOTE_SENT,
			WOS.QUOTE_SENT_OVERRIDE,
			WOS.LAST_QUOTE_APPROVED,
			WOS.QUOTE_APPROVED_OVERRIDE,
			WOS.RELEASE_DATE,
			WOS.CAA_DATE,
			WOS.CLOSE_DATE_ORIG,
			WOS.CLOSE_DATE,
			WOS.SHIP_DATE,
			WOS.INVC_DATE,
			WOS.SHIPPING_ORDER,
			WOS.WQ_EXCUSABLE_DELAY,
			WOS.WO_EXCUSABLE_DELAY_V2,
			WOS.SHIPPING_EXCUSABLE_DELAY_V2,
			WOS.TAT_START_DATE,
			WOS.TAT_STOP_DATE_V2,
			WOS.TAT_STOP_DATE_CUST_V2,
			WOS.NET_TAT_V2,
			WOS.TAT_CORRECTION,
			WOS.GROSS_TAT,
			PART_SHORT.TAT,
			CS_PARTS.TAT,
			CS_AWT_APP.TAT,
			CS_HOLD.TAT,
			CS_DISC.TAT,
			FIN_HOLD.TAT,
			CAA_S.TAT
	) data_query
	
	LEFT JOIN BA_VIEW_WO_PROFITABILITY PROFIT ON PROFIT.WORK_ORDER = data_query.WO_NUMBER
	
GROUP BY data_query.WO_NUMBER,
	data_query.WO_TYPE,
	data_query.ACCT_COMPANY,
	data_query.DEPT_NAME,
	data_query.LOCATION,
	data_query.SHOP_TYPE,
	data_query.ENTRY_DATE,
	data_query.MFG_CODE,
	data_query."TECHNOLOGY",
	data_query."PRODUCT GROUP",
	data_query."SUB GROUP",
	data_query.PN,
	data_query.DESCRIPTION,
	data_query.OEM,
	data_query.SERIAL_NUMBER,
	data_query.CUSTOMER_NAME,
	data_query.CUSTOMER_PO,
	data_query.PBH_CUSTOMER,
	data_query.CONTRACT_TYPE,
	data_query.WORK_REQUESTED,
	data_query.WORK_PERFORMED,
	data_query.STATUS,
	data_query.SO_NUMBER,
	data_query.EXCHANGE_DATE,
	data_query.EXCHANGE_TYPE_CODE,
	data_query.RO_NUMBER,
	data_query.LAST_RO,
	data_query.CONTRACT_TAT,
	data_query.RECEIVED_DATE,
	data_query.DUE_DATE,
	data_query.DATE_MOVED_TO_QUOTING,
	data_query.FIRST_QUOTE_SENT,
	data_query.LAST_QUOTE_SENT,
	data_query.QUOTE_SENT_OVERRIDE,
	data_query.LAST_QUOTE_APPROVED,
	data_query.QUOTE_APPROVED_OVERRIDE,
	data_query.RELEASE_DATE,
	data_query.CAA_DATE,
	data_query.CLOSE_DATE_ORIG,
	data_query.CLOSE_DATE,
	data_query.SHIP_DATE,
	data_query.INVC_DATE,
	data_query.SHIPPING_ORDER,
	data_query.TAT_START_DATE,
	data_query.TAT_STOP_DATE,
	data_query.TAT_STOP_DATE_CUST,
	data_query.W_DIFF,
	data_query.M_DIFF,
	data_query.Y_DIFF,
	data_query.RECEIVING_TAT,
	data_query.INCONSISTENT_RECEIVING_DATE,
	data_query.QUOTING_TAT,
	data_query.CORRECTED_QUOTE_DATE,
	data_query.CORRECTED_QUOTE_APPROVED_DATE,
	data_query.TAT_CUSTOMER,
	data_query.TAT,
	data_query.STANDARD_EXCHANGE_PROVIDED,
	data_query.CAA_DATE_USED,
	data_query.SHOP_REPAIR_TAT,
	data_query.PART_SHORTAGE_TAT,
	data_query.CS_CUST_PARTS_USED,
	data_query.CS_CUST_PARTS_TAT,
	data_query.CS_AWT_APP_TAT,
	data_query.CS_AWT_APP_USED,
	data_query.CS_CUST_HOLD_TAT,
	data_query.CS_CUST_HOLD_USED,
	data_query.CS_DISC_TAT,
	data_query.CS_DISC_USED,
	data_query.FIN_HOLD_TAT,
	data_query.FIN_HOLD_USED,
	data_query.CAA_S_TAT,
	data_query.CAA_S_USED,
	data_query.EXCUSABLE_DELAY_WO,
	data_query.WO_QUOTING_EXCUSABLE,
	data_query.WO_AFTER_QUOTING_EXCUSABLE,
	data_query.EXCUSABLE_DELAY_RC,
	data_query.EXCUSABLE_DELAY_SH,
	data_query.SHIPPING_TAT, 
	data_query.SPT_CUSTOMER,
	data_query.SPT,  
	data_query.MANUAL_TAT_ADJUSTMENT,
	data_query.ON_TIME_CUSTOMER,
	data_query.ON_TIME,
	data_query.WIP_ON_TIME_CUSTOMER,
	data_query.WIP_ON_TIME

Open in new window

SOLUTION
Avatar of arnold
arnold
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Avatar of lcohan
lcohan
Flag of Canada image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial