|
View previous topic :: View next topic
|
| Author |
Message |
rajrohith
New User
.jpg)
Joined: 15 Apr 2005
Posts: 54
Location: chennai
|
|
|
|
i accept ur word dick
but i need only who is taking maximum leave takers
by
raj |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello, raj,
Thank you for:
| Quote: |
| i accept ur word dick |
I am not clear on what this tells me:
| Quote: |
| but i need only who is taking maximum leave takers |
To continue with your example, what would you want for the result if there were several people who had taken 16 days. Let's say that in addition to the sample you posted, there was also:
12380 16
12478 16
12500 16
Should all of them be reported - i would think so if you want to know the empno(s) who took max leave? |
|
| Back to top |
|
|
rajrohith
New User
Joined: 15 Apr 2005
Posts: 54
Location: chennai
|
|
|
|
yes correct dick
i want that only what u said in the examples
by
raj |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello raj,
That brings me back to
| Quote: |
I do not believe this solution will be found in a simple, efficient SQL query.
|
If this was my requirement, i would set up a cursor that would allow me to fetch all of the rows in the "leave" table keeping the empno and the "leave days" calculation.
As i fetched the entries, i would RELEASE them to an internal sort that is sorted on ascending empno. I would then RETURN the entries from the sort and sum by empno.
Each time i encountered a new empno, i would check the current sum against a working data field with the previous "max". If the number is less, clear the "sum" and continue.
If the sum is equal to the "max" move the empno into the "next" position of an array (defined to hold as many empnos as are likely to "tie" for max leave).
If the sum is greater than the stored max, move spaces to the array and set the displacement back to the beginning, move the empno to the first array entry and save the "new" max value.
When the process completes (the RETURN raises the AT END condition), the stored max is the number you want and the entries in the array are all of the empnos who have taken the "max" leave.
Please let me know if any of that is not clear. |
|
| Back to top |
|
|
rajrohith
New User
Joined: 15 Apr 2005
Posts: 54
Location: chennai
|
|
|
|
yes u said correct way but its too big na
i need in one query
by
raj |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Good Luck  |
|
| Back to top |
|
|
rajrohith
New User
Joined: 15 Apr 2005
Posts: 54
Location: chennai
|
|
|
|
i have another one query
for example
in one table
empname empid date of birth
i want to take person who is the age between 25 to 30
this is my queary
any body know pls tell me the query
thanks and regards
raj |
|
| Back to top |
|
|
vini_srcna
Active User
Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark
|
|
|
|
SELECT * FROM SOMETABLE
WHERE YEAR(CURRENT TIMESTAMP) - YEAR(DOB) BETWEEN 25 & 30
I have not tested this query. Let me know if this doesnt work. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
That may get close, but will give some results that should not be "hits". For example, most of the people born in 1982 are not yet 25, but i believe this query will "find" them as being 25. . . . |
|
| Back to top |
|
|
vini_srcna
Active User
Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark
|
|
|
|
Yeah, You are right.
Month and Day should also be considered to get the exact results. |
|
| Back to top |
|
|
|
|
