Clarity

Can anyone help me perform the following?  We use MS SQL 2005.  Looking to see if we can create a query to create a list of a resources detail 'planned allocations' with the segment detail... [left]   [left] I.E. [left]   [left] Project ID [left] Resource name [left] Planned Allocation segments [left] Start and end date and amount (%) [left] 

Please disregard... I have written the necessary statement.

I TCOnnery,  I'm facing the some problem you did. I'm trying to create a SQL query to provide the following data:  - Project ID- Resource ID- Detail Allocation Segment Start Date- Detail Allocation Segment Finish Date- % Allocation per Segment  Can you please help me with this one?  Thanks a lot!  Best regards,Rui

Rui, I'll check my notes and get back to you.  

For some reason  I am not having any luck finding my notes on this query.   If I get a chance to write a new one, I'll reply again.  Good luck.

In thread% Allocation in Team Tab

http://caforums.ca.com/ca/board/message?board.id=CAClarityGeneralDiscussion&thread.id=968&view=by_date_ascending&page=1  there are a number or allocation related queries, but none related to the segments.  Martti K.

I was trying to think about this problem the other week.. (when that other thread was popular)....  I came to the conclusion that the "segments" did not actually exist anywhere in the "relational" bit of the datamodel, they are created "on-the-fly" by the application from the BLOBS as and when it needs to display them (i.e. on the allocation screen or when you extract the team data through XOG).  Really, really, really unhelpful then!  Someone came up with the idea of running XOG processes to "crack" the allocation segments (into XML) and then querying that XML to get the individual segment information - that all sounds like it might work (very neat idea I thought), but I'm glad that I have not had to try to do it yet!!!  Dave. Message Edited by Dave on 18-09-2009 09:11 AM [left]

Hi,                 We can't find the %allocation value directly. Dave already told, that column value will be stored in BLOB. Please advise me,  if i wrong.  We can calculate the %allocation value for the following formula.                                     %Allocation = (allocated Hrs per Day for particular project)/Availability Hrs per day) * 100  Availabilty hrs will be stored in PRJ_BLB_SLICES_D_AVL table and Allocated Hrs will be stored in PRJ_BLB_SLICES_D_ALC and PRJ_BLB_SLICES_D_HARDALC based on soft and hard booking.    For Example,             1) Resource X availability is 8 hrs per day.         2) Resource X,  50% allocated for X Project, 75% allocated for Y  Project and 100% Allocated for Z Project.                     Allocated Hrs Per Day:                             X project : 4 hrs day , Resource X Availability : 8 hrs per day   ; % Allocation = (allocated Hrs per Day for particular project)/Availability Hrs per day) * 100 ; (4/8)*100 = 50%                            Y project :  6 hrs day , Resource X Availability : 8 hrs per day   ; % Allocation = (allocated Hrs per Day for particular project)/Availability Hrs per day) * 100 ; (6/8)*100 = 75%                            Z project :  8 hrs day , Resource X Availability : 8 hrs per day   ; % Allocation = (allocated Hrs per Day for particular project)/Availability Hrs per day) * 100 ; (8/8)*100 = 100%    Home --> Resource --> Allocation:  SELECT   SRM.UNIQUE_NAME "Resource ID", SRM.FULL_NAME "Resource Name",INV.CODE   "Project ID",INV.NAME "Investment Name",
INV_MGR.FULL_NAME "Investment Manager",INV_ROLE.FULL_NAME "Investment Role",BOOK_STATUS.NAME "Booking Status",
NVL(TEAM.PRAVAILSTART,INV.SCHEDULE_START)   "Allocation Start", NVL(TEAM.PRAVAILFINISH,INV.SCHEDULE_FINISH) "Allocation Finish",
CASE WHEN SRM.RESOURCE_TYPE Team --> Staff:  Soft Booking:    SELECT INV.CODE "Project ID", INV.NAME "Project Name",SRM.UNIQUE_NAME "Resource ID", SRM.FULL_NAME "Resource Name",
START_DATE "Allocation Start Date",END_DATE "Allocation End Date",SLICE "Allocated Hrs", AVAIL "Availability Hrs"
, CASE WHEN AVAIL 0 THEN (SLICE/AVAIL)*100 END "%Allocation" FROM PRTEAM TEAM, INV_INVESTMENTS INV, SRM_RESOURCES SRM,
(SELECT PRJ_OBJECT_ID, INVESTMENT_ID, RESOURCE_ID, MIN(SLICE_DATE) START_DATE,MAX(SLICE_DATE) END_DATE,MAX(SLICE) SLICE FROM PRJ_BLB_SLICES_D_ALC GROUP BY INVESTMENT_ID, PRJ_OBJECT_ID,
SLICE, RESOURCE_ID, TO_CHAR(SLICE_DATE,'Mon-YYYY') HAVING SLICE 0) ALLOC, (SELECT PRJ_OBJECT_ID, MAX(SLICE) AVAIL FROM PRJ_BLB_SLICES_D_AVL GROUP BY PRJ_OBJECT_ID) AVL
WHERE TEAM.PRPROJECTID = INV.ID AND TEAM.PRRESOURCEID = SRM.ID AND TEAM.PRID = ALLOC.PRJ_OBJECT_ID
AND TEAM.PRRESOURCEID = AVL.PRJ_OBJECT_ID ORDER BY INV.ID,START_DATE  Hard Booking:      SELECT INV.CODE "Project ID", INV.NAME "Project Name",SRM.UNIQUE_NAME "Resource ID", SRM.FULL_NAME "Resource Name",
START_DATE "Allocation Start Date",END_DATE "Allocation End Date",SLICE "Allocated Hrs", AVAIL "Availability Hrs"
, CASE WHEN AVAIL 0 THEN (SLICE/AVAIL)*100 END "%Allocation" FROM PRTEAM TEAM, INV_INVESTMENTS INV, SRM_RESOURCES SRM,
(SELECT PRJ_OBJECT_ID, INVESTMENT_ID, RESOURCE_ID, MIN(SLICE_DATE) START_DATE,MAX(SLICE_DATE) END_DATE,MAX(SLICE) SLICE FROM PRJ_BLB_SLICES_D_HARDALC GROUP BY INVESTMENT_ID, PRJ_OBJECT_ID,
SLICE, RESOURCE_ID, TO_CHAR(SLICE_DATE,'Mon-YYYY') HAVING SLICE 0) ALLOC, (SELECT PRJ_OBJECT_ID, MAX(SLICE) AVAIL FROM PRJ_BLB_SLICES_D_AVL GROUP BY PRJ_OBJECT_ID) AVL
WHERE TEAM.PRPROJECTID = INV.ID AND TEAM.PRRESOURCEID = SRM.ID AND TEAM.PRID = ALLOC.PRJ_OBJECT_ID
AND TEAM.PRRESOURCEID = AVL.PRJ_OBJECT_ID ORDER BY INV.ID,START_DATE    ThanksSenthil.                                     Message Edited by senthil on 09-21-2009 08:13 AM [left] Message Edited by senthil on 09-21-2009 08:16 AM [left]

Yes you are right, you can "work out" the %age for any given day...  The problem is that if you are trying to work out the different segments from that logic, you then need to add in the logic to work our the allocation over a whole range of days and then identify when that %age changes from one day to the next (i.e. you have hit the next "segment").  Altogether far too complicated in order to identify something that the application must be doing simply (from the BLOBs) as it just puts it up on the screen!

As readers will already know the wanted information is held within a "curve" structure understood by Clarity but not SQL.  Here's another (somewhat novel, and absolutely untested) idea. "Grab" the XOG xml for the team structure and store it in a custom table (perhaps temporary) then use XQuery to access the information you are seeking from that stored XML. (Segments should be revealed via the availTo/availFrom dates and the defaultAllocation should also be relevant.)
     
       
         
       
....
      This might involve writing a process (that can be run as a job, so not linked to an object) which could do the xog reads and sql inserts needed. This would be a 'batch style' solution but just might meet your needs.  Anyone willing to try it?