creation_date to be modified to working day if its holiday or weekend day

[venkiatmaruthi]

Hi,

I have created_date column. I want this to be increased to next working day if that is public holiday or weekend day(Sat and Sunday).
I could make it for weekends but I struck with holiday on how to do for whole 5 days.

case day_of_week(creation_date)
when 6 then add_days(creation_date,2)
when 7 then add_days(creation_date,1)
else creation_date
end


Is it possible in cognos end or not? do we need to work at db side with user defined function?

I have a CorpHoliday table contains 2 names Holidaydate, Holidayname.



Thanks.
Venkat.

[blom0344]

You need a DB Calender table holding an indicator column for the holidays, especially as some are truly irregular (like Easter) It is fairly easy to build one in Excel, change where needed manually and upload the result to a table.

I can share a T-SQL script (through a PM; courtesy by 'someone else') that does make a very extensive table if you are interested

[venkiatmaruthi]

Thanks for replying bloom.

Please share that to my PM. I will be thankful to you. I thought I could make it at Cognos report studio level. I tried various ways. But failed.


Thanks.

[blom0344]

blom   My name is blom    8)

[MFGF]

blom   My name is blom    8)

Hee hee. If you had a Euro for every time your name was misspelled, you would be a very wealthy man, living in a huge mansion with a big garden with lots of flowers in bloom and no ties to a job... :D

[venkiatmaruthi]

Hi,

I need a temporary solution for it. My BA and Data modeler are not interested now to change the model. they are willing to do this in next phase all together.

I want this to be fixed at report level temporarily. Give me any ideas please.


I used if then else for 6 times. But that is not working and report is showing error.

if created_date in corpholiday table then
              if add_days(created_date,1) in corholiday table then
                      if add_date(created_date,2) in corpholiday table then
                           add_days(created_date,3)
                      else add_days(created_date,2)
              else add_days(created_date,1)
else created_date

The above code I kept in a query item named [cr_dt_xholidays]. This is working well.

I want to check now the weekend....

this is giving problem.
case when (day_of_week(cr_dt_xholidays)=6) then add_days(cr_dt_xholidays,2)
        when (day_of_week(cr_dt_xholidays)=7) then add_days(cr_dt_xholidays,1)
        else cr_dt_xholidays
end

can't I use the if then else in case block?


                       

[blom0344]

I think you may need to use 2 seperate queries. Your data table as master (all rows) and the holiday_table joined to it (make sure to define an outer join)

Then use the resulting 3rd query for the report. Through the join it should be possible to check the value of Holidaydate.
In the case first check  Holidaydate is not null --> add days appropriately
then apply further logic for the weekends like you are already doing now

[venkiatmaruthi]

Hi,

some how I made this possible to make it to next working day if its holiday or weekend.

see this code

case when (created_dt in (corpholiday.holiday) or day_of_week(created_dt,1) in (6,7)) then
                  case when (add_days(created_dt,1) in (corpholiday.holiday) or day_of_week(add_days(created_dt,1),1) in (6,7)) then
                                     case when (add_days(created_dt,2) in (corpholiday.holiday) or day_of_week(add_days(created_dt,2),1) in (6,7)) then
                                                        add_days(created_dt,3)
                                     else add_days(created_dt,2)
                                     end
                  else add_days(created_dt,1)
                  end
else (created_dt)
end

I kept in one data item [cr_dt_xhdswknds]
similary I have [completed_date]. I applied the same logic. I kept in data item [com_dt_xhdswknds]

Now I want the difference between these 2 data items.
In antoher data item named [No_of_days] I am wrote as below_

date_diff([com_dt_xhdswknds],[cr_dt_xhdswknds])

this is giving the error

RQP-DEF-0177: An error occurred while performing operation 'sqlPrepareWithOptions' status='-9'.

why is this error is coming. Does days_between() function not work for  calculated dataitems?

[pricter]

If you see the specification of the datadiff

datediff ( {datepart}, date_exp1, date_exp2 )
Returns the number of units indicated by datepart (day, month, year) between "date_exp1" and "date_exp2".

Did you use {datepart} in your expression?

[blom0344]

Personally I would use Cognos functions instead of native ones (in most cases available), especially when they are thrown 'into the mix'

[venkiatmaruthi]

Personally I would use Cognos functions instead of native ones (in most cases available), especially when they are thrown 'into the mix'

sorry.... I used days_between() function, not the datediff()

[venkiatmaruthi]

Hi,

another observation that I found is

[com_dt_xhdswknds] - [cr_dt_xhdswknds]

this is working fine and giving days interval like 0 days, 2 days 10 days...etc.

I am not able to convert this to integer.