AnsweredAssumed Answered

NSQL parameter - how to use user defined multiple times in calculations?

Question asked by urmas on Jun 26, 2016
Latest reply on Jun 26, 2016 by Dave_3.0

Going back to

https://communities.ca.com/message/101033107#comment-101033107

Say you have something like this

(select   

niku.prtask.prprojectid pid,

niku.prassignment.prresourceid rid,

year(niku.prj_blb_slices.slice_date) Year,

month(niku.prj_blb_slices.slice_date) Month,

isnull(sum(niku.prj_blb_slices.slice),0) Actual_hours

from

niku.prj_blb_slices,

niku.prj_blb_slicerequests,

niku.prtask,

niku.prassignment

where

niku.prj_blb_slices.slice_date > dateadd(m,-1,(getdate()))

and niku.prj_blb_slices.slice_date.

for each TSV column.

 

If you wanted days instead of the hours which is unit in the slices you would have to use the conversion factor.

The conversion factor used by the system is the the hours_per_day of the standard calendar, which can be changed any time. If you wanted to do the conversion with what ever factor is current you would have to query for it.

That is

(Select hours_per_day from prcalendar where prid = (Select prcalendarid from prsite)

 

If you have multiple TSV how would run the query just once and use the results for converting multiple columns?

 

Would

 

AND (WHERE@PARAM:USER_DEF:FLOAT:CFACTOR@ = (Select hours_per_day from prcalendar where prid = (Select prcalendarid from prsite))

and then in the query

isnull(sum(niku.prj_blb_slices.slice),0) / CFACTOR

work or would it have to be something else?

Outcomes