Dropping additional zeroes or numbers

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Dropping additional zeroes or numbers

Author

Topic

dougancil
Posting Yak Master

217 Posts

Posted - 2011-02-15 : 14:27:40

I have the following query:

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

into scratchpad7

FROM
ScratchPad5
GROUP BY
[ScratchPad5].EmployeeNumber,
sumhours
order by employeenumber asc

and when I run that query it produces a result which looks like this:

8474 21.385166 0.000

and what I'd like to do is just my result be to two decimal places, rounded up to the nearest minute (if possible) ... can someone offer a way to do that?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-15 : 14:36:25

Select EmployeeNumber, cast(TotalRegHours as Numeric(12,2) as TotalRegHours , cast(TotalOT as Numeric(12,2) as TotalOT
From scratchpad7

dougancil
Posting Yak Master

217 Posts

Posted - 2011-02-15 : 15:09:11

MIK,

your response has me confused. Is that From Scratchpad7 supposed to be into?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-16 : 09:20:21

quote:
Originally posted by dougancil

MIK,

your response has me confused. Is that From Scratchpad7 supposed to be into?

After running your query you need to use the above code to get the data you want

Madhivanan

Failing to plan is Planning to fail

dougancil
Posting Yak Master

217 Posts

Posted - 2011-02-16 : 11:18:11

Madhivanan,

Can I add that code to what I already have so that I don't have to run two queries?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-16 : 11:45:20

Try this

SELECT DISTINCT
[ScratchPad5].EmployeeNumber,
SUM( case when [sumhours]>40
THEN 40
ELSE cast([sumhours] as numeric(12,2))
END ) AS TotalRegHours,
SUM( case when [sumhours]>40
THEN cast([sumhours]-40 as numeric(12,2))
ELSE 0
END ) AS TotalOT

into scratchpad7

FROM
ScratchPad5
GROUP BY
[ScratchPad5].EmployeeNumber,
sumhours
order by employeenumber asc

Madhivanan

Failing to plan is Planning to fail

dougancil
Posting Yak Master

217 Posts

Posted - 2011-02-16 : 15:47:02

Madhivanan,

Thank you. That does the trick.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-18 : 09:56:15