List all dates within date range in SQL but ignore bank holidays

Question

I'm making a holiday manager.

I have a table with a list of start and end dates for each instance of holiday.

[LeaveID], [EmployeeID], [StartDate], [EndDate]

I also do have a calendar table with dates from 2016-2030, listing the usual variations of date format as well as times the factory is shut, including bank holidays, etc.

I'm working on the front end for it now they want me to display it in sort of calendar format so I will need to mark on each day, who has booked time off.

I figure I need to list each date within each date range (start date to end date), then check if each date on the calendar appears on that list.

So basically I need to get a list of dates within a date range.

On top of that. I'd like to be able to compare the list of dates from above, to the calendar table so I can ignore bank holidays when calculating the amount of holiday used for each instance.

Thanks in advance!

Possible duplicate of [How to generate a range of dates in SQL Server](http://stackoverflow.com/questions/11141507/how-to-generate-a-range-of-dates-in-sql-server) — Jens, Oct 24 '16 at 08:06
Can you include sample data from both tables? Could you show us the output you would expect from that sample? — David Rushton, Oct 24 '16 at 09:25

score 1 · Answer 1 · edited May 23 '17 at 11:48

1

To get a list of date within a date range, you will need source of numbers from 1 to n. I usually create such table and call it Numbers table.

To generate a list of date within a range, use following query.

SELECT
    DATEADD(DAY, Numbers.Number-1, [StartDate]) Date
FROM
    Numbers
WHERE
    DATEADD(DAY, Numbers.Number-1, [StartDate]) <= [EndDate]

To create such table, refer to this question.

If you want to list all dates in Employee table, just cross join it.

SELECT
    e.EmployeeID, 
    DATEADD(DAY, n.Number-1, e.[StartDate]) Date
FROM
    Numbers n, Employee e
WHERE
    DATEADD(DAY, n.Number-1, e.[StartDate]) <= e.[EndDate]

edited May 23 '17 at 11:48

Community

1
1

answered Oct 24 '16 at 08:08

Niyoko

7,512
4
32
59

Is the numbers table literally just a list of numbers to count facilitate a quicker version of a loop? – hero9989 Oct 24 '16 at 08:16
Yes. Without it, you will need a loop. There's a lot of ways to get such number, refer to this question to find more http://stackoverflow.com/questions/21425546/how-to-generate-a-range-of-numbers-between-two-numbers-in-sql-server – Niyoko Oct 24 '16 at 08:21
Is there any way to run this on all values in my employee leave table. As I said in the OP, I have a table with a list of start dates and end dates and I want to make a list of all dates contained in each date range listed in that table – hero9989 Oct 24 '16 at 08:33

score 0 · Answer 2 · answered Oct 24 '16 at 09:58

As you already have a dates table, you do not need the numbers table mentioned in the other answer. To accomplish what you are after requires a simple SQL Join from your dates table. Depending on how you want to format your final report you can either count up the number of EmployeeIDs returned or group them all into a calendar/table control in your front end on the DateValue.

In the query below you will get at least one DateValue for every date specified in the range (for which you can apply your own filtering such as where Dates.BankHoliday = 0 etc) and more than one where multiple Employees have taken leave:

-- Build some dummy data to run the query against.
declare @Emp table (LeaveID int, EmployeeID int , StartDate datetime, EndDate datetime);
insert into @Emp values
 (1,1,'20161101','20161105')
,(2,1,'20161121','20161124')
,(3,2,'20161107','20161109')
,(4,3,'20161118','20161122');

declare @Dates table (DateKey int, DateValue datetime, DateLabel nvarchar(50));
declare @s datetime = '20161025';
with cte as
(
select cast(convert(nvarchar(8),@s,112) as int) as DateKey
        ,@s as DateValue
        ,convert(nvarchar(50),@s,103) as DateLabel
union all
select cast(convert(nvarchar(8),DateValue+1,112) as int)
        ,DateValue+1
        ,convert(nvarchar(50),DateValue+1,103)
from cte
where DateValue+1 <= '20161205'
)
insert into @Dates
select * from cte;


-- Actually query the data.

-- Define the start and end of your date range to return.
declare @MinStart datetime = (select min(StartDate) from @Emp);
declare @MaxEnd datetime = (select max(EndDate) from @Emp);

select d.DateValue
    ,e.EmployeeID
from @Dates d
    left join @Emp e
        on(d.DateValue between e.StartDate and e.EndDate)
where d.DateValue between @MinStart and @MaxEnd
order by d.DateValue
        ,e.EmployeeID;

List all dates within date range in SQL but ignore bank holidays

2 Answers2