November 10, 2012 at 9:56 am
I 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,
MAX(SQL10.DASHBOARD_STATUS_LEVEL) AS [PROJECT_LEVEL],
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
) SQL10
GROUP 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_LEVEL
ORDER BY
SQL10.MARKET_PROJECT,
SQL10.PROJECT_STATUS,
SQL10.PROV_NAME
November 12, 2012 at 3:19 am
That 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 tables
2. 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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply