Home » SQL & PL/SQL » SQL & PL/SQL » attendence register (oracle 10g V2)
attendence register [message #434742] Thu, 10 December 2009 08:30 Go to next message
Messages: 420
Registered: May 2008
Senior Member

Thanks for your help all the time.

I have a problem, could you pls help me to find a solution for this.

I have a table say 'A'

create table a (id varchar2(10),date_time date);

insert into a values('A',to_date('12/1/2009','mm/dd/yyyy'));

insert into a values('A',to_date('12/2/2009','mm/dd/yyyy'));

insert into a values('A',to_date('12/4/2009','mm/dd/yyyy'));

insert into a values('A',to_date('12/6/2009','mm/dd/yyyy'));

insert into a values('A',to_date('12/8/2009','mm/dd/yyyy'));

insert into a values('A',to_date('12/10/2009','mm/dd/yyyy'));

insert into a values('B',to_date('12/1/2009','mm/dd/yyyy'));

insert into a values('B',to_date('12/20/2009','mm/dd/yyyy'));

as you see the table 'a' has data for the dates 1 dec,2 dec ,4 dec ,6 dec and 10 of december, 2009 for the id 'A'.
and 1,20 dec 2009 for the id 'B'

I have to generate the following output.
I have to take the whole month of december and should display flag (Y|N) for the each date in december by considering the table 'a' has data for a 'id' column.

out put is like below.

id dec1 dec2 dec3 dec4 dec5 dec6..... ....dec31
A Y Y N Y N Y.... .... N
B Y N N N N N... .... N

above, id, dec1,dec2...dec 31 are the columns.

Please help me on this.


Re: attendence register [message #434743 is a reply to message #434742] Thu, 10 December 2009 08:37 Go to previous messageGo to next message
Messages: 25148
Registered: January 2009
Location: SoCal
Senior Member
Nice homework assignment.

SEARCH this forum for "calendar" and for "pivot".
Re: attendence register [message #434744 is a reply to message #434742] Thu, 10 December 2009 08:38 Go to previous messageGo to next message
Messages: 12478
Registered: September 2008
Location: Rainy Manchester
Senior Member
You'll need to use a row generator to get the list of dates and then it's a standard pivot to get your output.
Re: attendence register [message #434821 is a reply to message #434742] Fri, 11 December 2009 00:26 Go to previous messageGo to next message
Messages: 9
Registered: August 2008
Junior Member
select id, max(decode(date_time,to_date('12/1/2009','mm/dd/yyyy'),'Y','N')) as "Dec 1" ,
max(decode(date_time,to_date('12/2/2009','mm/dd/yyyy'),'Y','N')) as "Dec 2" ,
max(decode(date_time,to_date('12/3/2009','mm/dd/yyyy'),'Y','N')) as "Dec 3" ,
max(decode(date_time,to_date('12/4/2009','mm/dd/yyyy'),'Y','N')) as "Dec 4"
-- and so on till dec 31
from a
group by id
Re: attendence register [message #434822 is a reply to message #434742] Fri, 11 December 2009 00:28 Go to previous messageGo to next message
Messages: 9
Registered: August 2008
Junior Member
But thats a laborious exercise...too much of code
Re: attendence register [message #434826 is a reply to message #434822] Fri, 11 December 2009 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64283
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
shettypravs wrote on Fri, 11 December 2009 07:28
But thats a laborious exercise...too much of code

Poor boy! ./fa/1637/0/

SQL> with d as (select level d from dual connect by level <= 31)
  2  select decode(d,1,'select id, ','')||
  3         'max(decode(date_time,to_date(''12/'||d||'/2009'',''mm/dd/yyyy''),''Y'',''N'')) as "Dec '||
  4         d||'"'||
  5         decode(d,31,'
  6  from a group by id',',') line
  7  from d
  8  /
select id, max(decode(date_time,to_date('12/1/2009','mm/dd/yyyy'),'Y','N')) as "Dec 1",
max(decode(date_time,to_date('12/2/2009','mm/dd/yyyy'),'Y','N')) as "Dec 2",
max(decode(date_time,to_date('12/3/2009','mm/dd/yyyy'),'Y','N')) as "Dec 3",
max(decode(date_time,to_date('12/4/2009','mm/dd/yyyy'),'Y','N')) as "Dec 4",
max(decode(date_time,to_date('12/5/2009','mm/dd/yyyy'),'Y','N')) as "Dec 5",
max(decode(date_time,to_date('12/6/2009','mm/dd/yyyy'),'Y','N')) as "Dec 6",
max(decode(date_time,to_date('12/7/2009','mm/dd/yyyy'),'Y','N')) as "Dec 7",
max(decode(date_time,to_date('12/8/2009','mm/dd/yyyy'),'Y','N')) as "Dec 8",
max(decode(date_time,to_date('12/9/2009','mm/dd/yyyy'),'Y','N')) as "Dec 9",
max(decode(date_time,to_date('12/10/2009','mm/dd/yyyy'),'Y','N')) as "Dec 10",
max(decode(date_time,to_date('12/11/2009','mm/dd/yyyy'),'Y','N')) as "Dec 11",
max(decode(date_time,to_date('12/12/2009','mm/dd/yyyy'),'Y','N')) as "Dec 12",
max(decode(date_time,to_date('12/13/2009','mm/dd/yyyy'),'Y','N')) as "Dec 13",
max(decode(date_time,to_date('12/14/2009','mm/dd/yyyy'),'Y','N')) as "Dec 14",
max(decode(date_time,to_date('12/15/2009','mm/dd/yyyy'),'Y','N')) as "Dec 15",
max(decode(date_time,to_date('12/16/2009','mm/dd/yyyy'),'Y','N')) as "Dec 16",
max(decode(date_time,to_date('12/17/2009','mm/dd/yyyy'),'Y','N')) as "Dec 17",
max(decode(date_time,to_date('12/18/2009','mm/dd/yyyy'),'Y','N')) as "Dec 18",
max(decode(date_time,to_date('12/19/2009','mm/dd/yyyy'),'Y','N')) as "Dec 19",
max(decode(date_time,to_date('12/20/2009','mm/dd/yyyy'),'Y','N')) as "Dec 20",
max(decode(date_time,to_date('12/21/2009','mm/dd/yyyy'),'Y','N')) as "Dec 21",
max(decode(date_time,to_date('12/22/2009','mm/dd/yyyy'),'Y','N')) as "Dec 22",
max(decode(date_time,to_date('12/23/2009','mm/dd/yyyy'),'Y','N')) as "Dec 23",
max(decode(date_time,to_date('12/24/2009','mm/dd/yyyy'),'Y','N')) as "Dec 24",
max(decode(date_time,to_date('12/25/2009','mm/dd/yyyy'),'Y','N')) as "Dec 25",
max(decode(date_time,to_date('12/26/2009','mm/dd/yyyy'),'Y','N')) as "Dec 26",
max(decode(date_time,to_date('12/27/2009','mm/dd/yyyy'),'Y','N')) as "Dec 27",
max(decode(date_time,to_date('12/28/2009','mm/dd/yyyy'),'Y','N')) as "Dec 28",
max(decode(date_time,to_date('12/29/2009','mm/dd/yyyy'),'Y','N')) as "Dec 29",
max(decode(date_time,to_date('12/30/2009','mm/dd/yyyy'),'Y','N')) as "Dec 30",
max(decode(date_time,to_date('12/31/2009','mm/dd/yyyy'),'Y','N')) as "Dec 31"
from a group by id

Does this help?


[Updated on: Fri, 11 December 2009 02:09]

Report message to a moderator

Re: attendence register [message #434830 is a reply to message #434826] Fri, 11 December 2009 02:36 Go to previous message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That's just cruel.

The trick to this class of problem is the Row Generator. (There was a link to more details earlier - I'd read it if I were you).

A row generator does just that - it returns rows.

In this case you need a row generator that will return one row for each day in December.
Once you've got this. all you need to do is outer joiin your row generator to your data query.

Ironically, @Michel included a row generator in his query to generate the lines needed for a non-row generator solution.

A sample row generator you could use is:
select to_date('01-12-2009','dd-mm-yyyy') + level -1 from dual connect by level <=31;
Previous Topic: ORA-04021 Timeout occured while waiting to lock object
Next Topic: PL/SQL sometimes performs faster than a single SQL
Goto Forum:

Current Time: Sat Jan 21 14:17:48 CST 2017

Total time taken to generate the page: 0.15490 seconds