I have created an NSQL query based Portlet which shows project financial information. In the Portlet there is a column for the sum of cost plan values for the current year. I am using the below query for getting the monthly cost sum, but the values are not populating accurately. For example, instead of 20000 I am getting 19999.999990. Can anyone help me on this?
select datediff(dd,TSL.START_DATE,TSL.FINISH_DATE)*TSL.SLICE, TSL.START_DATE,TSL.FINISH_DATEfrom niku.niku.FIN_PLANS fp inner join niku.niku.FIN_COST_PLAN_DETAILS fpd on fpd.PLAN_ID=fp.IDinner join niku.niku.ODF_SSL_CST_DTL_COST tsl on tsl.PRJ_OBJECT_ID=fpd.IDinner join niku.niku.INV_INVESTMENTS i on fp.OBJECT_ID=i.IDwhere YEAR(tsl.START_DATE)= YEAR(getdate())
and fp.PLAN_TYPE_CODE='forecast' and fp.IS_PLAN_OF_RECORD=1and i.CODE = '*********'
order by TSL.START_DATE
And the result set I am getting is,
The CostOfMonth values should be 15000, 20000, 10000 for Feb, Mar, Apr respectively.
It looks like the issue comes from lack of rounding. The slices hold a lot of information with high precision and when you query them you may need to get it to be less precise. Try the following and see if it works better for you:
select round(datediff(dd,TSL.START_DATE,TSL.FINISH_DATE)*TSL.SLICE,1), TSL.START_DATE,TSL.FINISH_DATE
from FIN_PLANS fp inner join FIN_COST_PLAN_DETAILS fpd on fpd.PLAN_ID=fp.ID
inner join ODF_SSL_CST_DTL_COST tsl on tsl.PRJ_OBJECT_ID=fpd.ID
inner join INV_INVESTMENTS i on fp.OBJECT_ID=i.ID
where YEAR(tsl.START_DATE)= YEAR(getdate())
and i.CODE = '*********'
This will allow for 1 decimal, you may change this to 0 or 2 depending on your needs.
Hope this helps -Nika