Time periods excluding weekends

[sstengler]

I have a requirement for a report that needs to calc a time period in Days (now just simply subtracting the two needed dates). Is there a way to change the calc using the COGNOS Impromptu functions to exclude the weekend days in the total?
(i.e., if case took 5 calendar days to complete (Thurs through the next Mon.), it makes more business sense to show that it only took 3 by recognizing that the period spanned a weekend and subtracting those two days.)

I can kinda already see what would be involved, but I would like to see if anyone else has made this type of process (since I don't want to have to reinvent the wheel...).

My search continues...

Thanks!

Sam Stengler

 

[CognosProfessional]

If you have Oracle you might see this function:


Syntax: or_dayofweek (datetime_exp)
Returns the day of the week in datetime_exp as an integer in the range 1 - 7. The start day depends on the value of Oracle variable...

That would make it easy to eliminate #1 and #7 in your filter if 1 is Sunday and 7 is Saturday for example.

Do all your calculating to get your total, then, filter on :
day of week not in ('1', '7')

Syntax=datedaydiff(Starting_date, Ending_date)
Returns the number of days between two datetimes.

there might be a couple of other ways too....



CP

 

[sstengler]

Thanks for the info.

My apologies, but unless I'm missing something in your reply, this is only a small part of the solution.
Neither of these functions would really work by themselves or together to give me the correct number of days with the weekends (if any) subtracted/ignored/removed. The output of DayDateDiff() is in integer number of days, which the or_dayofweek() function wouldn't be able to work on.

And I can't directly filter using or_dayofweek() because my source data is a beginning date and an ending date without the list of days in-between. So, unless the end and/or begin date was either Sat or Sun, then the filtering wouldn't work. And it certainly wouldn't filter out the weekends in the middle of a multi-week period.

Again, my apologies, and I do appreciate the help...

Sam Stengler

 

[canhe]

hello sstengler,


You can try this!


(A) ((date-to-days-1990(Finish Time)) - (date-to-days-1990(Start Time))) -> which will give you the number of days between the two.


(B)divide this figure by 7 to find out the number of weeks and multiply this by 2(saturday and sunday) and subtract from (A) above


This should give you the number of days minus saturday and sunday. You may need to add 1 depending on if you want to include the Start date etc. The only problem you may get is in (A) it may give you 6.65 weeks and you have got to get the whole number 6, but this should get you going!!


canhe

 

[canhe]

sstengler,

to get the whole number for the number of weeks use the function round-down.

canhe

 

[CognosProfessional]

I was thinking of a separate column that you would calculate for the day of the week, and hide these extra columns that you don't need when done. It would use counting and grouping of other columns that you would hide also. But, you can do this other ways too as suggested to see if that works better for your situation...


CP