Home » SQL & PL/SQL » SQL & PL/SQL » query
query [message #253612] Tue, 24 July 2007 07:15 Go to next message
spsbombay
Messages: 29
Registered: June 2007
Junior Member

I have a table employee.. (emp_id,emp_name) and another table leave (emp_id,leave_date)
whenever a employee takes leave a row get inserted into leave table.
now i require a report which shows the emp_id who has taken the 2 or more leave within a month in past 3 months.
I am not able to furnish query for this. Can anybody help me on this.

Regards,
Prashant
Re: query [message #253623 is a reply to message #253612] Tue, 24 July 2007 07:32 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Start with showing what you tried and why that does not fulfill your requirements.
Re: query [message #253625 is a reply to message #253612] Tue, 24 July 2007 07:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am not able to furnish query for this

Post the beginning and why it does not work.

Regards
Michel
Re: query [message #253639 is a reply to message #253625] Tue, 24 July 2007 08:18 Go to previous message
spsbombay
Messages: 29
Registered: June 2007
Junior Member

Thanx for the quick response.

I got the solution.

select B.EMP_ID,TO_CHAR(leave_date,'MM') month,count(b.emp_id) leave_count 
from emp a, leave b 
where a.emp_id = b.emp_id
and b.leave_date >= add_months(sysdate,-3)
group by b.emp_id,TO_CHAR(leave_date,'MM')
having count(b.emp_id) > =2 


Regards,
Prashant
Previous Topic: finding packages, Which are using particular table
Next Topic: date conversion
Goto Forum:
  


Current Time: Sat Dec 03 18:07:28 CST 2016

Total time taken to generate the page: 0.11264 seconds