Stuck on what is a simple query

If i have 2 tables one for people and one for holidays. Everytime someone goes on holiday the date gets entered in the holiday table. How would I query this so it shows the person name from the persons table if they have been on more then say 2 holidays bet开发者_运维问答ween 1st of jan 2010 and the 6th of june 2010? This seems simple but I cant seem to do it.

---

If all you want is the list of names of people taking 2 or more days between those two dates:

SELECT people.name
FROM people
WHERE EXISTS (
  SELECT count(*)
  FROM days_taken
  WHERE people.person_id=days_taken.person_id AND
  days_taken.vacation_date BETWEEN date1 AND date2
  HAVING count(*)>=2
)

If you want the name and the number of days:

SELECT people.name,count(*)
FROM people JOIN days_taken ON people.person_id=days_taken.person_id
WHERE days_taken.vacation_date BETWEEN date1 AND date2
GROUP BY people.name
HAVING count(*)>=2

---

SELECT people.name, COUNT(*) c
FROM people INNER JOIN holidays
ON people.user_id = holidays.user_id
WHERE holidays.departure_date BETWEEN date1 AND date2
GROUP BY people.name
HAVING c > 2

---

SELECT p1.name, p2.num_holidays
FROM people p1
INNER JOIN 
(
SELECT people.user_id, COUNT(*) as num_holidays
FROM people 
INNER JOIN holidays  ON (people.user_id = holidays.user_id)
WHERE holidays.departure_date BETWEEN date1 AND date2
GROUP BY people.user_id
HAVING  COUNT(*) > 2
)p2 ON (p2.user_id = p1.user_id)

---

For SQL Server 2005 onwards, you can use the analytical function Count() OVER

select p.*, h.C
from person p inner join
(
    select distinct person_id, C = COUNT(*) over (partition by person_id)
    from holiday
    where holiday_date between '20100101' and '20100606'
) h on h.person_id = p.person_id and h.C >= 2