OraFAQ Forum: SQL & PL/SQL » attendence register

Home » SQL & PL/SQL » SQL & PL/SQL » attendence register (oracle 10g V2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

attendence register [message #434742]

Thu, 10 December 2009 08:30

pointers
Messages: 451
Registered: May 2008

Senior Member

Hi,

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.

Regards,
Pointers

Report message to a moderator

Re: attendence register [message #434743 is a reply to message #434742]

Thu, 10 December 2009 08:37

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Nice homework assignment.

SEARCH this forum for "calendar" and for "pivot".

Report message to a moderator

Re: attendence register [message #434744 is a reply to message #434742]

Thu, 10 December 2009 08:38

cookiemonster
Messages: 13967
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.

Report message to a moderator

Re: attendence register [message #434821 is a reply to message #434742]

Fri, 11 December 2009 00:26

shettypravs
Messages: 9
Registered: August 2008

Junior Member

Quote:

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

Report message to a moderator

Re: attendence register [message #434822 is a reply to message #434742]

Fri, 11 December 2009 00:28

shettypravs
Messages: 9
Registered: August 2008

Junior Member

But thats a laborious exercise...too much of code

Report message to a moderator

Re: attendence register [message #434826 is a reply to message #434822]

Fri, 11 December 2009 01:22

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

shettypravs wrote on Fri, 11 December 2009 07:28

But thats a laborious exercise...too much of code

Poor boy!

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  /
LINE
-------------------------------------------------------------------------------------------------------
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?

Regards
Michel

[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

JRowbottom
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;

Report message to a moderator

Previous Topic:	ORA-04021 Timeout occured while waiting to lock object
Next Topic:	PL/SQL sometimes performs faster than a single SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Aug 25 10:10:33 CDT 2025