Re: Rounding in select statement

[Laura]

Not quite what you asked, but better if you can to store prices as longs.

 

trade:([]time:`time$();sym:`symbol$();price:`long$();size:`int$()); `trade insert(09:30:00.000;`a;1075;100); `trade insert(09:31:00.000;`a;1175;100); `trade insert(09:32:00.000;`a;1320;100); `trade insert(09:30:00.000;`b;10075;100); `trade insert(09:31:00.000;`b;10695;100); `trade insert(09:32:00.000;`b;12395;100);

 

Your query then becomes

 

q)select time,sym,price:%[;100] 10 xbar 5+trade`price from trade 
time       sym price 
---------------------- 
09:30:00.000 a 10.8 
09:31:00.000 a 11.8 
09:32:00.000 a 13.2 
09:30:00.000 b 100.8 
09:31:00.000 b 107 
09:32:00.000 b 124

 

If you want a general rounding function adapted for dollars stored as cents (or any price as 100ש

 

q)roundi:{%[;100]s xbar y+.5*s:10 xexp 2-x} 
q)select time,sym,price:roundi[1]price from trade 
time       sym price 
---------------------- 
09:30:00.000 a 10.8 
09:31:00.000 a 11.8 
09:32:00.000 a 13.2 
09:30:00.000 b 100.8 
09:31:00.000 b 107 
09:32:00.000 b 124

 

If you want formatted strings then the new internal function @sujoy13 mentions does the rounding for you.

 

q)select time,sym,price:-27!(1i;price%100) from trade 
time       sym price 
------------------------ 
09:30:00.000 a "10.8" 
09:31:00.000 a "11.8" 
09:32:00.000 a "13.2" 
09:30:00.000 b "100.8" 
09:31:00.000 b "107.0" 
09:32:00.000 b "124.0"