Apply SQL transformation : conditional rounding

Question

Apply SQL transformation : conditional rounding

Mohamed El Hamrouni 231

Dear,

I would like for a specidified column ("PagesTot"), apply a rounding (to 100's) when the number of pages goes over 500. I use for this the "apply sql transformation" module.
SELECT PagesTot,
CASE
WHEN [PagesTot]>500 THEN round(PagesTot,100)
ELSE PagesTot
END AS PagesTot
FROM t1;
** What am I doing wrong as the rounding does not apply and I would like to keep the column name and the module is creating a new column name ("PagesTot(2)").

Thank for your hep.

Mohamed.

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Anurag Sharma 17,636

Hi @Mohamed El Hamrouni , welcome to Microsoft Q& forum.

Above statement is creating PagesTot(2) because you have used PagesTot twice in your select statement.

To apply the rounding correctly, you can try below query:

SELECT PagesTot,CASE  
WHEN [PagesTot]>500 THEN round(PagesTot,-2)  
ELSE PagesTot  
END AS PagesTotNew  
FROM t1;

Please note '-2' it is for 100. If we want 1000 we need to use '-3'. Refer to link for more details.

This query would round the number to closest 100. That means:

601-649 would return 600
650-699 would return 700

Please let me know if this helps or else we can discuss further on the same.

----------

If answer helps, please select 'Accept answer' as it could help other having similar queries.

Mohamed El Hamrouni 231 Reputation points

2021-01-25T09:45:20.07+00:00

Dear,

I copied-pasted your formula but it does not change anything in teh result. The new column has the same values. The conditions is not getting applied....
Do you have an idea why?

Regards,

Mohamed.
Anurag Sharma 17,636 Reputation points

2021-01-25T10:02:41.237+00:00
Thanks for reverting back. Would it be possible for you to provide the table script with some insert statements so I can try on exact values?

Meanwhile I tried creating a demo table and some values and was able to see the results properly. Please check below:

create table test(PagesTot int) insert into test select 450 union select 470 union select 490 union select 510 union select 530 union select 550 union select 570 SELECT PagesTot,CASE WHEN [PagesTot]>500 THEN round(PagesTot,-2) ELSE PagesTot END AS PagesTotNew FROM test;

First column is the original values and second column is after we applied the conversions.
Anurag Sharma 17,636 Reputation points

2021-01-27T06:05:56.15+00:00

@Mohamed El Hamrouni , just wanted to check if you still are facing the issue.
Mohamed El Hamrouni 231 Reputation points

2021-01-29T14:26:58.737+00:00

Hello Dear,

sorry for my late feedback. The issue is still there, I guess I am missing something...
What do you mean by table script? My table has been downloaded within csv format. Shall I provide a sample ?

Regards,

Mohamed.
Anurag Sharma 17,636 Reputation points

2021-02-01T05:41:19.153+00:00

Thanks for reverting back. By table script I mean to say you can generate create table script and some data in it (which I think you already did in csv format).
Mohamed El Hamrouni 231 Reputation points

2021-02-01T11:05:38.243+00:00

Ha finally it worked.

But how can I do to keep the modification within the same column ("PagesTot") and not creating a new one please?

Thank you.

Mohamed.
Anurag Sharma 17,636 Reputation points

2021-02-01T11:21:27.577+00:00
Glad it worked. Do you want to just do select on the table? if yes you can use the query like below by removing the first "PagesTot"

SELECT CASE WHEN [PagesTot]>500 THEN round(PagesTot,-2) ELSE PagesTot END AS PagesTotNew FROM test;

If this helps you can mark the answer as 'Accept Answer'

Share via

Apply SQL transformation : conditional rounding

0 additional answers

Your answer