Clarity

Expand all | Collapse all

Monthly Cost Plan is not populating correctly from slice tables

  • 1.  Monthly Cost Plan is not populating correctly from slice tables

    Posted Feb 07, 2017 11:42 AM

    Hi,

     

    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_DATE
    from niku.niku.FIN_PLANS fp inner join niku.niku.FIN_COST_PLAN_DETAILS fpd on fpd.PLAN_ID=fp.ID
    inner join niku.niku.ODF_SSL_CST_DTL_COST tsl on tsl.PRJ_OBJECT_ID=fpd.ID
    inner join niku.niku.INV_INVESTMENTS i on fp.OBJECT_ID=i.ID
    where 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,

     

    CostOfMonthSTART_DATEFINISH_DATE
    15000.0000082/1/20173/1/2017
    19999.9999903/1/20174/1/2017
    9999.9999904/1/20175/1/2017

     

    The CostOfMonth values should be 15000, 20000, 10000 for Feb, Mar, Apr respectively.



  • 2.  Re: Monthly Cost Plan is not populating correctly from slice tables
    Best Answer

    Broadcom Employee
    Posted Feb 07, 2017 01:53 PM

    Hi Justine

     

     

    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 fp.PLAN_TYPE_CODE='forecast'

    and fp.IS_PLAN_OF_RECORD=1

    and i.CODE = '*********'

    order by TSL.START_DATE

     

    This will allow for 1 decimal, you may change this to 0 or 2 depending on your needs.

     

     

    Hope this helps -Nika



  • 3.  Re: Monthly Cost Plan is not populating correctly from slice tables

    Posted Feb 08, 2017 05:06 AM

    Thanks Nika.