CREATING A COLUMN TO RETURN THE MAX VALUE FOR EACH GROUP WITHIN A SUBQUERY

  • 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

  • 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.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply