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