Date Difference

[johny.cbi]

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

[JGirl]

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

[blom0344]

If you cannot change the datetable, but have access to the framework you could build an SQL model object like:

Code Select Expand


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..