Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

SQL to know the maximum leave taker in the year of 2005
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
rajrohith

New User


Joined: 15 Apr 2005
Posts: 54
Location: chennai

PostPosted: Thu Mar 22, 2007 3:32 pm    Post subject: SQL to know the maximum leave taker in the year of 2005
Reply with quote

my question is
one emp table is there
it contains four columns like empid , startdate enddate, remarks


i want to know who is the maximum leave taker in the year of 2005

any bodies know this query pls tell me

thanks and regards
raj
Back to top
View user's profile Send private message

mkk157

Active User


Joined: 17 May 2006
Posts: 310

PostPosted: Thu Mar 22, 2007 4:28 pm    Post subject: Re: date sql query
Reply with quote

Hi rajrohith,

Can u please elaborate ur query ......
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Thu Mar 22, 2007 4:32 pm    Post subject:
Reply with quote

Hi Raj,

On what basis one can determine who is maximum leave taker ?
Back to top
View user's profile Send private message
rajrohith

New User


Joined: 15 Apr 2005
Posts: 54
Location: chennai

PostPosted: Thu Mar 22, 2007 5:01 pm    Post subject:
Reply with quote

starting date and end date means we can get no of day leaves
then we have to culculate how many days they r taking leave in the year
then compare and who is taking maximum leave in the year of 2005
Back to top
View user's profile Send private message
rajrohith

New User


Joined: 15 Apr 2005
Posts: 54
Location: chennai

PostPosted: Thu Mar 22, 2007 5:04 pm    Post subject:
Reply with quote

i need who is taking maximum number of leaves taker in the year of 2005

right now we have starting date and ending date then we will get no of days leave
then we have to compare

thanks and regards
raj
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Thu Mar 22, 2007 5:21 pm    Post subject:
Reply with quote

Hi Raj,

Is starting date and ending date refer to leave start date & end date?
Back to top
View user's profile Send private message
rajrohith

New User


Joined: 15 Apr 2005
Posts: 54
Location: chennai

PostPosted: Thu Mar 22, 2007 7:34 pm    Post subject:
Reply with quote

yes correct ekta
its starting date and end date is how long days they want leave
then we can know how many days they have taken in every month then we have to calculate all days in the year of 2005

thanks
raj
Back to top
View user's profile Send private message
mkk157

Active User


Joined: 17 May 2006
Posts: 310

PostPosted: Thu Mar 22, 2007 7:46 pm    Post subject: Re: date sql query
Reply with quote

Hi Rajrohit,


From the data u gave, I consider the sample table structure as

ENo St_date End_date


Now to get the number of leaves for each employee in 2005,

Select eno,sum(St_date - End_date) from EMP group by eno having Year(St_date) = 2005 and Year(End_date) = 2005;


If any please correct me.
Back to top
View user's profile Send private message
rajrohith

New User


Joined: 15 Apr 2005
Posts: 54
Location: chennai

PostPosted: Thu Mar 22, 2007 8:05 pm    Post subject:
Reply with quote

my requirement is who is the maximum leave taker in the year of 2005

thanks and regards
raj
Back to top
View user's profile Send private message
tarunbhardwajleo

New User


Joined: 23 Feb 2007
Posts: 25
Location: Dallas, TX

PostPosted: Thu Mar 22, 2007 9:15 pm    Post subject: Re: date sql query
Reply with quote

Try this

SELECT ENO, MAX(DAYS(END_DATE) - DAYS(START_DATE) + 1)
FROM tablename
WHERE YEAR(END_DATE) = '2005';
Back to top
View user's profile Send private message
rajrohith

New User


Joined: 15 Apr 2005
Posts: 54
Location: chennai

PostPosted: Thu Mar 22, 2007 9:19 pm    Post subject:
Reply with quote

thanks tarun


by
raj
Back to top
View user's profile Send private message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Thu Mar 22, 2007 9:33 pm    Post subject:
Reply with quote

Select eno,sum(DAY(End_date)-DAY(St_date)) from EMP
where Year(St_date) = 2005 and Year(End_date) = 2005
GROUP BY ENO ORDER BY 2 DESC.

IF you want only the max leave taker then you can use
FETCH FRIST 1 ROW ONLY.

I have not tested this query and am sure there would be other simple ways. Let me know what happens
Back to top
View user's profile Send private message
rajrohith

New User


Joined: 15 Apr 2005
Posts: 54
Location: chennai

PostPosted: Thu Mar 22, 2007 11:04 pm    Post subject:
Reply with quote

can u tell me vini how to do

thanks and regards
Back to top
View user's profile Send private message
tarunbhardwajleo

New User


Joined: 23 Feb 2007
Posts: 25
Location: Dallas, TX

PostPosted: Fri Mar 23, 2007 12:00 am    Post subject:
Reply with quote

this should work for u

SELECT ENO, MAX(DAYS(END_DATE) - DAYS(START_DATE) + 1)
FROM tablename
WHERE YEAR(END_DATE) = 2005;

try it and lemme know if it doesnt.....
Back to top
View user's profile Send private message
rajrohith

New User


Joined: 15 Apr 2005
Posts: 54
Location: chennai

PostPosted: Fri Mar 23, 2007 12:04 am    Post subject:
Reply with quote

thanks tarun
i will do

thanks and regards
raj
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Mar 23, 2007 1:13 am    Post subject:
Reply with quote

Hello,

Possibly, i'm misreading the "rules" for max leqave-taker, but in many organizations people take multiple "leaves" throughout the year.

The solutions posted will identify the max for one "leave", but what if "Fred" takes a leave of 10 days and "Mary" takes 3 leaves of 5 days each? All of this was in 2005. I believe Mary should be the highest leave-taker. Unless i've misunderstood. . . .
Back to top
View user's profile Send private message
tarunbhardwajleo

New User


Joined: 23 Feb 2007
Posts: 25
Location: Dallas, TX

PostPosted: Fri Mar 23, 2007 1:28 am    Post subject:
Reply with quote

Hi Dick,

Your point is very valid. But in that case we cannot assume employee number to be the key of the table otherwise we would have just 1 record per employee and the previous solution would be valid.
If the employee number is not the key, we can then devise the logic by using the group by clause on employee number and build the query.

Raj, could you please give an extract of the DCLGEN/Copybook of the table or else specify what the key to the table is...
Back to top
View user's profile Send private message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Fri Mar 23, 2007 10:47 am    Post subject:
Reply with quote

[quote]could you please give an extract of the DCLGEN/Copybook of the table or else specify what the key to the table is...[/quote]

I'm curious to know how you can find the key of the table from the DCLGEN/Copybook.
Back to top
View user's profile Send private message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Fri Mar 23, 2007 10:55 am    Post subject:
Reply with quote

Are you guys saying the below query wont work..?

Select eno,sum(DAY(End_date)-DAY(St_date)) from EMP
where Year(St_date) = 2005 and Year(End_date) = 2005
GROUP BY ENO ORDER BY 2 DESC.
Back to top
View user's profile Send private message
rajrohith

New User


Joined: 15 Apr 2005
Posts: 54
Location: chennai

PostPosted: Fri Mar 23, 2007 11:19 am    Post subject:
Reply with quote

hai all

its contains so many records
here no primary keys
any peoples taken leaves any no of days in every month

for example

empno startdate enddate remarks

12345 05-01-2005 07-01-2005 fever
12356 14-01-2005 18-01-2005 marriage
12345 25-02-2005 27-02-2005 family function
12356 12-05-2006 15-05-2005 fever
12388 02-08-2005 10-08-2005 fever

records looks like that



so we have to calculate every month first then we have to add all leaves after
we have to take who is the maximum leave taker


thanks
raj
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Wishing everyone a very Happy Diwali ... Rohit Umarjikar General Talk & Fun Stuff 1 Sat Oct 29, 2016 1:54 am
No new posts Retrieve current year or month using ... vnktrrd DFSORT/ICETOOL 15 Tue Mar 15, 2016 4:14 pm
No new posts What is the maximum number of sort wo... Pravina M SYNCSORT 2 Mon Mar 14, 2016 4:51 pm
This topic is locked: you cannot edit posts or make replies. Mainframe Openings for 3-10 year exp ... yogeshwar_ade Mainframe Jobs 0 Wed Jan 13, 2016 2:52 pm
No new posts Merry Christmas and Happy New Year! Rohit Umarjikar General Talk & Fun Stuff 3 Thu Dec 24, 2015 3:58 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us