Clarity

Expand all | Collapse all

SQL for Duration Calculation

  • 1.  SQL for Duration Calculation

    Posted Mar 14, 2011 03:29 PM
    Can anyone provide the calculation for Duration in Clarity ?

    I need to calculate the duration for a set of tasks. We have added a custom field to tasks and I'm using it to calculate various metrics grouped by that field. One of the requirements is Duration in Business days (m-f) and excluding holidays if possible. I want to make sure I'm calculation my duration the same way Clarity does to avoid any issues.

    Does anyone know this calculation or where I could go to get it ? Providing the actual SQL would be awesome !!... but I will take what I can get.

    Thanks,
    Ben


  • 2.  RE: SQL for Duration Calculation

    Posted Mar 14, 2011 05:23 PM
    Hi Ben.
    For a task, PRTASK.PRDURATION is Business Workday Calendar cognizant. For a collection of tasks (with potential gaps or overlap) this gets a little trickier. Here's an old trick as well as a 12.1 twist on an old trick. I'm curious to hear other (easier? more robust?) approaches/tricks for this.
    Question to Answer: Based upon our business' work calendar in Clarity, how many business days are between dates X and Y?
    Starting Assumptions:
    1) Your System has an Admin user whose User ID is '1'
    SELECT * FROM SRM_RESOURCES
    WHERE ID = 1
    2) The Admin's Base Calendar is set to your organization's Workday Calendar (the calendar is current too :grin:).
    3) Admin has an Availability of 8.
    4) You know the start and finish dates you're testing for.
    5) You're staying within your slices.
    6) No one is monkeying with/making calendar exceptions for the Admin user.

    Basically we use the Admin user's availability from the time slices to tell us the 'legal' workdays. If the above assumptions are true, here are the workdays between Jan 1 2011 & April 1st 2011:
    SELECT (SUM(slice_data.slice)/8) AS business_days
    FROM 
    
    (SELECT s.prj_object_id AS resource_id 
    
    
    
    , s.slice_date 
    
    
    
    , s.slice 
    
     FROM
    prj_blb_slicerequests r 
    
    
    
    , prj_blb_slices s 
    
     WHERE r.id = s.slice_request_id 
    
     AND r.request_name = 'DAILYRESOURCEAVAILCURVE') slice_data 
    WHERE 
    slice_data.resource_id = 1
    AND slice_data.slice_date 
    BETWEEN '1/01/2011' and '4/01/2011'
    If you have 12.1, get to know the new report tables and views! These are all prefaced with 'RPT'.
    Here's the same code using a 'new in 12.1' aggregate reporting view:
    SELECT SUM(AVAIL_FTE) AS business_days
    FROM RPT_RES_D_AVAIL_FTE_V
    WHERE RESOURCE_ID = 1
    AND DAILY_START_DATE BETWEEN '1/01/2011' and '4/01/2011'
    How to test this (in your dev system of course):
    1) Make sure the Admin user is setup against your Business' Calender.
    2) Run Time Slicing.
    3) Once complete, run either of the above queries and log the results.
    4) Goto your Business' Workday Calendar in Clarity and make a couple workday exceptions between the constraining dates in the above WHERE clause.
    5) Run Time Slicing.... go grab some lunch. This one's gonna take awhile (you've just changed the global calendar - this'll need to replicate to all user slices associated with your Calendar). Once complete...
    6) Run the above queries again and see if the results match your expectations.

    Traps to watch for:
    1) Above examples are from the Dailys. The range you're testing for must be within the range of your slices (Dailys, Weekly, Monthlys, etc).
    2) Because this is slice based and slices roll, anything built using this approach is anchored real time at the time of viewing. You will not be able to datamine back in time past the limit of your slices. We use this approach for many "Business Days" or "Max Availability" between <Today> and <Deliverable or Milestone X>. Works great for near term metrics like this, might not be an appropriate solution to your 'historic performance measurement' problem.
    3) Careful with the 'BETWEEN'. Round up and down as appropriate.
    4) Careful with FINISH dates in Clarity. Use COP_CALC_FINISH_FCT function as appropriate.

    HTH,
    -R


  • 3.  RE: SQL for Duration Calculation

    Posted Mar 15, 2011 12:06 PM
    Rob,
    Thanks for this info. I was actually pondering something like this last night. When reading your traps to watch out for and the fact that this is based off of daily slices.... I don’t think this solution will work for what I'm trying to do. Our Daily time slices are set for +/- 365 days.... one year forward and one back. I looking to calculate durations for tasks that could be further forward or backward than one year.... for those projects that span several years.

    Is there any way to look at the corporate calendar itself, looking for work vs. non work flag, rather than going to the availability time slice ? I'm sure if there was you would have mentioned it .... but it can’t hurt to ask. Also, I don’t know the range on that calendar. I know as a company we roll out our holiday schedule only one year at a time. But I can work around that issue.

    I'm definitely open to any other solutions that folks may have.

    Thanks,
    Ben


  • 4.  RE: SQL for Duration Calculation

    Posted Mar 15, 2011 12:30 PM
    Ben I think you might have misread Rob's response (or maybe I have misinterpreted your response :wacko:)...

    Rob is saying that for existing TASKS then the PRDURATION field already contains the answer you are looking for.

    The further stuff Rob is talking about is for answering more general questions around "how many working days between A and B" (i.e. independent of a actual task).

    FWIW ; to get around the problem of identifying "business-days" outside the periods of my availability slice, I have (manually) created a table containing all my statutory holiday dates for the next 10 years, I then use that table and the dates not being a SAT or SUN to work out a number of business-days metric. Luckily I only have 1 calendar and SAT SUN are always non-business days for me, I dare-say the solution could be expanded to include other "locale" rules if I needed it to be though.


  • 5.  RE: SQL for Duration Calculation

    Posted Mar 15, 2011 12:42 PM
    Hi Dave,
    My immediate need is to calculate the duration for a set of tasks.... so first I have to determine the MIN(Startdate) and MAX(Finishdate) for the set of tasks.... then use those to calculate the workdays between. So that is why I cannot use the prduration field... as it is my understanding this is for an individual task.

    I will have to investigate creating a holiday table to see if we could do that as well. Our Official work week is Mon - Fri as well so I think I can calc that using SQL. We have multiple calendars for various holidays around the world but I think we could base this duration calc off our US calendar.. I don’t think we need to get super fancy.

    If you have any SQL for your business days metrics that you could share I would be interested to see it.

    Thanks,
    Ben


  • 6.  RE: SQL for Duration Calculation

    Posted Mar 15, 2011 01:21 PM
    'fraid the SQL for the business days was rather low-tech; I just inserted the records by hand one at a time (bespoke table so no problem there).


  • 7.  RE: SQL for Duration Calculation

    Posted Mar 15, 2011 02:19 PM
    This is a good brain twister.
    Ben, a quick answer to "where's my corporate calendar?", the calendars are kept in the BLOB and are not available in the datamodel (Do I smell an Idea here? 'Make calendars available in the datamodel'?).

    Dave's approach (having physical access to his business' calendar in the datamodel) is the best approach, but as he mentions, this has to be built and maintained.

    So, what are all the options?
    1) Create a table that is designed to meet your workday requirements (as Dave has done).
    2) Utilize slices to get workdays within slice limitations (like I've posted above).
    3) Here's a new one: Utilize available calendar tables in the Clarity database to get known weekdays within table limitations...

    From the Datamart tables:
    SELECT 
    COUNT(PERIOD_START_DATE) AS WEEKDAYS
    FROM NBI_DIM_CALENDAR_TIME
    WHERE HIERARCHY_LEVEL = 'DAY'
    AND DATEPART(weekday, PERIOD_START_DATE) NOT IN (1,7)
    AND (PERIOD_START_DATE BETWEEN '1/01/2007' AND '4/01/2007')
    From the 12.1 report tables
    SELECT
    COUNT(START_DATE) AS WEEKDAYS
    FROM RPT_CALENDAR
    WHERE PERIOD_TYPE = 'DAILY'
    AND DATEPART(weekday, START_DATE) NOT IN (1,7)
    AND (START_DATE BETWEEN '1/01/2007' AND '4/01/2007')
    4) Create and maintain a table that contains your business' calendar exceptions only, and for a given date range, use a combination of this and #3. Count of weekdays in period - count of exceptions in period = workdays.

    What else?


  • 8.  RE: SQL for Duration Calculation

    Posted Mar 15, 2011 03:47 PM
    Rob,
    Yes this is a bit of a brain twister... mine is hurting at this point.

    In your list of items below 1 & 2 will account for holidays but option 3 does not take into account holidays. Important difference to note.

    I'm using a different method at the moment that also does not take into account holidays.... Got this from the net... does pretty much the same as what you were doing without using the calendar tables.

    SELECT DATEDIFF(d,@StartDate,@EndDate)+1
    - (DATEDIFF(wk,@StartDate,@EndDate) + CASE WHEN DATEPART(dw,@StartDate)=1 then 1 else 0 End )
    - (DATEDIFF(wk,@StartDate,@EndDate) + CASE WHEN DATEPART(dw,@EndDate)=7 then 1 else 0 End )

    I'm open to other possibilities.... especially if it includes holidays !!!

    Thanks,
    Ben


  • 9.  RE: SQL for Duration Calculation

    Posted Mar 15, 2011 03:54 PM
    Rob,
    I did not see option 4 there at the bottom. This might be a good solution as well. Little less work to create a table of exclusions.... we only get 8 or 9 holidays a year I think so that would be pretty easy to do.

    Ben


  • 10.  RE: SQL for Duration Calculation

    Posted Mar 15, 2011 04:34 PM
    I like your approach - no table row countin'. This gets around the table limitations on #3 - this is the right way to do the 'weekday' piece.

    So what's the right way to maintain our calendar exceptions?
    - A stored procedure that can be run as a Clarity job that has a UI that allows the user to enter calendar exceptions?
    - An accompanying portlet that allows us to see the data that's already in the table?
    - A business process that reads "each time (year?) as I update my business calendars in Clarity, I run this job to update my calendar table as well."?

    These are the fist ideas off the top of my head, I'm open to others...


  • 11.  RE: SQL for Duration Calculation

    Posted Mar 16, 2011 06:03 AM
    Actually "my" solution is #4 in Rob's list not #1.

    JustSayin' :tongue

    --

    #5 call a web-service that tells you no. of days between A and B
    #6 write a database function that tells you no. of days between A and B


  • 12.  RE: SQL for Duration Calculation

    Posted Mar 20, 2011 01:48 PM
    Ideally our calendars and the calendar exceptions would be available to us in the datamodel OOTB (I'll open an idea for this). Since it isn't, consensus is we need to create and maintain this ourselves.

    Our Requirements are:
    - Must support multiple calendars
    - Must be easy to maintain

    Here's a possible solution: Create an object to manage this.
    Attributes:
    Calandar (tied to the OOTB Browse Calendars lookup)
    Year (4 digit string) - just for sorting and keeping things neat.
    Vacation Day (date) - the calendar exception. I know others (like in manufacturing) have scheduled downtimes as well as holidays (I plan to rename this holiday as I go from Dev to test), so call it whatchalike.
    Name (comes with the object)
    Notes (string)

    .


  • 13.  RE: SQL for Duration Calculation

    Posted Mar 20, 2011 01:52 PM
    ... and now, with this object in place, using Ben's SQL and Dave's #4 approach above...

    voilà - business workdays between two dates.
    SELECT 
    ((SELECT DATEDIFF(d,'1/1/2010','4/1/2010')+1
    - (DATEDIFF(wk,'1/1/2010','4/1/2010') + CASE WHEN DATEPART(dw,'1/1/2010')=1 then 1 else 0 End )
    - (DATEDIFF(wk,'1/1/2010','4/1/2010') + CASE WHEN DATEPART(dw,'4/1/2010')=7 then 1 else 0 End ))-
    
    (SELECT Count(ID)
    FROM ODF_CA_OSUMC_VAC_CALENDAR
    WHERE OSUMC_VACATION_DAY BETWEEN '1/1/2010' AND '4/1/2010'))
    My dirty secret on this dashboard is that the days are a raw date diff, not business days (like it ought to be). Good news is now I can fix it. :grin:

    Thanks guys for helping brain storm this out!


  • 14.  RE: SQL for Duration Calculation

    Posted Mar 20, 2011 02:32 PM
      |   view attached
    Oh heck. We've come this far - let's take it the last mile.

    Here's all of this wrapped up in a function...
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE FUNCTION [Niku].[z_CalcNumBusDays] (@StartDate datetime, @EndDate datetime)
    RETURNS INT
    AS
    BEGIN
        DECLARE @NUMBUSDAYS AS INT
        RETURN((SELECT DATEDIFF(d,@StartDate,@EndDate)+1
    - (DATEDIFF(wk,@StartDate,@EndDate) + CASE WHEN DATEPART(dw,@StartDate)=1 then 1 else 0 End )
    - (DATEDIFF(wk,@StartDate,@EndDate) + CASE WHEN DATEPART(dw,@EndDate)=7 then 1 else 0 End ))
    - (SELECT Count(ID) FROM ODF_CA_OSUMC_VAC_CALENDAR WHERE OSUMC_VACATION_DAY BETWEEN @StartDate AND @EndDate))
    
    END
    Here's how you use it:
    Select niku.z_CalcNumBusDays('1/01/2010','7/01/2010') as Business_Days
    I only have one calendar so I haven't put in the ability to specify the calendar into the function above. Since the custom object will allow us to associate to multiple calendars, one could easily either add a 3rd parameter to the function call and target the desired calender in the where clause or create a function per calendar as you need to use it.

    My brain says, in a multi-calendar environment the next layer of the onion will be to get the user's id and appropriate business days at runtime, but I don't have this problem to solve so I'm stopping here. If anyone has this problem to solve, please feel free to add to this thread.

    .


  • 15.  RE: SQL for Duration Calculation

    Posted Mar 21, 2011 09:40 AM
    Having a slow Sunday Rob? :grin:


  • 16.  RE: SQL for Duration Calculation

    Posted Dec 26, 2011 07:13 AM
    Thanks Rob, that was amazing, I had did some changes for more specific requirement but's great, thanks for the effort


  • 17.  RE: SQL for Duration Calculation

    Posted Dec 27, 2011 01:04 PM
    Thank you rayyan for confirming our efforts are still providing others value - it's great to see an old thread 'bounce back' like this.

    This is the beauty to the CA Clarity Global User Community and these forums: A spot to dig around and find where others have already solved problems you're hunting a solution for, or, if none found, a spot for us to collaborate to build a solution and continue to build out the knowledge.

    Keep paying it forward.


  • 18.  RE: SQL for Duration Calculation

    Posted Jan 26, 2012 10:22 AM
    Rob,
    I just reread/revisited this entire thread as I am facing this issue again. We are now considering enhancing the portlets we created that show hours to now convert the hours to FTE's !!! It's great to have all this info still out there. I wish the Idea to CA had been implemented already.
    Again thanks for all your hard work on this one and allowing me to brainstorm with you.

    Ben


  • 19.  RE: SQL for Duration Calculation

    Posted Mar 20, 2011 03:08 PM
    ... and we're in.

    Please Promote if you feel Enhanced Access to Base Calendar features in Clarity will provide value to your organization.

    [size=7] Click Here for Idea: Enhanced Access to Base Calendars features in Clarity.[size].


  • 20.  RE: SQL for Duration Calculation

    Posted Mar 21, 2011 10:42 AM
    Wow.... Rob... you have taken the ball and run with it on this one !!! I appreciate it. This goes way beyond my immediate need but I can see a use for it in the future for sure. I had to read this a few times to get my head around it. This goes beyond my abilities. I think for now my Portlet won’t take into account Holidays. My users are ok with that. When the day comes that this idea makes it into clarity we can enhance our Portlet to take holidays into consideration.

    Thanks again for going the extra mile !!!

    Ben


  • 21.  Re: SQL for Duration Calculation

    Posted Nov 16, 2016 11:56 AM

    Adding another tool to this toolkit. Here is a function (based off of this) that allows one to add 'Business Days' to a date.

    Our usage here is to calculate a finish date out of a start date & duration.

     

    CREATE FUNCTION Z_OSUWMC_ADD_BUSINESS_DAYS
    (   
        @WorkingDays As Int,
        @StartDate AS DateTime
    )
    RETURNS DateTime
    AS
    BEGIN
        DECLARE @Count AS Int
        DECLARE @i As Int
        DECLARE @NewDate As DateTime
        SET @Count = 0
        SET @i = 0

        WHILE (@i < @WorkingDays) --runs through the number of days to add
        BEGIN
    -- increments the count variable
            SELECT @Count = @Count + 1
    -- increments the i variable
            SELECT @i = @i + 1
    -- adds the count on to the StartDate and checks if this new date is a Saturday or Sunday
    -- if it is a Saturday or Sunday it enters the nested while loop and increments the count variable
    -- [RKE 11/16/2016] ... Or is an OSUWMC Holiday...
               WHILE DATEPART(weekday,DATEADD(d, @Count, @StartDate)) IN (1,7)
                 OR DATEADD(d, @Count, @StartDate) IN (SELECT holiday FROM ODF_CA_OSUMC_HOLIDAY)
                BEGIN
                    SELECT @Count = @Count + 1
                END
        END

    -- adds the eventual count on to the Start Date and returns the new date
        SELECT @NewDate = DATEADD(d,@Count,@StartDate)
        RETURN @NewDate
    END
    GO

     

    CREATE FUNCTION [Niku].[Z_OSUWMC_CALC_BUSINESS_DAYS] (@StartDate datetime, @EndDate datetime)
    RETURNS INT
    AS
    BEGIN
    DECLARE @NUMBUSDAYS AS INT
    RETURN((SELECT DATEDIFF(d,@StartDate,@EndDate)+1
    - (DATEDIFF(wk,@StartDate,@EndDate) + CASE WHEN DATEPART(dw,@StartDate)=1 then 1 else 0 End )
    - (DATEDIFF(wk,@StartDate,@EndDate) + CASE WHEN DATEPART(dw,@EndDate)=7 then 1 else 0 End ))
    - (SELECT Count(ID) FROM ODF_CA_OSUMC_HOLIDAY WHERE holiday BETWEEN @StartDate AND @EndDate))

    END

    GO