Hai
I am converting sqr reports to Business Obects Reports. We have a table named Work Day in our database which has the date and num of working day fields since 1992. The num of working day excludes weekends and holidays. In my report I have two result date columns called Focsent and Focdue coming from a different table and I need to compute the num of workdays between those two days. ex:
If Focsent = ’ ’ than
begin_date = Focdue
end_date = current_date
num_work_days = Num_of_work_day for current_date(from work_day Table) - Num_of_work_day for Focdue(from work_day Table)
else than
begin_date = Focdue
end_date = Focsent
num_work_days = Num_of_work_day for Focsent(from work_day Table) - Num_of_work_day for Focdue(from work_day Table)
I have several dates in my report where I need to get the Num of working day for that particular date from the work day table.
If you are working with Oracle you may be able to do this in single query if I understand the problem correctly.
Problem :say you are getting Focdue from table T1 and Focsent from table T2 and you have a table WORKDAY with say two fields DATE and NUM_WORK_DAYS. I assume the current date is the system date.
Solution in ORACLE.In Designer
Create an alias for the WORKDAY table call it WORKDAY_BEGIN
Create two objects:
num_work_day_begin=select WORKDAY.NUM_WORK_DAYS where WORKDAY.DATE=decode(T2.FOCSENT, NULL, SYSDATE, T2.FOCSENT)
num_work_day_end=select WORKDAY_BEGIN.NUM_WORK_DAYS where WORKDAY_BEGIN.DATE=T1.FOCDUE
Bring these two objects in the report query and do a subtraction (2-1)
If you do not have ORACLE as your database then you will not be able to use decode, in SQL Server you have CASE statement which you can use; otherwise you may have to resort two a UNION of two queries. In one query you can select the workday.numworkday where workday.date=subquery(select currentdate where T2.FOCSENT is Null ) and the second query you can select the workday.numworkday where workday.date=subquery(select T2.FOCSENT where T2.FOCSENT is notnull). Of course you will still need the alias to get the other numworkday for the FOCDUE.
I hope this works
Manoj
______________________________ Reply Separator _________________________________
Author: Radhika Mandava radhika.mandava@WCOM.COM at Internet
Date: 7/21/98 10:19 AM
Hai
I am converting sqr reports to Business Obects Reports. We have a table named Work Day in our database which has the date and num of working day fields since 1992. The num of working day excludes weekends and holidays. In my report I have two result date columns called Focsent and Focdue coming from a different table and I need to compute the num of workdays between those two days. ex: