Cannot divide results of a subquery

[Tiger955]

Hi!

It seemed to be an easy thing in T-SQL to do, but I can't solve it:

There is a table tblEventAnmeldungen containing employee number (MaID)and Status ('OK', 'NO', 'XX') of registrations of Events (EvID).

I would like to count the number of registrations (not Status 'NO'), the number of Cancelations (Status 'XX') and then calculate a reliability-rate per employee (MaID).

I can count both columns'Anmeldungen' and 'AnmeldungenXX', but I cannot divide them!!
By the way, both columns can have a 0 result.

I tried (only with one MaID):

begin
declare 
@Anmeldungen int,
@AnmeldungenXX int,
@Reliability decimal(4,4);
set @Anmeldungen=(SELECT count(*) FROM tblEventAnmeldungen WHERE Status<>'NO' and tblEventAnmeldungen.MaID = 2)
set @AnmeldungenXX=(SELECT count(*) FROM tblEventAnmeldungen WHERE Status='XX' and tblEventAnmeldungen.MaID = 2)
set @Reliability=@AnmeldungenXX/@Anmeldungen
--isnull((SELECT count(*) FROM tblEventAnmeldungen WHERE Status = 'XX' and tblPersonal.MaID = tblEventAnmeldungen.MaID)-NULLIF((SELECT count(*) FROM tblEventAnmeldungen WHERE Status <> 'NO' and tblPersonal.MaID = tblEventAnmeldungen.MaID),0),0) as Reliability,
 
select @Anmeldungen as Anmeldungen, @AnmeldungenXX as AnmeldungenXX, @AnmeldungenXX/nullif(@Anmeldungen,0) as Reliability, @Reliability
end

The result set should be for example:

Anmeldungen AnmeldungenXX Reliability
6.................... 2................. 0.33

or when AnmeldungenXX=0
6................... 0.................. 1 (meaning 100%)

or when Anmeldungen=0 and of course AnmeldungenXX should be 0
0................... 0.................. 0

(the dots stand for blanks!)

Please help me in defining my stored procedure.

Thanks a lot

Michael

 

[namliam]

You cant devide by zero, basic maths rule
Basic solution if the devisor is 0 return 0

6/2 isnt 33% it is 25%, 6 good + 2 bad is 2/8 cancels, not 2/6

 

[Tiger955]

Hi namliam,

6/2 is 3, but 2/6 is 0,33 ! 0/0 is not possible, so the result should be 0, allthough you cannot calculate it.

Of course a reliability rate would be: 1-(2/6)= 0,66 (= 66%).
XX means canceled registration.

But my problem ist, that even 2/6 (AnmeldungenXX/Anmeldungen) in my script is 0, and I think the problem is the definition of the ouput variable and not the calculation itself.

So I need a code (most probable with case when....) and the correct definition of data types to solve the problem.

Michael

 

[namliam]

6/2 is 3, but 2/6 is 0,33 ! 0/0 is not possible, so the result should be 0, allthough you cannot calculate it.

Yes sorry 2/6 6/2 you should get the idea...
So allthough "we" cannot calculate it you expect the database to magicaly return a 0 ?? No you have to program around it...
Case when devisor = 0 then 0 else division end

Of course a reliability rate would be: 1-(2/6)= 0,66 (= 66%).
XX means canceled registration.

No, you have 2 cancellations and 6 actual signups, that is a total of 8 signups
6/8 reliability in my book or 1 - 2/8, both cases 75%

 

[Tiger955]

Yes sorry 2/6 6/2 you should get the idea...
So allthough "we" cannot calculate it you expect the database to magicaly return a 0 ?? No you have to program around it...
Case when devisor = 0 then 0 else division end


No, you have 2 cancellations and 6 actual signups, that is a total of 8 signups
6/8 reliability in my book or 1 - 2/8, both cases 75%

Althoug we discuss about calculations, my main problem is still there.

But I solved it meanwhile that way - and it works:

set @Reliability=case when (SELECT count(*) FROM tblEventAnmeldungen WHERE Status='XX' and tblEventAnmeldungen.MaID = @MaID) = 0 and
(SELECT count(*) FROM tblEventAnmeldungen WHERE Status<>'NO' and tblEventAnmeldungen.MaID = @MaID) = 0 then 0 
when
(SELECT count(*) FROM tblEventAnmeldungen WHERE Status='XX' and tblEventAnmeldungen.MaID = @MaID) = 0 and
(SELECT count(*) FROM tblEventAnmeldungen WHERE Status<>'NO' and tblEventAnmeldungen.MaID = @MaID) > 0 then 100 
else
100-(SELECT count(*) FROM tblEventAnmeldungen WHERE Status='XX' and tblEventAnmeldungen.MaID = @MaID)*100/(SELECT count(*) FROM tblEventAnmeldungen WHERE Status<>'NO' and tblEventAnmeldungen.MaID = @MaID) 
end 
return @Reliability

Michael

 

[namliam]

Case when devisor = 0 then 0 else division end

I did give you your answer to your problem twice now already

Though I still disagree with your logic, if it works for you it works, glad you were able to solve it without my help