August 15, 2008 at 12:28 pm
Is there a function simliar to First Function in MS Access similar to MS SQL 2005? I tried using Top 1 in MS SQL, but the results are not the same. Below is what I have in Access and trying to translate it into MS SQL 2005.
SELECT Claims.SUBSC_LOCATION, Claims.SEQ_CLAIM_ID, Claims.MEDICAID_NO, Claims.LAST_NAME, Claims.FIRST_NAME, Claims.DATE_OF_BIRTH, Right([PLAN_CODE],3) AS Expr1, Claims.ORIG_EFFECTIVE_DATE, Claims.TERM_DATE, Claims.DIAGNOSIS_1, Claims.DIAGNOSIS_2, Claims.DIAGNOSIS_3, Claims.PRIMARY_SVC_DATE, Claims.CLAIM_THRU_DATE, Claims.PRIMARY_SVC_DATE, Claims.PROV_LAST_NAME, Claims.PROVIDER_ID, First(Claims.POST_DATE) AS FirstOfPOST_DATE, First(Claims.CHECK_DATE) AS FirstOfCHECK_DATE, First(Claims.CHECK_NUMBER) AS FirstOfCHECK_NUMBER, Sum(Claims.PAID_NET_AMT) AS SumOfPAID_NET_AMT
FROM Claims
GROUP BY Claims.SUBSC_LOCATION, Claims.SEQ_CLAIM_ID, Claims.MEDICAID_NO, Claims.LAST_NAME, Claims.FIRST_NAME, Claims.DATE_OF_BIRTH, Right([PLAN_CODE],3), Claims.ORIG_EFFECTIVE_DATE, Claims.TERM_DATE, Claims.DIAGNOSIS_1, Claims.DIAGNOSIS_2, Claims.DIAGNOSIS_3, Claims.PRIMARY_SVC_DATE, Claims.CLAIM_THRU_DATE, Claims.PRIMARY_SVC_DATE, Claims.PROV_LAST_NAME, Claims.PROVIDER_ID
HAVING (((Claims.DIAGNOSIS_1) Not Like "V3*") AND ((First(Claims.CHECK_DATE)) Is Not Null) AND ((Sum(Claims.PAID_NET_AMT))>0));
August 15, 2008 at 1:03 pm
There is no FIRST function in MSSQL.
You can use a CROSS APPLY to do this pretty easily, but it will generate a loop when it runs, so it may not perform so well depending on the amount of data.
You also need to order your data or FIRST does not mean anything and may easily be inconsistent in repeated executions of the query.
My syntax may be a bit off here, but you should get an idea from this:
[font="Courier New"]SELECT CLAIMS.SUBSC_LOCATION
,CLAIMS.SEQ_CLAIM_ID
,CLAIMS.MEDICAID_NO
,CLAIMS.LAST_NAME
,CLAIMS.FIRST_NAME
,CLAIMS.DATE_OF_BIRTH
,RIGHT([PLAN_CODE],3) AS EXPR1
,CLAIMS.ORIG_EFFECTIVE_DATE
,CLAIMS.TERM_DATE
,CLAIMS.DIAGNOSIS_1
,CLAIMS.DIAGNOSIS_2
,CLAIMS.DIAGNOSIS_3
,CLAIMS.PRIMARY_SVC_DATE
,CLAIMS.CLAIM_THRU_DATE
,CLAIMS.PRIMARY_SVC_DATE
,CLAIMS.PROV_LAST_NAME
,CLAIMS.PROVIDER_ID
,FC.POST_DATE AS FIRSTOFPOST_DATE
,FC.CHECK_DATE AS FIRSTOFCHECK_DATE
,FC.CHECK_NUMBER AS FIRSTOFCHECK_NUMBER
,SUM(CLAIMS.PAID_NET_AMT) AS SUMOFPAID_NET_AMT
FROM CLAIMS
CROSS APPLY (SELECT TOP 1 X.POST_DATE, X.CHECK_DATE, X.CHECK_NUMBER
FROM CLAIMS X
WHERE X.SUBSC_LOCATION = CLAIMS.SUBSC_LOCATION AND X.SEQ_CLAIM_ID = CLAIMS.SEQ_CLAIM_ID
AND X.MEDICAID_NO = CLAIMS.MEDICAID_NO AND X.LAST_NAME = CLAIMS.LAST_NAME AND X.FIRST_NAME = CLAIMS.FIRST_NAME
AND X.DATE_OF_BIRTH = CLAIMS.DATE_OF_BIRTH AND RIGHT(X.[PLAN_CODE],3) = RIGHT(CLAIMS.[PLAN_CODE],3)
AND X.ORIG_EFFECTIVE_DATE = CLAIMS.ORIG_EFFECTIVE_DATE AND X.TERM_DATE = CLAIMS.TERM_DATE AND X.DIAGNOSIS_1 = CLAIMS.DIAGNOSIS_1
AND X.DIAGNOSIS_2 = CLAIMS.DIAGNOSIS_2 AND X.DIAGNOSIS_3 = CLAIMS.DIAGNOSIS_3 AND X.PRIMARY_SVC_DATE = CLAIMS.PRIMARY_SVC_DATE
AND X.CLAIM_THRU_DATE = CLAIMS.CLAIM_THRU_DATE AND X.PRIMARY_SVC_DATE = CLAIMS.PRIMARY_SVC_DATE AND X.PROV_LAST_NAME = CLAIMS.PROV_LAST_NAME
AND X.PROVIDER_ID = CLAIMS.PROVIDER_ID
ORDER BY -SOMETHING-) FC
GROUP BY CLAIMS.SUBSC_LOCATION,CLAIMS.SEQ_CLAIM_ID,CLAIMS.MEDICAID_NO,CLAIMS.LAST_NAME,
CLAIMS.FIRST_NAME,CLAIMS.DATE_OF_BIRTH,RIGHT([PLAN_CODE],3),CLAIMS.ORIG_EFFECTIVE_DATE,
CLAIMS.TERM_DATE,CLAIMS.DIAGNOSIS_1,CLAIMS.DIAGNOSIS_2,CLAIMS.DIAGNOSIS_3,
CLAIMS.PRIMARY_SVC_DATE,CLAIMS.CLAIM_THRU_DATE,CLAIMS.PRIMARY_SVC_DATE,CLAIMS.PROV_LAST_NAME,
CLAIMS.PROVIDER_ID
HAVING (((CLAIMS.DIAGNOSIS_1) NOT LIKE "V3*")
AND (((FC.CHECK_DATE)) IS NOT NULL)
AND ((SUM(CLAIMS.PAID_NET_AMT)) > 0))
ORDER BY -SOMETHING-[/font]
August 15, 2008 at 1:49 pm
Thanks for the quick reply. I never heard of a Cross Apply until now. I have corrected the syntax as much as possible, but still get this error message:
Column 'FC.CHECK_DATE' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
I tried to add FC.CHECK_DATE' in the GROUP BY, but still won't work. Do think I can email or im you? You seem like a smart SQL type of person.
SELECT TXClaims.SUBSC_LOCATION
,TXClaims.SEQ_CLAIM_ID
,TXClaims.MEDICAID_NO
,TXClaims.LAST_NAME
,TXClaims.FIRST_NAME
,TXClaims.DATE_OF_BIRTH
,RIGHT([PLAN_CODE],3) AS EXPR1
,TXClaims.ORIG_EFFECTIVE_DATE
,TXClaims.TERM_DATE
,TXClaims.DIAGNOSIS_1
,TXClaims.DIAGNOSIS_2
,TXClaims.DIAGNOSIS_3
,TXClaims.PRIMARY_SVC_DATE
,TXClaims.CLAIM_THRU_DATE
,TXClaims.PRIMARY_SVC_DATE
,TXClaims.PROV_LAST_NAME
,TXClaims.PROVIDER_ID
,FC.POST_DATE AS FIRSTOFPOST_DATE
,FC.CHECK_DATE AS FIRSTOFCHECK_DATE
,FC.CHECK_NUMBER AS FIRSTOFCHECK_NUMBER
,SUM(TXClaims.PAID_NET_AMT) AS SUMOFPAID_NET_AMT
FROM TXClaims
CROSS APPLY (SELECT TOP 1 X.POST_DATE, X.CHECK_DATE, X.CHECK_NUMBER
FROM TXClaims X
WHERE X.SUBSC_LOCATION = TXClaims.SUBSC_LOCATION AND X.SEQ_CLAIM_ID = TXClaims.SEQ_CLAIM_ID
AND X.MEDICAID_NO = TXClaims.MEDICAID_NO AND X.LAST_NAME = TXClaims.LAST_NAME AND X.FIRST_NAME = TXClaims.FIRST_NAME
AND X.DATE_OF_BIRTH = TXClaims.DATE_OF_BIRTH AND RIGHT(X.[PLAN_CODE],3) = RIGHT(TXClaims.[PLAN_CODE],3)
AND X.ORIG_EFFECTIVE_DATE = TXClaims.ORIG_EFFECTIVE_DATE AND X.TERM_DATE = TXClaims.TERM_DATE AND X.DIAGNOSIS_1 = TXClaims.DIAGNOSIS_1
AND X.DIAGNOSIS_2 = TXClaims.DIAGNOSIS_2 AND X.DIAGNOSIS_3 = TXClaims.DIAGNOSIS_3 AND X.PRIMARY_SVC_DATE = TXClaims.PRIMARY_SVC_DATE
AND X.CLAIM_THRU_DATE = TXClaims.CLAIM_THRU_DATE AND X.PRIMARY_SVC_DATE = TXClaims.PRIMARY_SVC_DATE AND X.PROV_LAST_NAME = TXClaims.PROV_LAST_NAME
AND X.PROVIDER_ID = TXClaims.PROVIDER_ID
ORDER BY SEQ_CLAIM_ID) FC
GROUP BY TXClaims.SUBSC_LOCATION,TXClaims.SEQ_CLAIM_ID,TXClaims.MEDICAID_NO,TXClaims.LAST_NAME,
TXClaims.FIRST_NAME,TXClaims.DATE_OF_BIRTH,RIGHT([PLAN_CODE],3),TXClaims.ORIG_EFFECTIVE_DATE,
TXClaims.TERM_DATE,TXClaims.DIAGNOSIS_1,TXClaims.DIAGNOSIS_2,TXClaims.DIAGNOSIS_3,
TXClaims.PRIMARY_SVC_DATE,TXClaims.CLAIM_THRU_DATE,TXClaims.PRIMARY_SVC_DATE,TXClaims.PROV_LAST_NAME,
TXClaims.PROVIDER_ID
HAVING (((TXClaims.DIAGNOSIS_1) NOT LIKE 'V3%')
AND (((FC.CHECK_DATE)) IS NOT NULL)
AND ((SUM(TXClaims.PAID_NET_AMT)) > 0))
ORDER BY SEQ_CLAIM_ID
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply