CODEWITHSUNDEEP
sqldatemultiple-tables
Mugur Ungureanu
Mugur Ungureanu

Reputation: 161

Sql SELECT for personnel available in a specific time period

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!

Upvotes: 3

Views: 1196

Answers (3)

Chalky
Chalky

Reputation: 1642

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)
)

Upvotes: 1

Diego
Diego

Reputation: 36126

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)

Upvotes: 0

podiluska
podiluska

Reputation: 51494

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

Upvotes: 2

Related Questions