| Including Non-Working Days in Report [message #381136] |
Thu, 15 January 2009 06:50  |
imran_nu
Messages: 55
Registered: February 2007
Location: Islamabad
|
Member |
|
|
Hi Experts
I have come across a situation which i hope already resolved by you. One of the table in my database keeps the date of original data and the receiving date and time of data. The data has the daily frequency except for the non-working days or holidays.
I have to generate a report containing the date of orginal data and receiving date of data.
The agenda to discuss is that how can i include the non-working date (day like SUNDAY) with NULL as receiving date. I don't receive the data of non-working days and there is no entry against them in the database.
Looking for your kind help / hint
|
|
|
|
| Re: Including Non-Working Days in Report [message #381140 is a reply to message #381136] |
Thu, 15 January 2009 07:20  |
 |
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Basically, you have a "calendar" table which stores certain information. Unfortunately (from your report's point of view), this calendar is not complete - Sundays and holidays (i.e. non-working days) are missing.
What you might do is to create your own calendar - it can be done using a simple query which is based on one of the row generator techniques, such as this quick and dirty SQL*Plus example:SQL> select generated_date,
2 to_char(generated_date, 'Day') day
3 from (select to_date('&&first_date', 'dd.mm.yyyy') + level - 1 generated_date
4 from dual
5 connect by level <= to_date('&end_date', 'dd.mm.yyyy')
6 - to_date('&&first_date', 'dd.mm.yyyy') + 1
7 );
Enter value for first_date: 24.12.2008
Enter value for end_date: 03.01.2009
GENERATED_DATE DAY
------------------- ---------
24.12.2008 00:00:00 Wednesday
25.12.2008 00:00:00 Thursday
26.12.2008 00:00:00 Friday
27.12.2008 00:00:00 Saturday
28.12.2008 00:00:00 Sunday
29.12.2008 00:00:00 Monday
30.12.2008 00:00:00 Tuesday
31.12.2008 00:00:00 Wednesday
01.01.2009 00:00:00 Thursday
02.01.2009 00:00:00 Friday
03.01.2009 00:00:00 Saturday
11 rows selected.
SQL>
So: one report query should create such a "virtual" calendar, while the second query would represent your real data. You'd join these two over the "date" column.
Or, you can do that even in a single query - row generator query would suit as an inline view.
However: if your Report Builder version doesn't support such things, you could use a (global temporary?) table which would get populated by a stored procedure (it would contain such a query); this procedure might be called from the AFTER PARAMETER FORM report trigger. You'd then use this table in a query just as if it was an ordinary table; though, you might need outer join.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
