Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

CREATING A COLUMN TO RETURN THE MAX VALUE FOR EACH GROUP WITHIN A SUBQUERY Expand / Collapse
Author
Message
Posted Saturday, November 10, 2012 9:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 21, 2012 11:58 AM
Points: 4, Visits: 44
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

Post #1383379
Posted Monday, November 12, 2012 3:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:57 AM
Points: 2,854, Visits: 5,120
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1383579
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse