hero9989 hero9989 - 11 days ago 6
SQL Question

Holiday Booking system, search for date - return a list of employees who are on holiday on that date

I seem to be asking a lot of questions on here recently, thanks in advance for any help!

First - I have a calendar table which may make this easier. Calendar table is pretty standard, a dateID, Date and varying different formats of that date.

I want to give the query a date, and for it to return a list of all employees and whether or not they were on holiday on that date.

Employee Table
Leave Table

LeaveTypeID is 1 for holiday 2 for sickness, 3 for compassionate, etc, etc, I'm only concerned with holiday at this point.
StartDate and EndDate are as they appear
StartMidday is whether or not the holiday starts at midday on the first day (so working the morning and starting the holiday at lunch), same for endmidday but for the end of the holiday period)

Would ideally like it to say whether that day is a half day as well if possible?
So I want my result to be something like (assuming I chose the date of the 4th Nov, 2016)

EmployeeID OnHoliday IsHalf
1 TRUE TRUE
2 FALSE FALSE
3 FALSE FALSE
4 FALSE FALSE
5 FALSE FALSE


Hopefully that's enough info. Thanks again in advance!

Answer

i think you can use between for this purpose:

    declare @InputDate date='2016-09-16' --yur input date

   select EmployeeId,
   case when (select count(1) from EmployeeLeave 
   where (@InputDate between StartDate and EndDate) 
   and EmployeeLeave .EmployeeId=Employee .EmployeeId)>1 then 'True' else 'False' end as [OnHoliday],
   case when ((select StartMidDay from EmployeeLeave 
   where (@InputDate between StartDate and EndDate) 
   and EmployeeLeave .EmployeeId=Employee .EmployeeId))=0.5 then 'True' else 'False' end as [IsHalf] ,
   from Employee 
Comments