AnsweredAssumed Answered

Fetch future timesheet data

Question asked by PragyaT on Nov 29, 2012
Latest reply on Nov 29, 2012 by another_martink
I have a portlet that displays the PTO balance fields such as
Carryover Hours, Floating Holidays and PTO
For each of these time off categories, it displays the opening balance, Hours Used and hours remaining for any resource.

The NSQL used in this portlet works fine for the year 2012. Now if I fill future timesheet (for the year 2013) for a resource in time off categories, these hours dont get reflected in slice table. (The timesheet status is "Posted").
But the slice table shows 0 hours for each day in the timesheet period.
Below is a sample query:

SELECT

res.id [resid],

sl.slice_Date,

sl.slice [Hours_Taken]
FROM

niku.srm_projects prj

JOIN niku.prTask tsk ON (prj.id = tsk.prProjectID AND prj.unique_name = '9022') -----(9022 -> Time off category)

JOIN niku.prAssignment asgn ON (tsk.prID = asgn.prTaskID)

JOIN niku.odf_ca_task odft ON tsk.prid=odft.id

JOIN niku.srm_resources res ON (asgn.prResourceID = res.id AND res.USER_ID = 5001027)

JOIN niku.prj_blb_slices sl ON (asgn.prID = sl.prj_object_id AND sl.slice_date BETWEEN CONVERT(datetime,'1/1/' + CONVERT(nvarchar,datepart(yyyy,'01/01/2013'))) AND CONVERT(datetime,'12/31/' + CONVERT(nvarchar,datepart(yyyy,'01/01/2013'))))


JOIN niku.prj_blb_slicerequests slRq ON (sl.slice_request_id = slRq.ID AND slRq.request_name IN ('DAILYRESOURCEACTCURVE') )


For the above resource, timesheet has been filled for the timeperiod 01/06/2013 - 1/12/2013 (40 hours in PTO time off category) and the timesheet status is posted.

But below is the output of the query (for the time period 01/06/2013 - 1/12/2013):
resid
slice_Date
Hours_Taken
5001035
2013-01-06 00:00:00.000
0.000000
5001035
2013-01-07 00:00:00.000
0.000000
5001035
2013-01-08 00:00:00.000
0.000000
5001035
2013-01-09 00:00:00.000
0.000000
5001035
2013-01-10 00:00:00.000
0.000000
5001035
2013-01-11 00:00:00.000
0.000000
5001035
2013-01-12 00:00:00.000
0.000000

Please let me know if I am missing anything

Outcomes