November 14, 2012 at 8:19 am
Error Message:
Msg 164, Level 15, State 1, Line 1
Each GROUP BY expression must contain at least one column that is not an outer reference.
The SELECT statement in bold is where the error occurs. The statement should select the top DASHBOARD_STATUS_LEVEL within each MARKET_PROJECT and place this number in the PROJECT_LEVEL COLUMN. If I take out the GROUP BY, the statement simply copies the number in the DASHBOARD_STATUS_LEVEL to the PROJECT_LEVEL COLUMN for each record.
Example:
This is what should return
MARKET_PROJECT DASHBOARD_STATUS_LEVEL PROJECT_LEVEL
Austin T6-2011-03157 3 3
Austin T6-2011-03157 1 3
Austin T6-2011-03157 2 3
Dallas S8-2012-11111 1 2
Dallas S8-2012-11111 2 2
Query
SELECT
SQL10.MARKET_NAME,
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,
(SELECT TOP(1)SQL10.DASHBOARD_STATUS_LEVEL
GROUP BY SQL10.MARKET_PROJECT
ORDER BY SQL10.DASHBOARD_STATUS_LEVEL DESC) AS [PROJECT_LEVEL],
MIN(SQL10.DAYS_UNTIL_EXPIRE) AS [PROJECT_MINIMUM_DAYS_TO_EXPIRE]
FROM (
SELECT
M.MARKET_NAME,
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_NAME,
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.DASHBOARD_STATUS_LEVEL DESC
November 14, 2012 at 9:08 am
I take it the bolded part is the issue, ie this statement
(SELECT TOP(1)SQL10.DASHBOARD_STATUS_LEVEL
GROUP BY SQL10.MARKET_PROJECT
ORDER BY SQL10.DASHBOARD_STATUS_LEVEL DESC) AS [PROJECT_LEVEL],
Which makes no sense to me as
1) you are missing a FROM statement so how does the Sub query know where to get the data from?
2) The Column that is output is not in the group by or part of an Aggregation (MAX,MIN, SUM etc).
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 14, 2012 at 9:46 am
Sorry, I am quite new to SQL. TOP (1) is the aggregate. I am aware that the structure is incorrect (that's the problem). Because DASHBOARD_STATUS_ LEVEL is an alias field, should I restate the structure from which this field is derived?
(SELECT TOP(1)SQL10.DASHBOARD_STATUS_LEVEL
FROM CASE WHEN MIN(SQL1.DAYS_AGED) > 30 THEN '1'
WHEN MIN(SQL1.DAYS_AGED) > 15 THEN '2'
ELSE '3'
END AS [DASHBOARD_STATUS_LEVEL]
GROUP BY SQL10.MARKET_NAME+' '+SQL10.CLAIM_PROJ_NUM
ORDER BY SQL10.DASHBOARD_STATUS_LEVEL DESC) AS [PROJECT_LEVEL],
November 14, 2012 at 9:56 am
i've had that happen to me before;
you can change the database compatibility level (to 80?), and that will suppress the error.
alternatively, the error says what the issue is.
if you have a correlated sub query featuring a GROUP BY, 2008 and above requires at least one column to exist int he data INSIDE the sub query
in your example, grabbing top 1 from the outer query only, you'll never be able to satisfy it as is.
you could convert some of that into a CTE, so you can really select it the way SQL expects...
if you can, edit your original post and wrap it with [ code ] tags; to make it more readable and take up less web page real estate.
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply