Data Base: oracle
I want to create a caluclated column for ship date and req ship date
I am using the following syntax
(_days_between ([ship Date],[Req Ship Date]))
but I want to exclude saturdays/sundays/holidays
for eg:- aug 11 is the req date, aug 8th is the shipped date
the difference between these two dates is 3 days
but answer should be 1 day ( Aug 9 and Aug 10 are holidays)
appreciated for your reply
If adding to the database structure is an option (assuming you are using a data mart type structure with a date dimension):
1. Create & populate a flag (WORKING_DAY_FLAG) in your date table which differentiates between working days (Y) and non-working days (N).
2. Create a sequence number (SEQ) in your database date table which increments each row where WORKING_DAY_FLAG = 'Y' (the dates need to be sorted in order first)
3. Bring the SEQ columns for each date into your model & publish
4. Subtract one SEQ from the other SEQ in the report to get the difference in days.
For example:
DAY DATE WORKING_DAY_FLAG SEQ
Thu Aug 07 Y 36
Fri Aug 08 Y 37
Sat Aug 09 N 37
Sun Aug 10 N 37
Mon Aug 11 Y 38
Tue Aug 12 Y 39
In your example, to get working days between Aug 11 - Aug 8, your SEQ cols would give you 38 - 37 = 1 day.
Hope this helps.
J
If you cannot change the datetable, but have access to the framework you could build an SQL model object like:
select temp.ddate,temp.rnum from
(select cdate as ddate,temp.row_number() over (order by cdate asc) as rnum
from datetable where
to_char(cdate,'dy') not in ('SUN','SAT') and
to_char(cdate,'MM/DD/YYYY') not in ('08/09/2008','08/10/2008',....................................)) temp
You need to hardcode the holidays though in the where-clause as they are irregular.
The Oracle row_number() function may work in Cognos if it is used in an inline view construction..