Business Days Calculation in FM model

[jharmor]

My goal is to calculate the number of business days between two dates in a table modeled in Framework Manager.  The environment is Cognos 10.1.1 FP1 and DB2 9.5. I had my DBA create a calendar table with all days and a flag for business days and holidays. Then I had the DBA create a user defined function that has an input of two dates and returns an integer of the count of business days between the dates. I can see the user defined function in the explorer view under functions; however, I can't bring it into the model and use it nor can I call from Report Studio. So with plan A not working I tried plan B. Plan B was to model the primary table and create a column with a sub query to the calendar table and create my own calculation. When I try to create the column with the sub-query Cognos FM throws an error.  The sub query works when I run SQL directly against the DB but not in the model. How can I create a business days calculation in my framework model?

Cognos SQL – not working
Select
   ASGNMNT.CALLID,
   ASGNMNT.DATEACKNOW,
   ASGNMNT.DATEASSIGN,
   ASGNMNT.DATERESOLV,
   
                (Select Count(CALENDAR.CLNDR_SKEY)
                           From [HEAT].CALENDAR as CALENDAR
                           Where CALENDAR.CLNDR_CYMD  > ASGNMNT.DATEASSIGN
                                       And CALENDAR.CLNDR_CYMD  <= ASGNMNT.DATERESOLV
                                       And CALENDAR.WEEKDAY_IND = 'Y'
                                       And CALENDAR.HOLIDAY_IND = 'N') as DAYSTORESOLVE

From
   [HEAT].ASGNMNT as ASGNMNT

SQL to DB - works
Select HA.CALLID
, HA.HEATSEQ
, HA.DATEACKNOW
, HA.DATEASSIGN
, HA.DATERESOLV
, ( Select  Count(CLNDR_SKEY)

From HEAT.CALENDAR SC
Where SC.CLNDR_CYMD > HA.DateAssign
      and SC.CLNDR_CYMD <= HA.DateResolv
      and SC.weekday_ind = 'Y'
      and SC.holiday_ind = 'N' ) DaysToResolve

from HEAT.ASGNMNT HA

Thanks for any assistance.

[tjohnson3050]

On the query information tab, there is an options selection.  You can change the sql type to pass through, this should allow you to run the same statement that runs from your native sql tool.

[wyconian]

Hi

I was struggling with this for a long time.  The solution I came up with was to add a column to my calendar which identified the day time i.e work day, weekend,public holiday etc.  I assigned a value to each day type, 1 for work days, 0.5 to weekends (cause we work 1/2 days on weekends), 0 for public holidays etc.  Using a cross join I summed these values for each date in the calendar froom the calendar start date.  So each record in the calendar has the number of working days from the calendar start date.

When I need to calculate the number of working days between 2 dates in a report e.g start date and end date I take the subtract the values from the 'working days' column for each date.

was a bit of a pain to set up but it only needs to be calculated once during the initial calendar run

[JGirl]

Why not add a column to your date table that starts at 1 for the first business day, then increments by 1 for each subsequent business day (it doesn't reset over time, and is a meaningless number apart from to support this calculation), and do not increment for non-business days (ie a running count of business days).  Bring it through into your query subject as an attribute of the date, then when you want to count the working days between, if one day is business day # 450 and the other is #452 you can just subtract the numbers to get 2 business days - then depending on whether you want to count both days (an inclusive range) you might need to add 1.

Eg. Assuming M-F are business days and Sat & Sun are not
Sat 1/1 = 0
Sun 2/1 = 0
Mon 3/1 = 1
Tue 4/1 = 2
Wed 5/1 = 3
Thu 5/1 = 4
Fri 6/1 = 5
Sat 7/1 = 5
Sun 8/1 = 5
Mon 9/1 = 6

Business Days between Mon 9/1 and Thu 5/1 = 6 - 4 = 2 days (if you are looking for business days after the Thu), or 6 - 4 + 1 = 3 days if you want to count the inclusive number of days in the range.

[srinisid]

Thank you Mr. Lee for the solution!

I have implemented the above idea
http://www.cognoise.com/index.php/topic,28179.0.html