SQL seems to round up the number automatically on select statement? -

SQL seems to round up the number automatically on select statement? -

hi here sql code:

select a."date", a."missed", b."total client schedules", cast(100-((a."missed"*100) / b."total client schedules")as decimal) "pct completed" - - ( - select date(scheduled_start) "date",count(*) "missed" - events node_name not null , status in ('missed') grouping date(scheduled_start) - ) a, - ( - select date(scheduled_start) "date", count(*) - "total client schedules" events node_name not null grouping date(scheduled_start) - ) b - a."date" = b."date" order "date" desc

and here output

date missed total client schedules pct completed ----------- ------------ ----------------------- -------------- 2013-02-20 2 805 100 2013-02-19 14 805 99 2013-02-18 29 805 97 2013-02-17 59 805 93 2013-02-16 29 806 97 2013-02-15 49 805 94 2013-02-14 33 805 96 2013-02-13 57 805 93 2013-02-12 21 805 98 2013-02-11 35 805 96 2013-02-10 34 805 96

it seems round highest number when want 99.99% or 97.2% etc..

you don't specify database using. however, databases integer arithmetic, 1/2 0 not 0.5.

to prepare this, create constants using numeric rather integer:

cast(100.0-((a."missed"*100.0) / b."total client schedules")as decimal)

it convert non-integer type arithmetic.

sql