Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Briceston
Yak Posting Veteran
54 Posts |
Posted - 2013-07-16 : 16:20:27
|
Need some insight on how to write the below as a subquery???Code:Select IncurredCCYYMM, factMedicalClaims.MemberId,IsNull(SUM(ClaimCount),0) ClaimCount, COUNT(distinct MemberId)CountofMembers, SUM(ServiceCount)ServiceCount, Sum(UnitsAllowed)UnitAllowed, Sum(AmountPaid)AmountPaidfrom factMedicalClaimsinner joinfactMedicalClaimsDiagnoseson(factMedicalClaims.ClaimNum = factMedicalClaimsDiagnoses.ClaimNum)and(factMedicalClaims.SystemSourceCode = factMedicalClaimsDiagnoses.SystemSourceCode)Where BusinessUnitCode = 'EP'And ClaimPaidFlag = 'P'or IncurredCCYYMM Like '2012%'or DiagnosisCode Like '290.%'or DiagnosisCode Like '293.%'or DiagnosisCode Like '294.%'or DiagnosisCode Like '295.%'or DiagnosisCode Like '296.%'or DiagnosisCode Like '297.%'or DiagnosisCode Like '298.%'or DiagnosisCode Like '299.%'or DiagnosisCode Like '300.%'or DiagnosisCode Like '301.%'or DiagnosisCode Like '302.%'or DiagnosisCode Like '306.%'or DiagnosisCode Like '307.%'or DiagnosisCode Like '308.%'or DiagnosisCode Like '309.%'or DiagnosisCode Like '310.%'or DiagnosisCode Like '311.%'or DiagnosisCode Like '312.%'or DiagnosisCode Like '313.%'or DiagnosisCode Like '314.%'or DiagnosisCode Like '315.%'or DiagnosisCode Like '316.%'Group by IncurredCCYYMM,MemberId; |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-16 : 16:26:26
|
write it as a cte or a subquery like shown below:;WITH cte AS( YourQueryHere)SELECT * FROM cte c INNER JOIN someothertable s ON s.memberid = c.memberid;SELECT * FROM( YourQueryHere) CINNER JOIN someothertable s ON s.memberid = c.memberid; |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2013-07-16 : 16:53:54
|
Your long "where" sentence can be reduced, if DiagnosisCode is always numeric. See suggested correction below:quote: Originally posted by Briceston Need some insight on how to write the below as a subquery???Code:Select IncurredCCYYMM, factMedicalClaims.MemberId,IsNull(SUM(ClaimCount),0) ClaimCount, COUNT(distinct MemberId)CountofMembers, SUM(ServiceCount)ServiceCount, Sum(UnitsAllowed)UnitAllowed, Sum(AmountPaid)AmountPaidfrom factMedicalClaimsinner joinfactMedicalClaimsDiagnoseson(factMedicalClaims.ClaimNum = factMedicalClaimsDiagnoses.ClaimNum)and(factMedicalClaims.SystemSourceCode = factMedicalClaimsDiagnoses.SystemSourceCode)Where BusinessUnitCode = 'EP'And ClaimPaidFlag = 'P'or IncurredCCYYMM Like '2012%'or DiagnosisCode Like '290.%'or DiagnosisCode Like '293.%'or DiagnosisCode Like '294.%'or DiagnosisCode Like '295.%'or DiagnosisCode Like '296.%'or DiagnosisCode Like '297.%'or DiagnosisCode Like '298.%'or DiagnosisCode Like '299.%'or DiagnosisCode Like '300.%'or DiagnosisCode Like '301.%'or DiagnosisCode Like '302.%'or DiagnosisCode Like '306.%'or DiagnosisCode Like '307.%'or DiagnosisCode Like '308.%'or DiagnosisCode Like '309.%'or DiagnosisCode Like '310.%'or DiagnosisCode Like '311.%'or DiagnosisCode Like '312.%'or DiagnosisCode Like '313.%'or DiagnosisCode Like '314.%'or DiagnosisCode Like '315.%'or DiagnosisCode Like '316.%'or (floor(cast(DiagnosisCode as float)) between 290 and 316and floor(cast(DiagnosisCode as float)) not in (291,292,303,304,305) )Group by IncurredCCYYMM,MemberId;
|
|
|
Briceston
Yak Posting Veteran
54 Posts |
Posted - 2013-07-16 : 16:59:05
|
Thanks for the example, but I'm a newbie to SQL and do not fully understand the above. |
|
|
Briceston
Yak Posting Veteran
54 Posts |
Posted - 2013-07-16 : 20:22:16
|
I tried this suggestion and got the below error code.Msg 8114, Level 16, State 5, Line 1Error converting data type varchar to float.quote: Originally posted by bitsmed Your long "where" sentence can be reduced, if DiagnosisCode is always numeric. See suggested correction below:quote: Originally posted by Briceston Need some insight on how to write the below as a subquery???Code:Select IncurredCCYYMM, factMedicalClaims.MemberId,IsNull(SUM(ClaimCount),0) ClaimCount, COUNT(distinct MemberId)CountofMembers, SUM(ServiceCount)ServiceCount, Sum(UnitsAllowed)UnitAllowed, Sum(AmountPaid)AmountPaidfrom factMedicalClaimsinner joinfactMedicalClaimsDiagnoseson(factMedicalClaims.ClaimNum = factMedicalClaimsDiagnoses.ClaimNum)and(factMedicalClaims.SystemSourceCode = factMedicalClaimsDiagnoses.SystemSourceCode)Where BusinessUnitCode = 'EP'And ClaimPaidFlag = 'P'or IncurredCCYYMM Like '2012%'or DiagnosisCode Like '290.%'or DiagnosisCode Like '293.%'or DiagnosisCode Like '294.%'or DiagnosisCode Like '295.%'or DiagnosisCode Like '296.%'or DiagnosisCode Like '297.%'or DiagnosisCode Like '298.%'or DiagnosisCode Like '299.%'or DiagnosisCode Like '300.%'or DiagnosisCode Like '301.%'or DiagnosisCode Like '302.%'or DiagnosisCode Like '306.%'or DiagnosisCode Like '307.%'or DiagnosisCode Like '308.%'or DiagnosisCode Like '309.%'or DiagnosisCode Like '310.%'or DiagnosisCode Like '311.%'or DiagnosisCode Like '312.%'or DiagnosisCode Like '313.%'or DiagnosisCode Like '314.%'or DiagnosisCode Like '315.%'or DiagnosisCode Like '316.%'or (floor(cast(DiagnosisCode as float)) between 290 and 316and floor(cast(DiagnosisCode as float)) not in (291,292,303,304,305) )Group by IncurredCCYYMM,MemberId;
|
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-07-16 : 20:44:56
|
try this:[CODE]or (floor(cast(STUFF(DiagnosisCode, 5, 1, '0')as float)) between 290 and 316and floor(cast(STUFF(DiagnosisCode, 5, 1, '0')as float)) not in (291,292,303,304,305)[/CODE] |
|
|
Briceston
Yak Posting Veteran
54 Posts |
Posted - 2013-07-17 : 10:10:26
|
quote: Originally posted by MuMu88 try this:[CODE]or (floor(cast(STUFF(DiagnosisCode, 5, 1, '0')as float)) between 290 and 316and floor(cast(STUFF(DiagnosisCode, 5, 1, '0')as float)) not in (291,292,303,304,305)[/CODE]
Also, recieved an error on this code. Msg 102, Level 15, State 1, Line 16Incorrect syntax near ')'. |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-07-17 : 11:50:17
|
You need one more ')' quote: Originally posted by Briceston
quote: Originally posted by MuMu88 try this:[CODE]or (floor(cast(STUFF(DiagnosisCode, 5, 1, '0')as float))) between 290 and 316and floor(cast(STUFF(DiagnosisCode, 5, 1, '0')as float)) not in (291,292,303,304,305)[/CODE]
Also, recieved an error on this code. Msg 102, Level 15, State 1, Line 16Incorrect syntax near ')'.
|
|
|
Briceston
Yak Posting Veteran
54 Posts |
Posted - 2013-07-17 : 16:28:19
|
quote: Originally posted by MuMu88 You need one more ')' quote: Originally posted by Briceston
quote: Originally posted by MuMu88 try this:[CODE]or (floor(cast(STUFF(DiagnosisCode, 5, 1, '0')as float))) between 290 and 316and floor(cast(STUFF(DiagnosisCode, 5, 1, '0')as float)) not in (291,292,303,304,305)[/CODE]
Also, recieved an error on this code. Msg 102, Level 15, State 1, Line 16Incorrect syntax near ')'.
I added it, however, I'm still getting error: Msg 8114, Level 16, State 5, Line 3 Error converting data type varchar to float. |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-07-17 : 21:21:24
|
One more try:[CODE]or (floor(CAST(REVERSE(STUFF(REVERSE(DiagnosisCode), 1, 1, '0')) as FLOAT))) between 290 and 316and floor(CAST(REVERSE(STUFF(REVERSE(DiagnosisCode), 1, 1, '0')) as FLOAT)) not in (291,292,303,304,305)[/CODE]The above code works only if DiagnosisCode data format is [number].%, show us some representative sample data if the data format is different. |
|
|
Briceston
Yak Posting Veteran
54 Posts |
Posted - 2013-07-18 : 10:14:59
|
Select IncurredCCYYMM, factMedicalClaims.MemberId,IsNull(SUM(ClaimCount),0) ClaimCount,COUNT(distinct MemberId)CountofMembers, SUM(ServiceCount)ServiceCount,Sum(UnitsAllowed)UnitAllowed,Sum(AmountPaid)AmountPaidfrom factMedicalClaimsinner joinfactMedicalClaimsDiagnoseson(factMedicalClaims.ClaimNum = factMedicalClaimsDiagnoses.ClaimNum)and(factMedicalClaims.SystemSourceCode = factMedicalClaimsDiagnoses.SystemSourceCode)Where BusinessUnitCode = 'EP'And ClaimPaidFlag = 'P'or IncurredCCYYMM Like '2012%'or (floor(CAST(REVERSE(STUFF(REVERSE(DiagnosisCode), 1, 1, '0')) as FLOAT))) between 290 and 316and floor(CAST(REVERSE(STUFF(REVERSE(DiagnosisCode), 1, 1, '0')) as FLOAT)) not in (291,292,303,304,305)Group by IncurredCCYYMM,MemberId;I recieved an error on this as well. "Msg 8114, Level 16, State 5, Line 1Error converting data type varchar to float"Below is the data set example of the diag code field:281.0 281.2283.2290.0291.1290.3 quote: Originally posted by MuMu88 One more try:[CODE]or (floor(CAST(REVERSE(STUFF(REVERSE(DiagnosisCode), 1, 1, '0')) as FLOAT))) between 290 and 316and floor(CAST(REVERSE(STUFF(REVERSE(DiagnosisCode), 1, 1, '0')) as FLOAT)) not in (291,292,303,304,305)[/CODE]The above code works only if DiagnosisCode data format is [number].%, show us some representative sample data if the data format is different.
|
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-07-18 : 10:45:16
|
what is the data type and data length of DiagnosisCode column? |
|
|
Briceston
Yak Posting Veteran
54 Posts |
Posted - 2013-07-18 : 11:58:55
|
DiagnosisCode varchar(6)quote: Originally posted by MuMu88 what is the data type and data length of DiagnosisCode column?
|
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-07-18 : 20:36:09
|
We are almost there, here we will try to stuff '00' at the end of the string to make the cast operation happy...[CODE]or (cast(STUFF(DiagnosisCode, 5, 2, '00') as float)) between 290 and 316and cast(STUFF(DiagnosisCode, 5, 2, '00') as float) not in (291,292,303,304,305)[/CODE] |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2013-07-19 : 16:54:35
|
Most likely, DiagnosisCode contains non-numeric characters.Find them with this sql:select * from factMedicalClaims where isnumeric(DiagnosisCode)<>1 |
|
|
|
|
|
|
|