Zero value

[Crystalguru]

I am having trouble with the following query NOT doing the division...

SELECT
c.PROPERTY_REGION
,c.property_id as property_id
, max(C.PROPERTY_EFF_DATE) as prop_eff_dt
, max(C.PROPERTY_EXP_DATE) as prop_exp_dt
, to_char(TS.DATE_DAY,'MM') as Date_Month
, to_char(TS.DATE_DAY,'YYYY') as Date_Year
, sum(TS.CANCELS) as cancels
, sum(TS.DENIES) as denies
, sum(TS.GUEST_TRAFFIC) as guest_traffic
, sum(TS.NEW_LEASES) as new_leases
, case when sum(TS.GUEST_TRAFFIC)=0 then 0 else
sum(TS.NEW_LEASES)-(sum(TS.CANCELS)+
sum(TS.DENIES))/sum(TS.GUEST_TRAFFIC)end as closing_ratio
FROM
COMMUNITIES C,
TIME_SERIES_BY_UNIT_TYPE TS
WHERE
C.PROPERTY_ID = TS.PROPERTY_ID AND
C.PROPERTY_REGION = 'Atlanta'
group BY
C.PROPERTY_REGION
,to_char(TS.DATE_DAY,'MM')
,to_char(TS.DATE_DAY,'YYYY')
,c.property_id

The results are giving me the New_Lease total when the cancel+denies is 0.

For example:
Cancel Denies New_leases Traffic Closing_ratio
0 0 7 25 7
It should be:
0 0 7 25 0.28

thanks

 

[swampBoogie]

You probably need to cast to decimal instead of using integer arithmetic.

case when sum(TS.GUEST_TRAFFIC)=0 then 0.0 else
sum(TS.NEW_LEASES)-(sum(TS.CANCELS)+
sum(TS.DENIES))/(sum(TS.GUEST_TRAFFIC) + 0.0) end as closing_ratio

 

[Crystalguru]

I tried your new statement and it's still happening. Here's some additional info.
If cancels and denies have a value then the closing ratio is correct.
For example
Cancel Denies New_leases Traffic Closing_ratio
4 1 24 34 23.8

If cancel or denies have 0 then it returns new_leases. strange.

 

[carp]

Is it possible you have NULL values slipping in? If so, then you won't get a percentage calculated, since math operations on NULL values typically return NULL.

 

[swampBoogie]

Looking a bit closer, don't you need an extra set of parenthesis?

case when sum(TS.GUEST_TRAFFIC)=0 then 0.0 else
(sum(TS.NEW_LEASES)-(sum(TS.CANCELS)+
sum(TS.DENIES)))/(sum(TS.GUEST_TRAFFIC) + 0.0) end as closing_ratio

 

[Crystalguru]

Nope, no null values, just zeroes.

Those silly ole parenthesis...they are going to drive me mad!!!

That took care of the problem.

thanks!