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

rajrohith · New User Joined: 15 Apr 2005 Posts: 54 Location: chennai

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

mkk157 · Active User Joined: 17 May 2006 Posts: 310

Hi rajrohith,

Can u please elaborate ur query ......

guptae · Posted: Thu Mar 22, 2007 4:32 pm

Hi Raj,

On what basis one can determine who is maximum leave taker ?

rajrohith · New User Joined: 15 Apr 2005 Posts: 54 Location: chennai

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

rajrohith · New User Joined: 15 Apr 2005 Posts: 54 Location: chennai

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

guptae · Posted: Thu Mar 22, 2007 5:21 pm

Hi Raj,

Is starting date and ending date refer to leave start date & end date?

rajrohith · New User Joined: 15 Apr 2005 Posts: 54 Location: chennai

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

mkk157 · Active User Joined: 17 May 2006 Posts: 310

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.

rajrohith · New User Joined: 15 Apr 2005 Posts: 54 Location: chennai

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

thanks and regards
raj

tarunbhardwajleo · Posted: Thu Mar 22, 2007 9:15 pm

Try this

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

rajrohith · New User Joined: 15 Apr 2005 Posts: 54 Location: chennai

thanks tarun

by
raj

vini_srcna · Posted: Thu Mar 22, 2007 9:33 pm

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

rajrohith · New User Joined: 15 Apr 2005 Posts: 54 Location: chennai

can u tell me vini how to do

thanks and regards

tarunbhardwajleo · Posted: Fri Mar 23, 2007 12:00 am

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.....

rajrohith · New User Joined: 15 Apr 2005 Posts: 54 Location: chennai

thanks tarun
i will do

thanks and regards
raj

dick scherrer · Posted: Fri Mar 23, 2007 1:13 am

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. . . .

tarunbhardwajleo · Posted: Fri Mar 23, 2007 1:28 am

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...

vini_srcna · Posted: Fri Mar 23, 2007 10:47 am

[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.

vini_srcna · Posted: Fri Mar 23, 2007 10:55 am

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.

rajrohith · New User Joined: 15 Apr 2005 Posts: 54 Location: chennai

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

rajrohith · New User Joined: 15 Apr 2005 Posts: 54 Location: chennai

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

dick scherrer · Posted: Fri Mar 23, 2007 7:10 pm

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.

rajrohith · New User Joined: 15 Apr 2005 Posts: 54 Location: chennai

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

rajrohith · New User Joined: 15 Apr 2005 Posts: 54 Location: chennai

i want to take that max leave taker only

by
raj

dick scherrer · Posted: Fri Mar 23, 2007 9:50 pm

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.