Clarity

Expand all | Collapse all

Trying to find adjusted time in a Query

  • 1.  Trying to find adjusted time in a Query

    Posted Oct 25, 2016 10:20 AM

    We are trying to find the Adjusted time in a query. We can find it if the Timesheet is not adjusted but are getting a null result for Timesheets that are Adjusted. Any insight would be appreciated. Our current query is:

    select r.full_name as consultant,
    ts.prresourceid,
    ts.prid as tsid,
    ts.prstatus,
    te.prid as teid,
    tp.prid as tpid,
    tp.prstart,
    tp.prfinish,
    s.slice_date,
    s.slice

    from prtimesheet ts
       left join srm_resources r
            on r.id = ts.prresourceid
       left join prtimeentry te
           on te.prtimesheetid = ts.prid
       left join prtimeperiod tp
          on tp.prid = ts.prtimeperiodid
       left join prj_blb_slices s
          on s.prj_object_id = te.prid

    where r.last_name = 'Cora'
    -- and ts.prstatus = 4
    -- and te.prid is not null and te.prid is not null
    order by tpid

     

    Thanks,

    Rich



  • 2.  Re: Trying to find adjusted time in a Query

    Posted Oct 25, 2016 10:33 AM

    Your join to prj_blb_slices needs to be a bit more detailed than just on the object id, you need the slice type (SLICE_REQUEST_ID) in there too or you might start picking up wrong data.

     

    But the prj_blb_slices table will only have data in it for the period defined in the slice setup though (for the Daily Time Entries slice) - perhaps you have no adjusted timesheets in that period?

     

    If you do not need the daily values and can just use the weekly totals, then you don't need to go to the slice tables at all, you can just pick-up the practsum from the prtimeentry record (will be in seconds though so need to divide the value to get hours or days).



  • 3.  Re: Trying to find adjusted time in a Query

    Posted Oct 25, 2016 12:55 PM

    Thanks David.

    I am getting data correctly if the Timesheet has not been adjusted.

    My problem is trying to find the data for adjusted timesheets so that it balances to the latest approved and posted entry. 

    Is the time (slice) field for adjusted Timesheets pulled from a different table?



  • 4.  Re: Trying to find adjusted time in a Query

    Posted Oct 25, 2016 01:56 PM

    No its all the same tables - the difference is the timesheet's prstatus (but you seem to have that commented out)

     

    NB that the adjusted timesheet doesn't appear (prstatus will still indicate posted) as "adjusted" until its replacement (the adjustment) itself has become posted - but all the data should be there in the tables/slices.



  • 5.  Re: Trying to find adjusted time in a Query

    Posted Oct 26, 2016 11:26 AM

    Timesheets from PPM

    Above are the Timesheets in PPM

    Here is the result of the Query

    Result of Query

    No data when the Timesheet is Adjusted.

    Thanks for sticking with me on this one.

    Cheers,

    Rich



  • 6.  Re: Trying to find adjusted time in a Query

    Posted Oct 26, 2016 11:33 AM

    All your adjusted timesheets appear to have zero hours in them - so they will not have any slice data in the prj_blb_slices table.

     

    If you remove that table from your SQL (so you just pick up the prtimesheet / prtimeentry records) you should see the structure of the data (the timesheet may not even have any timeentry records?). or put a load of outer joins on the timeentry and slice tables perhaps.

     

    EDIT : oops I see you do already have the outer (LEFT JOIN)  , perhaps its the implicit null values that you would get with the outer-joined columns that is causing the confusion  - I would break your SQL down a table at a time to understand why the query is not returning data (i.e. adding which table causes it not to return data).

     

    EDIT AGAIN : oops again - I'll try to actually read your post/data before replying this time  ; your data is showing the adjusted timesheet (eg the first row, for timesheet id 5070690, prstatus = 5), just that the timesheet is empty (no timeentry rows).

     

    [ If you think the timesheet should have rows, just with no time entered against it, then note that when a timeentry that contains zero hours is on a timesheet that is being POSTED, then the posting job removes the redundant timeentry ]



  • 7.  Re: Trying to find adjusted time in a Query

    Posted Oct 26, 2016 01:26 PM

    The Task has the Correct Actuals.

    Cora Task Detail

    I am trying to pull this same data from the underlying tables and not getting values when the Timesheet is adjusted.

    It has to be stored somewhere, just need the query to find it.

    Thanks so much for trying to help.

    Cheers,

    Rich



  • 8.  Re: Trying to find adjusted time in a Query

    Posted Oct 27, 2016 03:46 AM

    The task (assignment really) actuals will only reflect the POSTED actuals ; when an adjusted timesheet is adjusted by another timesheet the actuals on the original timesheet are removed from the assignment (and replaced with the new actuals).

     

    But as I'm trying to say (in my somewhat overcomplicated manner) above ; your original timesheet(s) are "empty" - there are no tasks/actuals on those timeheets at all. Bit confused now about exactly what you think you looking for?



  • 9.  Re: Trying to find adjusted time in a Query

    Posted Oct 27, 2016 03:35 PM

    Just trying to pull the data that matches the Actuals in the Task.

    Should be simple but not obviously I am missing something.

    We are doing this to provide a spreadsheet by month by contractor to accounting to balance to the monthly invoices from the vendors.

    It works fine when there is no adjustment to the timesheet which is about 80% of the time. So we just need to get the adjusted time by slice_date when the time is adjusted so we can put it into the correct month.



  • 10.  Re: Trying to find adjusted time in a Query

    Posted Oct 27, 2016 04:58 PM

    Some comments;

     

    1.The actuals we see against a task(assignment) will only ever relate to posted timesheets (if a posted timesheet subsequently gets adjusted, then the original hours are removed from the task(assignment)'s actuals and the new hours applied to it).

     

    2.The time slice for daily time entries (i.e. the one related to the timesheet's timeentry rows) only has a "window" of available data - if the timesheet contributing to the task(assignment) is outside that window of time then the data will not be present in prj_blb_slices, whatever the state of the timesheet is (posted, adjusted). The data might be in the posted actuals slice though - related to the assignment record - if I recall correctly that has a much larger window of data available by default.

     

    3.The example data you posted above ; the original (now adjusted) timesheets all looked empty to me ; no hours to see there anyway.

     

    4.If you don't care about the DAILY hours, and can live with just the WEEKLY totals, then all the total weekly data can be found on the PRTIMEENTRY record - practsum. That way you don't have to think about the slices at all.



  • 11.  Re: Trying to find adjusted time in a Query

    Posted Oct 31, 2016 09:25 AM

    Thanks. We need the daily hours so that we can line them up with Monthly Invoices from vendors. 

    1. Any insight into why is the query not getting the Posted Hours when the Timesheet is adjusted?

    2. I have a test that retrieves only Posted Time but does not get data when the Timesheet is adjusted. I am guessing I have a simple error in my query but I cannot see it.

    Again, thanks for an insight.

    Cheers,

    Rich



  • 12.  Re: Trying to find adjusted time in a Query

    Posted Oct 31, 2016 10:09 AM

    RichWolverton wrote:

     

    1. Any insight into why is the query not getting the Posted Hours when the Timesheet is adjusted?

    We seem to be going round in circles with this - not sure whether that because I'm fundamentally misunderstanding what you are asking (and meaning by "the adjusted timesheet" etc).

     

    In your example data that you posted earlier, as far I can see, the ADJUSTED timesheet(s) do not contain any hours ; so your query has nothing to report. The ADJUSTMENT timesheet (the one that has adjusted the original timesheet) is now POSTED and there is data for it.

     

    Do you want to post a cut down / different example to see if its clearer because I don't see any "missing data" in the example data you posted.



  • 13.  Re: Trying to find adjusted time in a Query

    Posted Oct 31, 2016 02:16 PM

    Thanks for trying to understand.

    When I run this query I am not getting the posted time from the PRJ_BLB_SLICES table if there is an adjustment to the timesheet.

     

    select r.full_name as consultant,
    ts.prresourceid,
    ts.prid as tsid,
    ts.prstatus,
    te.prid as teid,
    te.prtimesheetid,
    tp.prid as tpid,
    tp.prstart,
    tp.prfinish,
    s.slice_request_id,
    s.prj_object_id,
    s.slice_date,
    s.slice

    from prtimesheet ts --Time Sheet
    left join srm_resources r -- Resource
    on r.id = ts.prresourceid
    left join prtimeentry te -- Time Entry
    on te.prtimesheetid = ts.prid
    left join prj_blb_slices s -- Daily Slice Data
    on s.prj_object_id = te.prid
    left join prtimeperiod tp -- Time Period
    on tp.prid = ts.prtimeperiodid

    where r.last_name = 'Cora'
    and ts.prstatus = 4                     ---Posted Timesheets

    order by tp.prstart

    Results-  my expectation is that their should be results for all Time Periods since Cora's time sheet had adjustments POSTED. I am sure it is a small tweak to the query.

    CONSULTANTPRRESOURCEIDTSIDPRSTATUSTEIDPRTIMESHEETIDTPIDPRSTARTPRFINISHSLICE_REQUEST_IDPRJ_OBJECT_IDSLICE_DATESLICE
    Cora, Geraldo50521585078096450841255078096500005326-JUN-1603-JUL-16
    Cora, Geraldo50521585078095450841245078095500100103-JUL-1610-JUL-16
    Cora, Geraldo50521585078094450841235078094500200110-JUL-1617-JUL-16
    Cora, Geraldo50521585078093450841225078093500200217-JUL-1624-JUL-16
    Cora, Geraldo50521585078092450841215078092500200324-JUL-1631-JUL-16
    Cora, Geraldo50521585070695450719645070695500200431-JUL-1607-AUG-1655555507196401-AUG-168
    Cora, Geraldo50521585070695450719645070695500200431-JUL-1607-AUG-1655555507196405-AUG-168
    Cora, Geraldo50521585070695450719645070695500200431-JUL-1607-AUG-1655555507196404-AUG-168
    Cora, Geraldo50521585070695450719645070695500200431-JUL-1607-AUG-1655555507196402-AUG-168
    Cora, Geraldo50521585070696450719625070696500200507-AUG-1614-AUG-1655555507196208-AUG-168
    Cora, Geraldo50521585070696450719625070696500200507-AUG-1614-AUG-1655555507196212-AUG-168
    Cora, Geraldo50521585070696450719625070696500200507-AUG-1614-AUG-1655555507196211-AUG-168
    Cora, Geraldo50521585070696450719625070696500200507-AUG-1614-AUG-1655555507196210-AUG-168
    Cora, Geraldo50521585070696450719625070696500200507-AUG-1614-AUG-1655555507196209-AUG-168
    Cora, Geraldo50521585070697450761615070697500200614-AUG-1621-AUG-1655555507616115-AUG-168
    Cora, Geraldo50521585070697450761615070697500200614-AUG-1621-AUG-1655555507616119-AUG-168
    Cora, Geraldo50521585070697450761615070697500200614-AUG-1621-AUG-1655555507616118-AUG-168
    Cora, Geraldo50521585070697450761615070697500200614-AUG-1621-AUG-1655555507616117-AUG-168
    Cora, Geraldo50521585070697450761615070697500200614-AUG-1621-AUG-1655555507616116-AUG-168
    Cora, Geraldo50521585072446450756195072446500200721-AUG-1628-AUG-1655555507561922-AUG-168
    Cora, Geraldo50521585072446450756205072446500200721-AUG-1628-AUG-16
    Cora, Geraldo50521585072446450756195072446500200721-AUG-1628-AUG-1655555507561923-AUG-168
    Cora, Geraldo50521585072446450756195072446500200721-AUG-1628-AUG-1655555507561925-AUG-168
    Cora, Geraldo50521585072446450756195072446500200721-AUG-1628-AUG-1655555507561924-AUG-168
    Cora, Geraldo50521585072446450756195072446500200721-AUG-1628-AUG-1655555507561926-AUG-168
    Cora, Geraldo50521585072444450756175072444500200828-AUG-1604-SEP-1655555507561729-AUG-168
    Cora, Geraldo50521585072444450756175072444500200828-AUG-1604-SEP-1655555507561702-SEP-168
    Cora, Geraldo50521585072444450756175072444500200828-AUG-1604-SEP-1655555507561701-SEP-168
    Cora, Geraldo50521585072444450756175072444500200828-AUG-1604-SEP-1655555507561731-AUG-168
    Cora, Geraldo50521585072444450756175072444500200828-AUG-1604-SEP-1655555507561730-AUG-168
    Cora, Geraldo505215850728364500200904-SEP-1611-SEP-16


  • 14.  Re: Trying to find adjusted time in a Query

    Posted Nov 01, 2016 05:00 AM

    I would just look at the settings for the 55555 time slice - it just looks like that that is not set up to slice any data prior to 1st August? (and now since we have just moved into November that slice window might have moved forward to only slice data from 1st September?)

     

    (I think 'out of the box' that slice only has a 90 day window of time - this is what I was getting at with my comment #2 earlier)



  • 15.  Re: Trying to find adjusted time in a Query
    Best Answer

    Posted Nov 01, 2016 12:42 PM

    Got it!!!! Thanks for all the insight!

     

    select r.full_name as consultant,
    ts.prresourceid,
    ts.prid as tsid,
    ts.prstatus,
    te.prid as teid,
    te.prtimesheetid,
    tp.prid as tpid,
    tp.prstart,
    tp.prfinish,
    s.slice_request_id,
    s.prj_object_id,
    s.slice_date,
    s.slice

     

    from prtimesheet ts --Time Sheet
    left join srm_resources r -- Resource
    on r.id = ts.prresourceid
    left join prtimeentry te -- Time Entry
    on te.prtimesheetid = ts.prid
    left join prj_blb_slices s -- Daily Slice Data
    on s.prj_object_id = te.prid      ---<<<<<<<<<<< change this to ts.prid for the Timesheet. 
    left join prtimeperiod tp -- Time Period
    on tp.prid = ts.prtimeperiodid

     

    where r.last_name = 'Cora'
    and ts.prstatus = 4                     ---Posted Timesheets

    order by tp.prstart



  • 16.  Re: Trying to find adjusted time in a Query

    Posted Nov 01, 2016 05:57 PM

    RichWolverton wrote:

     

    left join prj_blb_slices s -- Daily Slice Data
    on s.prj_object_id = te.prid      ---<<<<<<<<<<< change this to ts.prid for the Timesheet. 

    If this is saying that you are using the prtimesheet.prid as a join into prj_blb_slices then that is wrong ; timeentries are sliced not timesheets (prtimesheet record has no time-scaled data on it, the prtimeentry contains the time-sclaed-values). Also, you need to provide the slice_request_id when you join to prj_blb_slices otherwise you may just pick up random (wrong) data.

     

    ::confused::

     

    (but if you are not posting your full SQL and are happy with your results, then just ignore me! )



  • 17.  Re: Trying to find adjusted time in a Query

    Posted Nov 02, 2016 08:47 AM

    As you pointed out, I was premature. Still trying to get a simple query to pull PRJ_BLB_SLICES data for one Resource for Posted Time.



  • 18.  Re: Trying to find adjusted time in a Query

    Posted Nov 02, 2016 09:10 AM

    as I've pointed out - a few times now - if the timesheet period is outside the time range configured in a daily-time-entries timeslice ; then you will not be able to do this in 'simple' SQL.



  • 19.  Re: Trying to find adjusted time in a Query

    Posted Nov 08, 2016 08:22 AM

    Mystery solved. When the query looks good check the data. We had the Time Slicing job failing and therefore the data in the PRJ_BLB_SLICES table was not correct. Once we fixed the Time Slicing job everything balances. Thanks for looking at this with me and offering suggestions.