Get Decimal Places for 10/7

user_9980229 0
Viewed 24

I am trying to make a Calculation where I want to update ideal hours based on number of Assistant and Head Coaches based on whether a restaurant is 5 day or 7 day a week. When I make a calculation where I use 10/7 or 20/7, it takes it as 1 and 2 respectively. I am currently using trunc but I have tried using cast :: decimal(10,6) etc and it doesn't work.

select
       a.entity,
       a.store_name,
       a.order_date,
       a.daily_ideal_hours,
       a.daily_ideal_hours - (case when b.days_open like '%Weekdays%' then ((c.total_acs*(20/5) + c.total_hcs*(10/5)))
                                   when b.days_open like '%All%' then ((c.total_acs * trunc(20/7,10)) + (c.total_hcs * trunc(10/7,10))) end) as updated_value
from scorecards_ideal_labor_hours as a
left join days_store_open as b
    on a.entity = b.entity
left join hc_ac_data as c
    on a.entity = c.entity_id
    and a.order_date = c.report_date
where a.order_date between '2021-12-06' and '2021-12-12'
and a.entity = 66
order by a.order_date desc;

How do I fix this?

amazon-redshift integer-division decimal sql
1 Answers

You don't need to use trunc function here. Just typecast both numerator and denominator to float. This will give the final answer with decimal places, without any rounding:
select (10::float)/(7::float);
This gives answer as: 1.4285714285714286

If you need to round the final answer to few digits only:
select trunc((10::float)/(7::float),2);

user_159488150
Related