oracle query

anumoses
anumoses used Ask the Experts™
on
select ds.payroll_id as payroll_id,
       sum(ds.total_hours)/100 as sum_wk_hours
  from dept_staff ds, not_avail_reason nar
 where ds.schedule_date between  '27-may-2012' and '02-jun-2012'
   and ds.reason_code = nar.reason_code
   and nar.pay_hours_flag = 'Y'
   and payroll_id in ('ZZW0024403','ZZW0023632')
  group by payroll_id

PAYROLL_ID|SUM_WK_HOURS
ZZW0023632|28.75
ZZW0024403|32.25

I need to round it off 28.75 = 29
32.25 = 32

I have to use floor and ceil. How can I use both?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012
Commented:
first - don't do this...  '02-jun-2012'

avoid implicit conversions,    


second,  if you want to round,  use ROUND  not floor/ceil.
Most Valuable Expert 2011
Top Expert 2012
Commented:
but,  for academic solution (i.e. interesting, but don't do it)

assuming your values are always positive  you could do this...

floor(yourvalue + 0.5)

or

ceil(yourvalue - 0.5)

but.  use ROUND instead

Author

Commented:
thanks. Thought so.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial