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
Best answer by paul harland
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)
+24seems 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)
Thank you not sure why that didn’t cross my mind you helped me one earlier using the same set up, thank you again
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
