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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Subquery help

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)AmountPaid
from factMedicalClaims
inner join
factMedicalClaimsDiagnoses
on(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
) C
INNER JOIN someothertable s ON s.memberid = c.memberid;
Go to Top of Page

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)AmountPaid
from factMedicalClaims
inner join
factMedicalClaimsDiagnoses
on(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 316
and floor(cast(DiagnosisCode as float)) not in (291,292,303,304,305)
)

Group by IncurredCCYYMM,MemberId;

Go to Top of Page

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.
Go to Top of Page

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 1
Error 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)AmountPaid
from factMedicalClaims
inner join
factMedicalClaimsDiagnoses
on(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 316
and floor(cast(DiagnosisCode as float)) not in (291,292,303,304,305)
)

Group by IncurredCCYYMM,MemberId;





Go to Top of Page

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 316
and floor(cast(STUFF(DiagnosisCode, 5, 1, '0')as float)) not in (291,292,303,304,305)

[/CODE]
Go to Top of Page

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 316
and 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 16
Incorrect syntax near ')'.
Go to Top of Page

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 316
and 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 16
Incorrect syntax near ')'.

Go to Top of Page

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 316
and 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 16
Incorrect 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.
Go to Top of Page

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 316
and 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.
Go to Top of Page

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)AmountPaid
from factMedicalClaims
inner join
factMedicalClaimsDiagnoses
on(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 316
and 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 1
Error converting data type varchar to float"

Below is the data set example of the diag code field:

281.0
281.2
283.2
290.0
291.1
290.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 316
and 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.

Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-18 : 10:45:16
what is the data type and data length of DiagnosisCode column?
Go to Top of Page

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?

Go to Top of Page

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 316
and cast(STUFF(DiagnosisCode, 5, 2, '00') as float) not in (291,292,303,304,305)

[/CODE]
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -