# Clarity

## Break PRTIMEENTRY Into Daily Totals

• #### 1.  Break PRTIMEENTRY Into Daily Totals

Posted Aug 21, 2013 02:06 PM
Hi. I've been working on this for over a week now, and desperately seeking some assistance.

So far I've come up with the following query, which seems to bring back all our Indirect Time from the PRTIMEENTRY table, by week. So one record would return 40 hours, if that person had 5 vacation days in the current week.

How can I break this down into daily detail? So that I could query an exact month, from the 1st to the 31st, without worrying that the 1st may fall mid week on an actual time sheet. I found a few related posts, but I can't make them work. Can somebody help me simplify it? Here is my current query:

SELECT S1.First_Name,S1.Last_Name, P2.PRNAME, P1.PRACTSUM, (P1.PRACTSUM / 3600) as TimeAlloted, P4.PRSTART,P4.PRFINISH FROM CLARITY.PRTIMEENTRY P1 Inner Join clarity.prchargecode P2 on
P1.PRCHARGECODEID = p2.PRID Inner Join Clarity.prtimesheet P3 on
p3.PRID = P1.PRTIMESHEETID INNER JOIN CLARITY.srm_resources S1 ON
S1.ID = P3.PRRESOURCEID INNER JOIN clarity.prtimeperiod P4 ON
P4.PRID = P3.PRTIMEPERIODID
WHERE P1.PRASSIGNMENTID is null

• #### 2.  RE: Break PRTIMEENTRY Into Daily Totals Best Answer

Posted Aug 21, 2013 03:04 PM
If you want daily numbers you can't get them from the timeentries.
You have to create time slices for timeentries and to get the data from the time slices.

See
100462597
99557135
2280108
2303313

for starters and search for more.

Martti K.

• #### 3.  RE: [CA Clarity General Discussion] Break PRTIMEENTRY Into Daily Totals

Posted Aug 22, 2013 06:48 AM
Hi Steeves,

You have to use the slice tables for getting the daily info.

JOIN PRJ_BLB_SLICES ON PRTIMEENTRY.PRID = PRJ_BLB_SLICES.PRJ_OBJECT_ID

And mention the slice id which you want to use depending on the data you need and then extract the hours .

Regards,
Supriya Chakraborty

From: CA Clarity Global User Community [mailto:CommunityAdmin@communities-mail.ca.com]
Sent: Wednesday, August 21, 2013 11:36 PM
To: mb.2277340.101626302@myca-email.ca.com
Subject: [CA Clarity General Discussion] Break PRTIMEENTRY Into Daily Totals

Hi. I've been working on this for over a week now, and desperately seeking some assistance.

So far I've come up with the following query, which seems to bring back all our Indirect Time from the PRTIMEENTRY table, by week. So one record would return 40 hours, if that person had 5 vacation days in the current week.

How can I break this down into daily detail? So that I could query an exact month, from the 1st to the 31st, without worrying that the 1st may fall mid week on an actual time sheet. I found a few related posts, but I can't make them work. Can somebody help me simplify it? Here is my current query:

SELECT S1.First_Name,S1.Last_Name, P2.PRNAME, P1.PRACTSUM, (P1.PRACTSUM / 3600) as TimeAlloted, P4.PRSTART,P4.PRFINISH FROM CLARITY.PRTIMEENTRY P1 Inner Join clarity.prchargecode P2 on
P1.PRCHARGECODEID = p2.PRID Inner Join Clarity.prtimesheet P3 on
p3.PRID = P1.PRTIMESHEETID INNER JOIN CLARITY.srm_resources S1 ON
S1.ID = P3.PRRESOURCEID INNER JOIN clarity.prtimeperiod P4 ON
P4.PRID = P3.PRTIMEPERIODID
WHERE P1.PRASSIGNMENTID is null
Posted by:shane.steeves
--
CA Communities Message Boards
101628842
mb.2277340.101626302@myca-email.ca.com<mailto:mb.2277340.101626302@myca-email.ca.com>
https://communities.ca.com

• #### 4.  RE: Break PRTIMEENTRY Into Daily Totals

Posted Aug 22, 2013 12:58 PM
Thanks for the help! It's slowly starting to come together for me. But from what I read and see, the Slices table doesn't contain Indirect time?

So now, how do I find indirect time by daily totals?

I find it strange why it's so difficult to query John Doe for a particular date, and want to see a full breakdown of his eight hour day, even if part of it was toward indirect time.

• #### 5.  RE: Break PRTIMEENTRY Into Daily Totals

Posted Aug 22, 2013 03:28 PM
The identifying factor is that indirect time is not associated with an assignment nor incidents.
See the below examples for a timesheet and the corresponding records in prtimeentry table.
Feel free to experiment more on that.
As you can see the timeentries in prtimeentry are in seconds.

Martti K.

.

• #### 6.  RE: Break PRTIMEENTRY Into Daily Totals

Posted Aug 23, 2013 04:15 AM

shane.steeves wrote:

Thanks for the help! It's slowly starting to come together for me. But from what I read and see, the Slices table doesn't contain Indirect time.
A custom slice on "Time entries" does contain indirect time (i.e. not the "stock" slices which are related to assignments, but the slices related to timeentries)

This custom daily "time entries" slice will create a PRJ_BLB_SLICE record for each DAY (SLICE_DATE) in a PRTIMEENTRY record; the PRJ_OBJECT_ID of the PRJ_BLB_SLICES record is the PRTIMEENTRY.PRID and you can just pick up the hours from the SLICE column.

(the other advantage of a timeentry slice over an actuals slice, is that the timeentry slice contains the numbers from the timesheet irrespective of the timesheet status - i.e. before posting)