round to nearest 50 - SQL Server Forums

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)

round to nearest 50

Author

Topic

blackX
Posting Yak Master

102 Posts

Posted - 2008-07-02 : 13:34:37

Is this possible?

my query looks like this but it only rounds to nearest 100

select round(currentannualdues,-2) from member where projectid = 'ttpoints' and contactstatus <> 'un-marketable c'

I am in a time crunch here. Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-02 : 14:01:04

quote:
Originally posted by blackX

Is this possible?

my query looks like this but it only rounds to nearest 100

select round(currentannualdues,-2) from member where projectid = 'ttpoints' and contactstatus <> 'un-marketable c'

I am in a time crunch here. Thanks

Is this?

select currentannualdues+50-currentannualdues%50 as currentannualdues from member where projectid = 'ttpoints' and contactstatus <> 'un-marketable c'

Madhivanan

Failing to plan is Planning to fail

blackX
Posting Yak Master

102 Posts

Posted - 2008-07-02 : 14:08:31

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-02 : 14:12:59

quote:
Originally posted by blackX

no

Can you post some sample data with expected result?

Madhivanan

Failing to plan is Planning to fail

blackX
Posting Yak Master

102 Posts

Posted - 2008-07-02 : 14:16:01

quote:
Originally posted by madhivanan

quote:
Originally posted by blackX

no

Can you post some sample data with expected result?

Madhivanan

Failing to plan is Planning to fail

Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals modulo, type equals money.

Original Should become
324.42 300
324.42 300
324.42 300
324.42 300
324.42 300
324.47 300
324.5 300
324.62 300
324.69 300
324.69 300
324.69 300
324.71 300
325.06 350
325.08 350
325.18 350
325.18 350

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-02 : 14:24:33

select case when currentannualdues%50<25 then currentannualdues-currentannualdues%50 else currentannualdues+50-currentannualdues%50 end from
(
select cast(currentannualdues as int) as currentannualdues from member where projectid = 'ttpoints' and contactstatus <> 'un-marketable c'
) as t

Madhivanan

Failing to plan is Planning to fail

blackX
Posting Yak Master

102 Posts

Posted - 2008-07-02 : 14:26:34

quote:
Originally posted by madhivanan

select case when currentannualdues%50<25 then currentannualdues-currentannualdues%50 else currentannualdues+50-currentannualdues%50 end from
(
select cast(currentannualdues as int) as currentannualdues from member where projectid = 'ttpoints' and contactstatus <> 'un-marketable c'
) as t

Madhivanan

Failing to plan is Planning to fail

just as i needed. This forum has helped me so much and I really appreciate it.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-07-02 : 14:29:16

[code]
select
a.num,
[Rounded] = (convert(int,a.num+25)/50)*50
from
(
--Test Data
select num = 324.99 union all
select num = 325.00
) a

Results:
num Rounded
------- -----------
324.99 300
325.00 350

(2 row(s) affected)

[/code]

CODO ERGO SUM

blackX
Posting Yak Master

102 Posts

Posted - 2008-07-02 : 14:29:35

one more thing, i need these grouped by the rounded number and the count of each rounded number

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-02 : 14:45:32

Select expression,count(expression) from table
group by expression

Madhivanan

Failing to plan is Planning to fail

blackX
Posting Yak Master

102 Posts

Posted - 2008-07-02 : 15:04:16

quote:
Originally posted by madhivanan

Select expression,count(expression) from table
group by expression

Madhivanan

Failing to plan is Planning to fail

that is what i did but the count was based off of the unrounded currentannualdues and is returned something like
0 1
0 7
50 1
50 1
50 2

and it shoud be 0 8
50 4

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-02 : 15:13:25

Use derived table

Select col,count(col) from
(
your query without group by
) as t
group by col

Madhivanan

Failing to plan is Planning to fail

blackX
Posting Yak Master

102 Posts

Posted - 2008-07-02 : 15:17:58

quote:
Originally posted by madhivanan

Use derived table

Select col,count(col) from
(
your query without group by
) as t
group by col

Madhivanan

Failing to plan is Planning to fail

this?

select case when currentannualdues%50<25 then currentannualdues-currentannualdues%50 else currentannualdues+50-currentannualdues%50 end, count(member_id) from
(
select cast(currentannualdues as int) as currentannualdues, member_id from member where projectid = 'ttpoints' and contactstatus <> 'un-marketable c'
) as t
group by currentannualdues order by currentannualdues

it produces the undesired results as above

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-02 : 15:28:38

[code]

--Mine

select currentannualdues,count(member_id) as counting from
(
select case when currentannualdues%50<25 then currentannualdues-currentannualdues%50 else currentannualdues+50-currentannualdues%50 end as currentannualdues from
(
select cast(currentannualdues as int) as currentannualdues,member_id from member where projectid = 'ttpoints' and contactstatus <> 'un-marketable c'
) as t1
) as t2
group by currentannualdues

--MVJ's

select currentannualdues,count(member_id) as counting from
(
select (currentannualdues+25)/50*50 as currentannualdues end from
(
select cast(currentannualdues as int) as currentannualdues,member_id from member where projectid = 'ttpoints' and contactstatus <> 'un-marketable c'
) as t1
) as t2
group by currentannualdues

[/code]

Madhivanan

Failing to plan is Planning to fail

blackX
Posting Yak Master

102 Posts

Posted - 2008-07-02 : 15:52:29

you the man

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources