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

Author Message
rajrohith

New User

Joined: 15 Apr 2005
Posts: 54
Location: chennai

 Posted: Thu Mar 22, 2007 3:32 pm 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

 Posted: Thu Mar 22, 2007 4:28 pm Hi rajrohith, Can u please elaborate ur query ......
guptae

Moderator

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

 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

 Posted: Thu Mar 22, 2007 5:01 pm 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

 Posted: Thu Mar 22, 2007 5:04 pm 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

Moderator

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

 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

 Posted: Thu Mar 22, 2007 7:34 pm 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

 Posted: Thu Mar 22, 2007 7:46 pm 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

 Posted: Thu Mar 22, 2007 8:05 pm my requirement is who is the maximum leave taker in the year of 2005 thanks and regards raj
tarunbhardwajleo

New User

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

 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

 Posted: Thu Mar 22, 2007 9:19 pm thanks tarun by raj
vini_srcna

Active User

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

 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

 Posted: Thu Mar 22, 2007 11:04 pm can u tell me vini how to do thanks and regards
tarunbhardwajleo

New User

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

 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

 Posted: Fri Mar 23, 2007 12:04 am thanks tarun i will do thanks and regards raj
dick scherrer

Moderator Emeritus

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

 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

New User

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

 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

Active User

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

 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

Active User

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

 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

 Posted: Fri Mar 23, 2007 11:19 am 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

 Posted: Fri Mar 23, 2007 12:32 pm 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

Moderator Emeritus

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

 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

 Posted: Fri Mar 23, 2007 9:15 pm 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

 Posted: Fri Mar 23, 2007 9:16 pm i want to take that max leave taker only by raj
dick scherrer

Moderator Emeritus

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

 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.
 View Bookmarks All times are GMT + 6 Hours
 Goto page 1, 2  Next

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

 © 2003-2021  IBM MAINFRAMES Software & Support Division Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us