SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Subquery help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Briceston
Yak Posting Veteran

54 Posts

Posted - 07/16/2013 :  16:20:27  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3326 Posts

Posted - 07/16/2013 :  16:26:26  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

277 Posts

Posted - 07/16/2013 :  16:53:54  Show Profile  Reply with Quote
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 - 07/16/2013 :  16:59:05  Show Profile  Reply with Quote
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 - 07/16/2013 :  20:22:16  Show Profile  Reply with Quote
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;






Edited by - Briceston on 07/16/2013 20:23:24
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/16/2013 :  20:44:56  Show Profile  Reply with Quote
try this:


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)

Go to Top of Page

Briceston
Yak Posting Veteran

54 Posts

Posted - 07/17/2013 :  10:10:26  Show Profile  Reply with Quote
quote:
Originally posted by MuMu88

try this:


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)





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

547 Posts

Posted - 07/17/2013 :  11:50:17  Show Profile  Reply with Quote
You need one more ')'
quote:
Originally posted by Briceston

quote:
Originally posted by MuMu88

try this:


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)





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 - 07/17/2013 :  16:28:19  Show Profile  Reply with Quote
quote:
Originally posted by MuMu88

You need one more ')'
quote:
Originally posted by Briceston

quote:
Originally posted by MuMu88

try this:


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)





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

547 Posts

Posted - 07/17/2013 :  21:21:24  Show Profile  Reply with Quote
One more try:

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)


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 - 07/18/2013 :  10:14:59  Show Profile  Reply with Quote
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:

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)


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

547 Posts

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

Briceston
Yak Posting Veteran

54 Posts

Posted - 07/18/2013 :  11:58:55  Show Profile  Reply with Quote
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

547 Posts

Posted - 07/18/2013 :  20:36:09  Show Profile  Reply with Quote
We are almost there, here we will try to stuff '00' at the end of the string to make the cast operation happy...


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)

Go to Top of Page

bitsmed
Constraint Violating Yak Guru

277 Posts

Posted - 07/19/2013 :  16:54:35  Show Profile  Reply with Quote
Most likely, DiagnosisCode contains non-numeric characters.
Find them with this sql:

select *
  from factMedicalClaims
 where isnumeric(DiagnosisCode)<>1
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000