CODEWITHSUNDEEP
sqlsql-server
WSL346
WSL346

Reputation: 13

Query returning a zero when dividing in the select statement

When i try to divide the first nested select statement by the count of a column, the query returns a zero. When I replace the "/" with a ",", I receive two different numbers so the return value shouldn't be a zero. Could this have something to do with there being zeros in the data set? Any help would be appreciated

declare @hospitalfk int;
set @hospitalfk='1335'

declare @startdate date;
set @startdate='03/01/2014'

declare @enddate date;
set @enddate='02/28/2015'

declare @reportname varchar(50);
set @reportname='%Medicaid Billable Report%'

declare @metasectionname varchar(100);
set @metasectionname='Medicaid Primary'

select 
(
select count(iscoded)
from ope.ope.vwerali 
    where iscoded=1 
        and hospitalfk=@hospitalfk
        and reportdate between @startdate and @enddate
        and reportname like @reportname
        and metasectionname like @metasectionname
)
/count(iscoded)
from ope.ope.vwerali
    where hospitalfk=@hospitalfk
        and reportdate between @startdate and @enddate
        and reportname like @reportname
        and metasectionname like @metasectionname

Upvotes: 1

Views: 296

Answers (3)

WSL346
WSL346

Reputation: 13

I've got it returning the expected value I was looking for. Credit goes to Kevin Suchlicki. All I needed to do was change:

count(iscoded)

to

convert(float,nullif(count(iscoded),0))

Updated query 

declare @hospitalfk int;
set @hospitalfk='1335'

declare @startdate date;
set @startdate='03/01/2014'

declare @enddate date;
set @enddate='02/28/2015'

declare @reportname varchar(50);
set @reportname='%Medicaid Billable Report%'

declare @metasectionname varchar(100);
set @metasectionname='Medicaid Primary'

select 
(
select convert(float,nullif(count(iscoded),0))
from ope.ope.vwerali 
    where iscoded=1 and hospitalfk=@hospitalfk
        and reportdate between @startdate and @enddate
        and reportname like @reportname
        and metasectionname like @metasectionname
)
/convert(float,nullif(count(iscoded),0))
from ope.ope.vwerali
    where hospitalfk=@hospitalfk
        and reportdate between @startdate and @enddate
        and reportname like @reportname
        and metasectionname like @metasectionname

Upvotes: 0

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

declare @hospitalfk int;
set @hospitalfk='1335'

declare @startdate date;
set @startdate='03/01/2014'

declare @enddate date;
set @enddate='02/28/2015'

declare @reportname varchar(50);
set @reportname='%Medicaid Billable Report%'

declare @metasectionname varchar(100);
set @metasectionname='Medicaid Primary'

select 
(
    select CONVERT(NUMERIC(10,2),count(iscoded))
    from ope.ope.vwerali 
    where iscoded=1 
        and hospitalfk=@hospitalfk
        and reportdate between @startdate and @enddate
        and reportname like @reportname
        and metasectionname like @metasectionname
)
/CONVERT(NUMERIC(10,2),count(iscoded))
from ope.ope.vwerali
    where hospitalfk=@hospitalfk
        and reportdate between @startdate and @enddate
        and reportname like @reportname
        and metasectionname like @metasectionname

Upvotes: 0

M.Ali
M.Ali

Reputation: 69524

There is no need for two selects, simply use a Case statement for count.

Also I think you are trying to avoid a Zero 0 in divisor.

Also for date parameters pass date values as ANSI Date i.e YYYYMMDD

select count(CASE WHEN iscoded=1  THEN iscoded END) * 1.00
      / NULLIF(count(iscoded), 0) 
from ope.ope.vwerali 
    where  hospitalfk=@hospitalfk
        and reportdate between @startdate and @enddate
        and reportname like @reportname
        and metasectionname like @metasectionname

Upvotes: 1

Related Questions