Sql SELECT for personnel available in a specific time period

Question

I have 3 tables. First one is a list of personnel:

Table Personnel

Id_personnel         Name
1                    John
2                    Alice
3                    Tom
4                    Charles
5                    Ben

Second is a table with medical leave:

Table Medical

Id_personnel         Start_date         End_date
3                    2012-08-02         2012-08-05
2                    2012-08-02         2012-08-15
4                    2012-10-04         2012-10-06
2                    2012-10-02         2012-10-15
2                    2012-09-20         2012-09-21

Third is a table with holiday leave:

Table Holiday

Id_personnel         Start_date         End_date
3                    2012-08-02         2012-08-05
2                    2012-02-02         2012-02-15
5                    2012-10-01         2012-10-05

I have two event dates: Symposium_start_date and a Symposium_end_date (as variables) and I would like to select all Personnel.Id_personnel and Personnel.Name available for the symposium (not in medical leave or on holiday).

In my example if Symposium_start_date = 2012-10-02 and Symposium_end_date = 2012-10-06 the result must be:

Id_personnel         Name
1                    John
3                    Tom

(as you can see Alice and Charles are on medical leave and Ben on holiday)

Thank you!

For this type of query you must specify the database. Date overlapping is handled differently by Oracle and MS SQL Server and others. — Damian Leszczyński - Vash, Oct 08 '12 at 08:47

podiluska · Accepted Answer · 2012-10-08T09:01:06.653

For a MS SQL server.

declare @start date = '2012-10-02', @end date = '2012-10-10'

select personnel.* 
from personnel
    left join holiday on personnel.Id_personnel = holiday.Id_personnel
        and holiday.start_date<=@end
        and holiday.end_date>=@start
    left join medical on personnel.Id_personnel= medical.Id_personnel
        and medical.start_date<=@end
        and medical.end_date>=@start
where holiday.Id_personnel is null  
and medical.Id_personnel is null

Chalky · Answer 2 · 2012-10-08T18:38:46.757

1

assuming MS-SQL, similar to others, but have shown tables unioned together, and subquery that shows all those who conflict with the symposium dates - I think this is easier to write first - and then selecting all those people who are NOT conflicting. Regards!

declare @Symposium_start_date date = '2012-10-02'
declare @Symposium_end_date date = '2012-10-06'

select * from Personnel P where p.Id_personnel not in (
    select Id_personnel from (
        select m.Id_personnel, m.Start_date, m.End_date
        from Medical M 
        union
        select h.Id_personnel, h.Start_date, h.End_date
        from Holiday H 
    ) as Leave
    where (Start_date  between @Symposium_start_date and @Symposium_end_date)
        or (End_date between @Symposium_start_date and @Symposium_end_date)
        or (Start_date <= @Symposium_start_date and End_date >= @Symposium_end_date)
)

edited Oct 08 '12 at 18:38

answered Oct 08 '12 at 09:20

Chalky

1,624
18
20

What if the person's holiday starts befoe the symposium and ends after it? – podiluska Oct 08 '12 at 09:25
Very true, have added that now. – Chalky Oct 08 '12 at 18:39
And @podiluska, I like your date selection approach better than mine! – Chalky Oct 09 '12 at 07:49
@podiluska - thank you :))) yes, both are elegant and useful. I will use podiluska's solution because I see it more easy to customize (my question was only the "core" of the problem - each table has other fields I have to filter). – Mugur Ungureanu Oct 09 '12 at 14:59

Diego · Answer 3 · 2012-10-08T09:10:31.447

0

this is an example with the Medical table only. You can easily add the Holiday

declare @Symposium_start_date datetime
declare @Symposium_end_date  datetime

set @Symposium_start_date = '2012-08-01'
set @Symposium_end_date  = '2012-08-04'


select * 
from personel p left join medical m on p.Id_personel = m.Id_personel
where (@Symposium_start_date > m.end_date) or (@Symposium_end_date<m.start_date)

edited Oct 08 '12 at 09:10

answered Oct 08 '12 at 08:58

Diego

34,802
21
91
134

Sql SELECT for personnel available in a specific time period

3 Answers3