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.
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
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.
blom My name is blom 8)
Quote from: blom0344 on 27 Nov 2011 10:45:07 AM
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
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?
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
Hi,
some how I made this possible to make it to next working day if its holiday or weekend.
see this code
Quote
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_
Quotedate_diff([com_dt_xhdswknds],[cr_dt_xhdswknds])
this is giving the error
QuoteRQP-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?
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?
Personally I would use Cognos functions instead of native ones (in most cases available), especially when they are thrown 'into the mix'
Quote from: blom0344 on 30 Nov 2011 09:57:49 AM
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()
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.