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
 Error converting data type varchar to numeric.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Trininole
Yak Posting Veteran

USA
83 Posts

Posted - 12/10/2012 :  13:02:51  Show Profile  Reply with Quote
I keep getting this error message when I am running this query:
Msg 8114, Level 16, State 5, Line 25
Error converting data type varchar to numeric.

The error message points to this line:

select count(distinct appid) participants,



Here is the query below:

Use OSPR_WorkArea;

Declare @BeginDte Smalldatetime; --<<<< Enter the beginning time frame
Set @BeginDte = '07/01/2011'; --<<<< Enter date in this format: '04/01/2007'
Declare @EndDte Smalldatetime; --<<<< Enter the ending time frame
Set @EndDte = '06/30/2012'; --<<<< Enter date in this format: '04/30/2007'
drop table #temp
select left(@BeginDte,11) 'Begin Date',left(@EndDte,11) 'End Date'

--get all participants
select appid
into #temp
from DOLETA.dbo.wp_9002_20114 wp
where 1=1
and rgn = '01'
and Black = '1'
and Gender = '1'
and convert(smalldatetime,
convert(varchar(10),programparticipationdate, 112))
<= @EndDte
and (convert(smalldatetime,
convert(varchar(10),programexitdate, 112))
>= @BeginDte or programexitdate is null)
--get number of participants
select count(distinct appid) participants,
s2p.srvs2partx,
plx.partxPlaced,
ref.ref2svcs
from #temp tmp
,
(
--get number of services
select count(*) srvs2partx
from efmextract.dbo.serviceplan svp
join #temp tmp
on tmp.appid = svp.programappid
where 1=1
and svp.programid = 3
) s2p
,
(
--get number of placements
select count(*) partxPlaced
from efmextract.dbo.serviceplan svp
join #temp tmp
on tmp.appid = svp.programappid
and servicecode between '750' and '882'
and programid = 3
) Plx
,
(
select count(*) ref2svcs
from efmextract.dbo.serviceplan svp
join #temp tmp
on tmp.appid = svp.programappid
and servicecode in ('210','211')
and programid = 3
) Ref
group by
s2p.srvs2partx,plx.partxPlaced,ref.ref2svcs

--drop table #temp

Roger DeFour

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/10/2012 :  13:33:08  Show Profile  Reply with Quote
Won't this be enough

Use OSPR_WorkArea 

Declare @BeginDte Smalldatetime; --<<<< Enter the beginning time frame 
Set @BeginDte = '07/01/2011'; --<<<< Enter date in this format: '04/01/2007' 
Declare @EndDte Smalldatetime; --<<<< Enter the ending time frame 
Set @EndDte = '06/30/2012'; --<<<< Enter date in this format: '04/30/2007' 

 
Select appid,
Count(distinct wp.appid) participants,
Count(svp.*) srvs2partx ,
SUM(Case When servicecode between '750' and '882' then 1 Else 0 End) partxPlaced,
SUM(Case When servicecode in ('210','211') then 1 Else 0 End) ref2svcs,
from DOLETA.dbo.wp_9002_20114 wp
inner join efmextract.dbo.serviceplan svp on wp.appid = svp.programappid and svp.programid = 3
and rgn = '01'
and Black = '1'
and Gender = '1'
and programparticipationdate <= @EndDte 
and (programexitdate >= @BeginDte or programexitdate is null))
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.42 seconds. Powered By: Snitz Forums 2000