Rounding up to the nearest minute

[dougancil]

I have the following query:

select name, employeenumber, dateadd(day,datediff(day,0,date),0) as dateonly,(cast (sum (ontime) as decimal (10,2))) as minutes into scratchpad2
from scratchpad1
where date between '10/3/2010' and '10/10/2010'
group by employeenumber, name ,dateadd(day,datediff(day,0,date),0)
order by employeenumber asc

that rounds to the nearest minute, but my data results look like this:

8245 36.965166 .000000 36.965166
8247 10.292000 .000000 10.292000
8295 29.162833 .000000 29.162833
8378 21.346000 .000000 21.346000
8389 13.414333 .000000 13.414333
8428 29.329666 .000000 29.329666
8433 16.928833 .000000 16.928833
8442 10.647500 .000000 10.647500
8451 13.567500 .000000 13.567500
8455 23.935166 .000000 23.935166

what I would like is to have the sums rounded to the nearest minute with only 2 decimal places instead of the 6 that I have. Can someone please assist?

So for example, this line

8245 36.965166 .000000 36.965166

would be

8245 36.97 .000000 36.97

Thank you

Doug

[Rabbit]

You mean like the ROUND function?

[dougancil]

I guess that would be it. Where would I put that in my query?

[Rabbit]

You put it around the number you want to round.

ROUND(cast (sum (ontime) as decimal (10,2)), 2)

[dougancil]

I'm sorry I was looking at the wrong query. This is the one I need to have the ROUND function in:

SELECT DISTINCT [ScratchPad5].EmployeeNumber, Sum([ScratchPad5].sumhours),
SUM(Case when [sumhours]>40
THEN [sumhours]-40
ElSE 0
END) AS TotalOT,
SUM(Case when [sumhours]>40
THEN 40
ELSE [sumhours]
End) AS TotalRegHours

From Scratchpad5
Group By
[Scratchpad5].Employeenumber,
Sumhours

Where would it go here?

Thanks

[Rabbit]

You would do the same thing I did in my earlier example. Put it around the number you want to round.

ROUND(expression, # of decimal digits)

[dougancil]

So something like this?

SELECT DISTINCT [ScratchPad5].EmployeeNumber, Sum([ScratchPad5].sumhours),
Round(SUM(Case when [sumhours]>40,2)
THEN [sumhours]-40
ElSE 0
END) AS TotalOT,
SUM(Case when [sumhours]>40
THEN 40
ELSE [sumhours]
End) AS TotalRegHours

From Scratchpad5
Group By
[Scratchpad5].Employeenumber,
Sumhours

[Rabbit]

Not quite, your case and sum statements extend beyond the first line. You'll want to bring the end of the round statement to the end of the sum statement.

[dougancil]

Ok, I'm obviously missing where this goes, I've tried a few places and can't seem to place it correctly.

Should it go inside the SUM statement or outside?

[Rabbit]

Outside the SUM, your SUM extends multiple lines. Didn't you write the original SQL? If you wrote the SUM and CASE statements, you should know where they end.