| Need To Be Displayed NULL COLUMNS (merged 3) [message #432995] |
Sat, 28 November 2009 10:08  |
venkatesh.M.P
Messages: 37
Registered: February 2009
Location: india
|
Member |
|
|
I am unable to get the data of the employees who are not present for a day in a Month.If there is In or Out Swip its showing for the calender days "I NEED TO GET COMPLETE 30 DAYS WITH OR WITHOUT DATA"
Thanks in ADVANCE
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Need To Be Displayed NULL COLUMNS (merged 3) [message #433071 is a reply to message #432995] |
Sun, 29 November 2009 13:38  |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
We do not do IM speak here. u? That person is not here right now, call back later. Do you have Chef Boyardee in a can? You better let him out before he suffocates.
Now to your problem.
Do you know what outer join is?
Do you know what a calendar table is? There are many variations on this theme from simple, all the way up to data warehouse TIME dimension. Here is a very simple example.
SQL> create table calendar (thedate date,year number,month number,day number);
Table created.
SQL> insert into calendar values (to_date('01-jan-2009','dd-mon-rrrr'),2009,1,1);
1 row created.
SQL> insert into calendar values (to_date('02-jan-2009','dd-mon-rrrr'),2009,1,2);
1 row created.
SQL> -- ...
SQL>
SQL> insert into calendar values (to_date('31-jan-2009','dd-mon-rrrr'),2009,1,31);
1 row created.
So now join your data to the calendar table using outer join.
Good luck, Kevin
|
|
|
|
|
|
|
|
| Re: Need To Be Displayed NULL COLUMNS (merged 3) [message #433117 is a reply to message #433114] |
Mon, 30 November 2009 03:56 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
venkatesh.M.P wrote on Mon, 30 November 2009 10:43Thanks for your responce and other thing is its should not be manullay.When i run the report it should pic up the date from the system calendar and should print all the month dates with or without data
Michel Cadot wrote on Sun, 29 November 2009 11:57Answer already gave.
What did you try for what we gave you?
What did you find searching for "calendar"?
How does this not fit your requirements?
Or are you just waiting we provide you the final solution?
Regards
Michel
[Updated on: Mon, 30 November 2009 03:56] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Need To Be Displayed NULL COLUMNS (merged 3) [message #433157 is a reply to message #433146] |
Mon, 30 November 2009 06:38 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
To state it again, in smaller words.
1) You need a data source that will provide you with a record for each day of the month. @Kevin's Calendar table will do, but there are other, more flexible approaches
2) You take your query, and outer join it ,on a date column, to a query selecting every row from the Calendar, so that you still get a row from the calendar even if your query doesn't return a row for a specific date you still get a row back from the calendar.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
