IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

SQL to know the maximum leave taker in the year of 2005


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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: 1208
Location: Bangalore,India

PostPosted: Thu Mar 22, 2007 4:32 pm
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
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
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: 1208
Location: Bangalore,India

PostPosted: Thu Mar 22, 2007 5:21 pm
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
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
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
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
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
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
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
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
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
Reply with quote

thanks tarun
i will do

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

Moderator Emeritus


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

PostPosted: Fri Mar 23, 2007 1:13 am
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
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
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
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
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
rajrohith

New User


Joined: 15 Apr 2005
Posts: 54
Location: chennai

PostPosted: Fri Mar 23, 2007 12:32 pm
Reply with quote

hai all

SELECT date1.empno, MAX(DAY(enddate)-DAY(startdate)+1)
FROM date1
WHERE YEAR(enddate)=2005;


above query is not working


SELECT date1.empno, Sum(Day(Enddate)-Day(Startdate)+1) AS total
FROM date1
WHERE (((Year([Startdate]))=2005) AND ((Year([Enddate]))=2005))
GROUP BY date1.empno
ORDER BY 2 DESC;


this query works fine
i got all
but i want to take particular maximum leave taker

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

Moderator Emeritus


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

PostPosted: Fri Mar 23, 2007 7:10 pm
Reply with quote

Hi Vinay,

Yes, that query will not find the maximum leave-taker - it will find the "single" longest leave (in number of days), but it will not find the max leave-taker for the month/year.

I do not believe this solution will be found in a simple, efficient SQL query.
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 9:15 pm
Reply with quote

yes vinay
u r correct
i didnt get maximum leave taker
in the above query
i got only list of peoples

empno total

12350 16
12555 10
12556 8

i want 12350 employee
because he onlyhas taken maximum leave
so i want to take employ only

thanks and regards
raj
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 9:16 pm
Reply with quote

i want to take that max leave taker only


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

Moderator Emeritus


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

PostPosted: Fri Mar 23, 2007 9:50 pm
Reply with quote

Hello,

What should happen if there are 3 or 9 or 20 people who have taken the same "maximum leave"?

I don't believe takng the highest "one" is the best answer. If you only want the max days taken in one "leave" it could be ok, but then the empno wouldn't be informative. If you want all of the empnos who took the "highest" leave, then you will need to allow for multiple empnos.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2 Goto page 1, 2  Next

 


Similar Topics
Topic Forum Replies
No new posts Increase the Maximum Length of LRECL ... JCL & VSAM 5
No new posts Vision plus req. In Pune 5 to 10 year Mainframe Jobs 0
No new posts Logic to skip Maximum time-stamp and ... DB2 2
No new posts Fetch Previous month & year in MM... DFSORT/ICETOOL 3
No new posts SORT to append Month and YEAR in MMYY... DFSORT/ICETOOL 1
Search our Forums:

Back to Top