Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dougancil
Posting Yak Master
217 Posts |
 Posted - 2011-01-12 : 10:35:42
|
| 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 ascthat rounds to the nearest minute, but my data results look like this:8245 36.965166 .000000 36.9651668247 10.292000 .000000 10.2920008295 29.162833 .000000 29.1628338378 21.346000 .000000 21.3460008389 13.414333 .000000 13.4143338428 29.329666 .000000 29.3296668433 16.928833 .000000 16.9288338442 10.647500 .000000 10.6475008451 13.567500 .000000 13.5675008455 23.935166 .000000 23.935166what 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 line8245 36.965166 .000000 36.965166would be 8245 36.97 .000000 36.97Thank youDoug |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-01-12 : 15:01:32
|
Use ROUND function?
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
dougancil
Posting Yak Master
217 Posts |
Posted - 2011-01-19 : 12:52:44
|
| Peso,Thats exactly what I did:select name, employeenumber, dateadd(day,datediff(day,0,date),0) as dateonly,ROUND(cast (sum (ontime) as decimal (10,2)),2) as minutes into scratchpad2 from scratchpad1 where date between @payrollstartdate And @payrollenddategroup by employeenumber, name ,dateadd(day,datediff(day,0,date),0)order by employeenumber asc |
|
|
|
|
|
|
|
|
Rounding up to the nearest minute