Clarity

 View Only
Expand all | Collapse all

Query to find out the details of an Adjusted timesheet

  • 1.  Query to find out the details of an Adjusted timesheet

    Posted Feb 23, 2016 06:27 AM

    Hi All,

     

    I  need to find the comparison details between an adjusted and adjustment timesheets of a resource.

    Below is the detailed description -

     

    A resource has supposed filled 4 hrs each on 2 tasks from Mon to Friday and posted the timesheets. Now when queried, i got the posted hours for a particular task on a particular day. (complete 10 rows i got for that time period).

    Now when i go and adjust that time period - I removed the hrs from 1 task filled for Friday and added one more task and filled 4 hrs on it for Friday. Now when queried for the adjustment timesheet, i got the new timesheet details.(even the new task which has been added and it's filled hours). I am facing the challenge to get the details for the task that got removed.

     

    I have to provide the details where the hours has been changed or removed or added. I am getting the changed/added hours but not able to get the details for the removed one.

    Could you please help me on this?

     

    Thanks & Regards,

    Manisha



  • 2.  Re: Query to find out the details of an Adjusted timesheet

    Posted Feb 23, 2016 06:59 AM

    Without seeing your SQL, its a bit hard to GUESS, but...

     

    You are presumably JOINing between tasks on the (new) adjustment timesheet to tasks on the (old) adjusted timesheet - this is OK where the task is on the new timesheet and the old timesheet only.

     

    What I would think your query needs to look like would be more like a UNION of tasks on the new timesheet and tasks on the old timesheet  - you could probably just add a UNION'ed statement that picks up task on the old timesheet WHERE NOT EXISTS on the new timesheet.

     

    --

     

    Perhaps post the SQL you have if I have not made sense / given you enough clues?



  • 3.  Re: Query to find out the details of an Adjusted timesheet

    Posted Feb 23, 2016 07:01 AM

    What you are supposed to get are the timeentries on the original timesheet (status 5, there can be many with this status) and the entries on the posted adjustment sheet (status 4, only one with this status).

     

    You do not get the entries that were removed. You have to compare the adjusted and the adjustment to see which are not there and which were added. You can do that with SQL.



  • 4.  Re: Query to find out the details of an Adjusted timesheet

    Posted Feb 23, 2016 02:53 PM

    The new timesheet will get a new PRTIMESHEET entry and have a new status (something other than 5).  The original timesheet will have a status 5.

     

    So you'll need to compare 2 separate timesheets.



  • 5.  Re: Query to find out the details of an Adjusted timesheet

    Posted Feb 23, 2016 03:00 PM

    The original timesheet will have status 4 until the new one is posted and then the old one will have status of 5.



  • 6.  Re: Query to find out the details of an Adjusted timesheet

    Posted Feb 23, 2016 11:28 PM

    Hi,

    Yes, the old one gets a status 5 when the new one gets posted. But the difficulty here is when i am quering for the old timesheet, I am not getting the hours which are removed, which is quite strange. Without getting the original values, i can't compare with the new one to know which has been removed. Hence, i need a query to get the whole details of the old timesheet.

     

    Hope my question is now a bit more clear.

     

    Thanks,

    Manisha



  • 7.  Re: Query to find out the details of an Adjusted timesheet

    Posted Feb 24, 2016 12:55 AM

    When you say
    "the original timesheet is also not giving the hours which are removed from it"

     

    are you saying that your query does not return everything what you can see in the GUI on the original timesheet?



  • 8.  Re: Query to find out the details of an Adjusted timesheet

    Posted Feb 23, 2016 11:31 PM

    Hi,

     

    yes, but the original timesheet is also not giving the hours which are removed from it. So, it's becoming a bit challenging here.

     

    Thanks,

    Manisha



  • 9.  Re: Query to find out the details of an Adjusted timesheet

    Posted Feb 24, 2016 03:45 AM

    So your SQL is "wrong", not a lot anyone can say unless you post it so we can look at it!



  • 10.  Re: Query to find out the details of an Adjusted timesheet

    Posted Feb 25, 2016 11:56 PM

    Hi David,

    Yes, there was a small mistake in my SQL because of which i was not getting the original time entries.

    Thanks for your help.

    BR,

    Manisha



  • 11.  Re: Query to find out the details of an Adjusted timesheet

    Posted Feb 25, 2016 01:22 PM

    So I see lots of technical answers regarding writing a query, but I'm wondering if you really need to write a query.  If you have access to timesheets, simply go to the adjustment timesheet and navigate to the bottom right of the timesheet and click on delta view.  The resulting view is a line by line comparison between the original posted (adjusted) timesheet to the revised posted (adjustment) timesheet.  Perhaps using the delta view will save you time and give you the required answer.  Hope this comment is helpful.