InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 17 Jun 2013 21:40:07 GMT20http://www.sqlservercentral.com/Forums/Topic1383379-338-1.aspxThat is what could easily win "Query From Hell" competition...It's "War and Peace" of the queries!Does it run at all (even without grouping)?Before trying to help or resolve any problem related to this query, I would advise the following:1. Re-factor by selecting intermediate sub-queries results into temp tables2. You continues checks for "SUBSTRING(CLAIM_PROJ_NUM,13,1) IN ('A','B'..." better to be done once (filter relevant recors into temp-table before using it)3. Format it accurately, to make it readable without causing vision distortion. Is any particular reason for using NOLOCK? Do you understand all consequences? For example, your query can read some data multiple times, and it can also miss some data completely, and that includes data which are not subject of running UPDATE/INSERT/DELETE queries.Mon, 12 Nov 2012 03:19:16 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1383379-338-1.aspxI would like the column PROJECT_LEVEL to consists of the MAX value within the column DASHBOARD_STATUS_LEVEL for each group (groups determined by MARKET_PROJECT column). I am confused on how to state this within my subquery.The query I have below is returning the value in the DASHBOARD_STATUS_LEVEL column for each record. For Example:The PROJECT_LEVEL column should return the MAX DASHBOARD_STATUS_LEVEL for every MARKET_PROJECT of the same id.SELECT SQL10.MARKET_PROJECT, SQL10.PROJECT_STATUS, SQL10.PROJ_TYPE_NAME, SQL10.DAYS_IN_LAST_PROJ_STAT, SQL10.PROV_NAME, SQL10.PROV_NUM, SQL10.PROV_IRS, SQL10.PROVIDER_STATUS, SQL10.TOTAL_UNVALIDATED_CLAIMS, SQL10.TOTAL_PROVIDERS, SQL10.OVP, SQL10.LOAD_DT, SQL10.OK_TO_ASSIGN_DT, SQL10.ASSIGNED_DATE, SQL10.DAYS_UNTIL_EXPIRE, SQL10.DASHBOARD_STATUS_LEVEL,[b] MAX(SQL10.DASHBOARD_STATUS_LEVEL) AS [PROJECT_LEVEL],[/b] MIN(SQL10.DAYS_UNTIL_EXPIRE) AS [PROJECT_MINIMUM_DAYS_TO_EXPIRE]FROM (SELECT M.MARKET_NAME+' '+C.CLAIM_PROJ_NUM as MARKET_PROJECT, PRJS.STATUS_NAME AS [PROJECT_STATUS], PT.PROJ_TYPE_NAME, DATEDIFF("D", GETDATE(), PH.CREATE_DTTM) AS [DAYS_IN_LAST_PROJ_STAT], PRV.PROV_NAME, PRV.PROV_NUM, PRV.PROV_IRS, PRVS.STATUS_NAME AS [PROVIDER_STATUS],-- CASE WHEN X.PROV_NUM IS NULL THEN DATEDIFF("D", GETDATE(), PRV.PROV_CREATE_DTTM)-- WHEN X.PROV_NUM IS NOT NULL AND X.CREATE_DTTM < PRV.PROV_CREATE_DTTM THEN DATEDIFF("D", GETDATE(), PRV.PROV_CREATE_DTTM)-- ELSE DATEDIFF("D", GETDATE(), X.CREATE_DTTM)-- END AS [DAYS_IN_LAST_PROV_STAT], UC.TOTAL_UNVALIDATED_CLAIMS, COUNT (PRV.PROV_NUM)AS [TOTAL_PROVIDERS], SUM(OVP_AMT) AS [OVP], MIN(C.CLAIM_CREATE_DTTM) AS [LOAD_DT], OK.CREATE_DTTM AS [OK_TO_ASSIGN_DT], AH.ASSIGNED_DATE, CASE WHEN MIN(SQL1.DAYS_AGED) > 30 THEN '1' WHEN MIN(SQL1.DAYS_AGED) > 15 THEN '2' ELSE '3' END AS [DASHBOARD_STATUS_LEVEL], GETDATE() + MIN(SQL1.DAYS_AGED) AS [WILL_EXPIRE_ON], MIN(SQL1.DAYS_AGED) AS [DAYS_UNTIL_EXPIRE] FROM ( --Cullpepper SELECT C.CLAIM_ID, DATEDIFF("D", (GETDATE() - 180), C.EFF_DT) AS [DAYS_AGED], 'Cullpepper' AS [EXPIRE_TYPE] FROM OVP_CLAIM C WITH (NOLOCK) INNER JOIN OVP_PROJECT P WITH (NOLOCK) ON C.CLAIM_PROJ_ID = P.PROJ_ID INNER JOIN OVP_MSTR_STATUS PS WITH (NOLOCK) ON P.PROJ_STAT_ID = PS.STATUS_ID INNER JOIN OVP_PROJMAIN PM WITH (NOLOCK) ON P.PROJMAIN_ID = PM.PROJMAIN_ID INNER JOIN OVP_MSTR_STATUS CS WITH (NOLOCK) ON C.CLAIM_STAT_ID = CS.STATUS_ID INNER JOIN OVP_PROVIDER PROV WITH (NOLOCK) ON PROV.PROV_ID = C.CLAIM_PROV_ID WHERE PROV.PROV_IRS IN ('540622371') AND PROJ_STAT_ID NOT IN (5, 7, 25, 26, 27) AND MEDICARE_FLAG IS NULL AND C.CLAIM_STAT_ID IN (8,19) AND SUBSTRING(CLAIM_PROJ_NUM,3,1) <> 'H' AND (GETDATE()-180) <= C.EFF_DT -- Will collect unexpired records AND (NOT (SUBSTRING(CLAIM_PROJ_NUM,12,1) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') OR SUBSTRING(CLAIM_PROJ_NUM,13,1) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') OR SUBSTRING(CLAIM_PROJ_NUM,14,1) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') OR SUBSTRING(CLAIM_PROJ_NUM,15,1) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') OR SUBSTRING(CLAIM_PROJ_NUM,6,1) = '-')) UNION ALL --Expire check for Texas Cooks Children Medical Center SELECT C.CLAIM_ID, DATEDIFF("D", (GETDATE() - 365), C.EFF_DT) AS [DAYS_AGED], 'Texas Cooks Children Medical Center' AS [EXPIRE_TYPE] FROM OVP_CLAIM C WITH (NOLOCK) INNER JOIN OVP_PROJECT P WITH (NOLOCK) ON C.CLAIM_PROJ_ID = P.PROJ_ID INNER JOIN OVP_MSTR_STATUS PS WITH (NOLOCK) ON P.PROJ_STAT_ID = PS.STATUS_ID INNER JOIN OVP_PROJMAIN PM WITH (NOLOCK) ON P.PROJMAIN_ID = PM.PROJMAIN_ID INNER JOIN OVP_MSTR_STATUS CS WITH (NOLOCK) ON C.CLAIM_STAT_ID = CS.STATUS_ID INNER JOIN OVP_PROVIDER PROV WITH (NOLOCK) ON PROV.PROV_ID = C.CLAIM_PROV_ID WHERE PROV.PROV_IRS IN ('752051646','752485366') AND PROJ_STAT_ID NOT IN (5, 7, 25, 26, 27) AND MEDICARE_FLAG IS NULL AND C.CLAIM_STAT_ID IN (8,19) AND SUBSTRING(CLAIM_PROJ_NUM,3,1) <> 'H' AND (GETDATE()-365) <= C.EFF_DT -- Will collect unexpired records AND (NOT (SUBSTRING(CLAIM_PROJ_NUM,12,1) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') OR SUBSTRING(CLAIM_PROJ_NUM,13,1) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') OR SUBSTRING(CLAIM_PROJ_NUM,14,1) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') OR SUBSTRING(CLAIM_PROJ_NUM,15,1) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') OR SUBSTRING(CLAIM_PROJ_NUM,6,1) = '-')) UNION ALL --TINS in Expire Table SELECT C.CLAIM_ID, DATEDIFF("D", (GETDATE() - CASE WHEN X.MONTHS_BEFORE_EXPIRE = 12 THEN 365 ELSE X.MONTHS_BEFORE_EXPIRE * 30 END), PAID_DT) AS [DAYS_AGED], 'TIN in Expire Table (special rules apply)' AS [EXPIRE_TYPE] FROM OVP_CLAIM C WITH (NOLOCK) INNER JOIN OVP_PROJECT P WITH (NOLOCK) ON C.CLAIM_PROJ_ID = P.PROJ_ID INNER JOIN OVP_MSTR_STATUS PS WITH (NOLOCK) ON P.PROJ_STAT_ID = PS.STATUS_ID INNER JOIN OVP_PROJMAIN PM WITH (NOLOCK) ON P.PROJMAIN_ID = PM.PROJMAIN_ID INNER JOIN OVP_MSTR_STATUS CS WITH (NOLOCK) ON C.CLAIM_STAT_ID = CS.STATUS_ID INNER JOIN OVP_PROVIDER PROV WITH (NOLOCK) ON PROV.PROV_ID = C.CLAIM_PROV_ID INNER JOIN OVP_EXPIRE_TIN X WITH (NOLOCK) ON PROV.PROV_IRS = X.TIN AND X.END_RECORD_DATE IS NULL AND (X.ALL_FLAG IS NOT NULL OR (X.BEGIN_DATE IS NOT NULL AND X.END_DATE IS NOT NULL AND C.EFF_DT >= X.BEGIN_DATE AND C.EFF_DT <= X.END_DATE)) WHERE C.CLAIM_STAT_ID IN (8,19) AND (GETDATE() - CASE WHEN X.MONTHS_BEFORE_EXPIRE = 12 THEN 365 ELSE X.MONTHS_BEFORE_EXPIRE * 30 END) <= PAID_DT AND PROJ_STAT_ID NOT IN (5, 7, 25, 26, 27) AND MEDICARE_FLAG IS NULL AND SUBSTRING(CLAIM_PROJ_NUM,3,1) <> 'H' AND C.CLAIM_MARKET_ID <> 8 AND (NOT (SUBSTRING(CLAIM_PROJ_NUM,12,1) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') OR SUBSTRING(CLAIM_PROJ_NUM,13,1) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') OR SUBSTRING(CLAIM_PROJ_NUM,14,1) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') OR SUBSTRING(CLAIM_PROJ_NUM,15,1) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') OR SUBSTRING(CLAIM_PROJ_NUM,6,1) = '-')) UNION ALL --Default Logic SELECT C.CLAIM_ID, DATEDIFF("D", (GETDATE() - DATEDIFF(DAY, CUTOFF_DT, RECD_DT)), PAID_DT) AS [DAYS_AGED], 'Default Expire Logic' AS [EXPIRE_TYPE] FROM OVP_CLAIM C WITH (NOLOCK) INNER JOIN OVP_PROJECT P WITH (NOLOCK) ON C.CLAIM_PROJ_ID = P.PROJ_ID INNER JOIN OVP_MSTR_STATUS PS WITH (NOLOCK) ON P.PROJ_STAT_ID = PS.STATUS_ID INNER JOIN OVP_PROJMAIN PM WITH (NOLOCK) ON P.PROJMAIN_ID = PM.PROJMAIN_ID INNER JOIN OVP_MSTR_STATUS CS WITH (NOLOCK) ON C.CLAIM_STAT_ID = CS.STATUS_ID INNER JOIN OVP_PROVIDER PROV WITH (NOLOCK) ON C.CLAIM_PROV_ID = PROV.PROV_ID WHERE CUTOFF_DT Is Not Null AND PROJ_STAT_ID NOT IN (5, 7, 25, 26, 27) AND MEDICARE_FLAG IS NULL AND C.CLAIM_STAT_ID IN (8,19) AND CLAIM_MARKET_ID NOT IN (17,8,16) AND SUBSTRING(CLAIM_PROJ_NUM,3,1) <> 'H' AND PAID_DT >= GETDATE() - DATEDIFF(DAY, CUTOFF_DT, RECD_DT) AND (NOT (CLAIM_MARKET_ID = 5 AND APPLY_6_MONTH_RULE IN ('X','x') AND PAID_DT >= '2007-07-01')) AND (NOT (SUBSTRING(CLAIM_PROJ_NUM,12,1) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') OR SUBSTRING(CLAIM_PROJ_NUM,13,1) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') OR SUBSTRING(CLAIM_PROJ_NUM,14,1) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') OR SUBSTRING(CLAIM_PROJ_NUM,15,1) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') OR SUBSTRING(CLAIM_PROJ_NUM,6,1) = '-')) UNION ALL --Tennessee Logic SELECT C.CLAIM_ID, DATEDIFF("D", CASE WHEN PM.PROJ_TYPE_ID IN (1,21,25,30) THEN (GETDATE() - 270) ELSE (GETDATE() - 1825) END, PAID_DT) AS [DAYS_AGED], 'Tennessee Expire Logic' AS [EXPIRE_TYPE] FROM OVP_CLAIM C WITH (NOLOCK) INNER JOIN OVP_PROJECT P WITH (NOLOCK) ON C.CLAIM_PROJ_ID = P.PROJ_ID INNER JOIN OVP_MSTR_STATUS PS WITH (NOLOCK) ON P.PROJ_STAT_ID = PS.STATUS_ID INNER JOIN OVP_PROJMAIN PM WITH (NOLOCK) ON P.PROJMAIN_ID = PM.PROJMAIN_ID INNER JOIN OVP_MSTR_STATUS CS WITH (NOLOCK) ON C.CLAIM_STAT_ID = CS.STATUS_ID INNER JOIN OVP_PROVIDER PROV WITH (NOLOCK) ON C.CLAIM_PROV_ID = PROV.PROV_ID WHERE CUTOFF_DT Is Not Null AND PROJ_STAT_ID NOT IN (5, 7, 25, 26, 27) AND MEDICARE_FLAG IS NULL AND C.CLAIM_STAT_ID IN (8,19) AND SUBSTRING(CLAIM_PROJ_NUM,3,1) <> 'H' AND CLAIM_MARKET_ID IN (8) AND PAID_DT >= CASE WHEN PM.PROJ_TYPE_ID IN (1,21,25,30) THEN (GETDATE() - 270) ELSE (GETDATE() - 1825) END AND (NOT (SUBSTRING(CLAIM_PROJ_NUM,12,1) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') OR SUBSTRING(CLAIM_PROJ_NUM,13,1) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') OR SUBSTRING(CLAIM_PROJ_NUM,14,1) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') OR SUBSTRING(CLAIM_PROJ_NUM,15,1) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') OR SUBSTRING(CLAIM_PROJ_NUM,6,1) = '-')) UNION ALL --Virginia Logic SELECT C.CLAIM_ID, DATEDIFF("D", (GETDATE() - 180), PAID_DT) AS [DAYS_AGED], 'Virginia Expire Logic' AS [EXPIRE_TYPE] FROM OVP_CLAIM C WITH (NOLOCK) INNER JOIN OVP_PROJECT P WITH (NOLOCK) ON C.CLAIM_PROJ_ID = P.PROJ_ID INNER JOIN OVP_MSTR_STATUS PS WITH (NOLOCK) ON P.PROJ_STAT_ID = PS.STATUS_ID INNER JOIN OVP_PROJMAIN PM WITH (NOLOCK) ON P.PROJMAIN_ID = PM.PROJMAIN_ID INNER JOIN OVP_MSTR_STATUS CS WITH (NOLOCK) ON C.CLAIM_STAT_ID = CS.STATUS_ID INNER JOIN OVP_PROVIDER PROV WITH (NOLOCK) ON C.CLAIM_PROV_ID = PROV.PROV_ID WHERE CUTOFF_DT Is Not Null AND PROJ_STAT_ID NOT IN (5, 7, 25, 26, 27) AND MEDICARE_FLAG IS NULL AND C.CLAIM_STAT_ID IN (8,19) AND SUBSTRING(CLAIM_PROJ_NUM,3,1) <> 'H' AND CLAIM_MARKET_ID IN (16) AND PAID_DT >= (GETDATE() - 180) AND PROV.PROV_IRS NOT IN ('541190771','621711997','541719463') AND (NOT (SUBSTRING(CLAIM_PROJ_NUM,12,1) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') OR SUBSTRING(CLAIM_PROJ_NUM,13,1) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') OR SUBSTRING(CLAIM_PROJ_NUM,14,1) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') OR SUBSTRING(CLAIM_PROJ_NUM,15,1) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') OR SUBSTRING(CLAIM_PROJ_NUM,6,1) = '-')) UNION ALL --Expire logic for Maryland 6 Months SELECT C.CLAIM_ID, DATEDIFF("D", (GETDATE() - 180), PAID_DT) AS [DAYS_AGED], 'Maryland 6-Month Expire' AS [EXPIRE_TYPE] FROM OVP_CLAIM C WITH (NOLOCK) INNER JOIN OVP_PROJECT P WITH (NOLOCK) ON C.CLAIM_PROJ_ID = P.PROJ_ID INNER JOIN OVP_MSTR_STATUS PS WITH (NOLOCK) ON P.PROJ_STAT_ID = PS.STATUS_ID INNER JOIN OVP_PROJMAIN PM WITH (NOLOCK) ON P.PROJMAIN_ID = PM.PROJMAIN_ID INNER JOIN OVP_MSTR_STATUS CS WITH (NOLOCK) ON C.CLAIM_STAT_ID = CS.STATUS_ID WHERE C.CLAIM_STAT_ID IN (8,19) AND PROJ_STAT_ID NOT IN (5, 7, 25, 26, 27) AND MEDICARE_FLAG IS NULL AND CLAIM_MARKET_ID = 5 --Maryland claim AND APPLY_6_MONTH_RULE IN ('X','x') --Apply 6 Month Rule has been checked AND PAID_DT >= GETDATE() - 180 AND SUBSTRING(CLAIM_PROJ_NUM,3,1) <> 'H' AND PAID_DT > '2007-07-01' AND (NOT (SUBSTRING(CLAIM_PROJ_NUM,12,1) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') OR SUBSTRING(CLAIM_PROJ_NUM,13,1) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') OR SUBSTRING(CLAIM_PROJ_NUM,14,1) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') OR SUBSTRING(CLAIM_PROJ_NUM,15,1) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') OR SUBSTRING(CLAIM_PROJ_NUM,6,1) = '-')) UNION ALL --Expire Logic - Georgia SELECT C.CLAIM_ID, DATEDIFF("D", (GETDATE()-1), (CASE WHEN DATEDIFF(DD,C.END_DT,C.REC_DT) <= 90 THEN C.END_DT + 365 ELSE C.REC_DT + 365 END)) AS [DAYS_AGED], 'Georgia Expire Logic' AS [EXPIRE_TYPE] FROM OVP_CLAIM C WITH (NOLOCK) INNER JOIN OVP_PROJECT P WITH (NOLOCK) ON C.CLAIM_PROJ_ID = P.PROJ_ID INNER JOIN OVP_MSTR_STATUS PS WITH (NOLOCK) ON P.PROJ_STAT_ID = PS.STATUS_ID INNER JOIN OVP_PROJMAIN PM WITH (NOLOCK) ON P.PROJMAIN_ID = PM.PROJMAIN_ID INNER JOIN OVP_MSTR_STATUS CS WITH (NOLOCK) ON C.CLAIM_STAT_ID = CS.STATUS_ID WHERE CUTOFF_DT Is Not Null AND PROJ_STAT_ID NOT IN (5, 7, 25, 26, 27) AND MEDICARE_FLAG IS NULL AND C.CLAIM_STAT_ID IN (8,19) AND SUBSTRING(CLAIM_PROJ_NUM,3,1) <> 'H' AND CLAIM_MARKET_ID = 17 --Georgia Market Only AND (GETDATE()-1) <= (CASE WHEN DATEDIFF(DD,C.END_DT,C.REC_DT) <= 90 THEN C.END_DT + 365 ELSE C.REC_DT + 365 END) AND (NOT (SUBSTRING(CLAIM_PROJ_NUM,12,1) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') OR SUBSTRING(CLAIM_PROJ_NUM,13,1) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') OR SUBSTRING(CLAIM_PROJ_NUM,14,1) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') OR SUBSTRING(CLAIM_PROJ_NUM,15,1) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') OR SUBSTRING(CLAIM_PROJ_NUM,6,1) = '-')) UNION ALL --Medicare SELECT C.CLAIM_ID, DATEDIFF("D", (GETDATE() - 1095), PAID_DT) AS [DAYS_AGED], 'Medicare Expire Logic' AS [EXPIRE_TYPE] FROM OVP_CLAIM C WITH (NOLOCK) INNER JOIN OVP_PROJECT P WITH (NOLOCK) ON C.CLAIM_PROJ_ID = P.PROJ_ID INNER JOIN OVP_MSTR_STATUS PS WITH (NOLOCK) ON P.PROJ_STAT_ID = PS.STATUS_ID INNER JOIN OVP_PROJMAIN PM WITH (NOLOCK) ON P.PROJMAIN_ID = PM.PROJMAIN_ID INNER JOIN OVP_MSTR_STATUS CS WITH (NOLOCK) ON C.CLAIM_STAT_ID = CS.STATUS_ID INNER JOIN OVP_PROVIDER PROV WITH (NOLOCK) ON C.CLAIM_PROV_ID = PROV.PROV_ID WHERE CUTOFF_DT Is Not Null AND PROJ_STAT_ID NOT IN (5, 7, 25, 26, 27) AND MEDICARE_FLAG IS NOT NULL AND C.CLAIM_STAT_ID IN (8,19) AND SUBSTRING(CLAIM_PROJ_NUM,3,1) <> 'H' AND PAID_DT >= CAST(YEAR((GETDATE() - 1095)) AS VARCHAR(4)) + '-01-01' AND (NOT (SUBSTRING(CLAIM_PROJ_NUM,12,1) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') OR SUBSTRING(CLAIM_PROJ_NUM,13,1) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') OR SUBSTRING(CLAIM_PROJ_NUM,14,1) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') OR SUBSTRING(CLAIM_PROJ_NUM,15,1) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') OR SUBSTRING(CLAIM_PROJ_NUM,6,1) = '-')) ) SQL1 INNER JOIN OVP_CLAIM C WITH (NOLOCK) ON SQL1.CLAIM_ID = C.CLAIM_ID INNER JOIN UTI_MSTR_MARKET M WITH (NOLOCK) ON C.CLAIM_MARKET_ID = M.MARKET_ID INNER JOIN OVP_PROVIDER PRV WITH (NOLOCK) ON PRV.PROV_ID = C.CLAIM_PROV_ID INNER JOIN OVP_PROJECT PRJ WITH (NOLOCK) ON PRJ.PROJ_ID = C.CLAIM_PROJ_ID INNER JOIN OVP_MSTR_STATUS PRVS WITH (NOLOCK) ON PRV.PROV_STAT_ID = PRVS.STATUS_ID INNER JOIN OVP_MSTR_STATUS PRJS WITH (NOLOCK) ON PRJ.PROJ_STAT_ID = PRJS.STATUS_ID INNER JOIN (SELECT CLAIM_PROV_ID, COUNT(CLAIM_ID) AS [TOTAL_UNVALIDATED_CLAIMS] FROM OVP_CLAIM WITH (NOLOCK) WHERE CLAIM_STAT_ID IN (8,19) GROUP BY CLAIM_PROV_ID) UC ON C.CLAIM_PROV_ID = UC.CLAIM_PROV_ID LEFT OUTER JOIN (SELECT SOURCE_ID, MAX(STAT_CHANGE_ID) AS [MAX_ID] FROM OVP_STATUS WITH (NOLOCK) WHERE ENT_TYPE_ID = 3 AND NEW_STAT_ID = 3 GROUP BY SOURCE_ID) MX ON MX.SOURCE_ID = C.CLAIM_PROJ_ID LEFT OUTER JOIN OVP_STATUS OK WITH (NOLOCK) ON OK.STAT_CHANGE_ID = MX.MAX_ID LEFT OUTER JOIN (SELECT SOURCE_ID, ASSIGNED_TO, MIN(HISTORY_ID) AS [MIN_ID] FROM OVP_ASSIGNMENT_HISTORY WITH (NOLOCK) WHERE ENT_TYPE_ID = 1 GROUP BY SOURCE_ID, ASSIGNED_TO) MN ON MN.SOURCE_ID = C.CLAIM_PROV_ID AND MN.ASSIGNED_TO = PRV.PROV_ASSIGN_ID LEFT OUTER JOIN OVP_ASSIGNMENT_HISTORY AH WITH (NOLOCK) ON MN.MIN_ID = AH.HISTORY_ID INNER JOIN OVP_PROJMAIN PM WITH (NOLOCK) ON C.CLAIM_PROJMAIN_ID = PM.PROJMAIN_ID INNER JOIN OVP_MSTR_PROJTYPE PT WITH (NOLOCK) ON PM.PROJ_TYPE_ID = PT.PROJ_TYPE_ID LEFT OUTER JOIN (SELECT SOURCE_ID, NEW_STAT_ID, MAX(STAT_CHANGE_ID) AS [MAX_ID] FROM OVP_STATUS WITH (NOLOCK) WHERE ENT_TYPE_ID = 3 GROUP BY SOURCE_ID, NEW_STAT_ID) MXP ON MXP.SOURCE_ID = PRJ.PROJ_ID AND MXP.NEW_STAT_ID = PRJ.PROJ_STAT_ID LEFT OUTER JOIN OVP_STATUS PH WITH (NOLOCK) ON MXP.MAX_ID = PH.STAT_CHANGE_ID LEFT OUTER JOIN OVP_PROV_EXCEPTION X ON PRV.PROV_NUM = X.PROV_NUM AND X.END_DTTM IS NULL AND X.DEFAULT_STAT_ID = 16 GROUP BY M.MARKET_NAME, C.CLAIM_PROJ_NUM, PRJS.STATUS_NAME, PT.PROJ_TYPE_NAME, DATEDIFF("D", GETDATE(), PH.CREATE_DTTM), PRV.PROV_NAME, PRV.PROV_NUM, PRV.PROV_IRS, PRVS.STATUS_NAME,-- CASE WHEN X.PROV_NUM IS NULL THEN DATEDIFF("D", GETDATE(), PRV.PROV_CREATE_DTTM)-- WHEN X.PROV_NUM IS NOT NULL AND X.CREATE_DTTM < PRV.PROV_CREATE_DTTM THEN DATEDIFF("D", GETDATE(), PRV.PROV_CREATE_DTTM)-- ELSE DATEDIFF("D", GETDATE(), X.CREATE_DTTM)-- END, UC.TOTAL_UNVALIDATED_CLAIMS, OK.CREATE_DTTM, AH.ASSIGNED_DATE ) SQL10GROUP BY SQL10.MARKET_PROJECT, SQL10.PROJECT_STATUS, SQL10.PROJ_TYPE_NAME, SQL10.DAYS_IN_LAST_PROJ_STAT, SQL10.PROV_NAME, SQL10.PROV_NUM, SQL10.PROV_IRS, SQL10.PROVIDER_STATUS, SQL10.TOTAL_UNVALIDATED_CLAIMS, SQL10.TOTAL_PROVIDERS, SQL10.OVP, SQL10.LOAD_DT, SQL10.OK_TO_ASSIGN_DT, SQL10.ASSIGNED_DATE, SQL10.DAYS_UNTIL_EXPIRE, SQL10.DASHBOARD_STATUS_LEVELORDER BY SQL10.MARKET_PROJECT, SQL10.PROJECT_STATUS, SQL10.PROV_NAMESat, 10 Nov 2012 09:56:42 GMTcnjsvision74