Solved

ROUNDING PERCENTAGE SQL

  • 8 January 2021
  • 2 replies
  • 128 views

Userlevel 3
Badge +7

Does any one know how i can adjust the below SQL so that it rounds to the nearest whole number. i am trying to see 10% of records to count a month. Below is current SQL 

 

I have tried adding Round in the front but it wouldnt work

 

(CASE WHEN LAST_COUNT_DATE >trunc(to_date( '#START_OF_THIS_YEAR#', 'YYYY-MM-DD-HH24:MI:SS' )) THEN '0' ELSE '1' END) *.10

 

 

icon

Best answer by paul harland 8 January 2021, 03:05

View original

This topic has been closed for comments

2 replies

Userlevel 7
Badge +24

seems like you need a group by clause.

In the absence of that, each row is just going to return either 0 or 1; multiplied by 0.1 = 0.1.

You need group by contract and then sum & Round.

round(sum(CASE WHEN LAST_COUNT_DATE >trunc(to_date( '#START_OF_THIS_YEAR#', 'YYYY-MM-DD-HH24:MI:SS' )) THEN '0' ELSE '1' END) *.10 , 0)

Userlevel 3
Badge +7

Thank you not sure why that didn’t cross my mind you helped me one earlier using the same set up, thank you again