Checking if a date range exists in a particular da

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Checking if a date range exists in a particular da

Author

Topic

priyanka.ks
Starting Member

1 Post

Posted - 2015-01-06 : 09:28:49

Hi,

I have a table called as Events and below are its columns

ID int
EventFromDate datetime
EventToDate datetime
EventDesc nvarchar(200)
IsHoliday bit

this is a master table where the admin would enter the Events/Holidays for the entire year.
the data would be as below:

ID EventFromdate EventTodate EventDesc isHoliday
1 26-01-2015 26-01-2015 Republicday Yes
2 30-01-2015 31-01-2015 TeamOuting No
3 01-05-2015 01-05-2015 Labour day Yes

Now, suppose a employee applies leave on 26/01/2015 to 26/01/2015 then it should not insert into table and return a value "Not updated"
2. How to handle the scenario if a employee applies leave between the range 23/01/2015 to 27/01/2015, since 26/01/2015 is a holiday in between. how the data can still be inserted excluding 26/01/2015
3. Can we exclude a non-working day or a sunday.

Leavedetails table to insert leaves applied by employee is as follows

LeaveDetailID int
LeaveTypeId int
FromDate datetime
EndDate datetime
Remarks nvarchar

Please help.

Regards.

rocknpop
Posting Yak Master

201 Posts

Posted - 2015-01-07 : 05:29:51

Maybe something like below.
This will give you the valid dates for the leaves excluding the days marked as Holidays in the Events table and excluding weekends.

DECLARE @Events TABLE
(
ID INT IDENTITY(1,1) ,
EventFromDate datetime,
EventToDate datetime,
EventDesc nvarchar(200),
IsHoliday bit
);

INSERT INTO @Events
VALUES('2015-01-26','2015-01-26', 'Republicday',1),
('2015-01-30','2015-01-31','TeamOuting',0),
('2015-01-05','2015-01-05','Labour day', 1)

--SELECT * FROM @Events

DECLARE @Leaves TABLE
(
LeaveDetailID INT IDENTITY(1,1),
EMPID INT,
LeaveTypeId int,
FromDate datetime,
EndDate datetime,
Remarks nvarchar
)

DECLARE @leaveStartDate DATETIME = '2015-01-23', @leaveEndDate DATETIME= '2015-01-27'
--DECLARE @leaveStartDate DATETIME = '2015-01-29', @leaveEndDate DATETIME= '2015-02-02'

;WITH calendarTable
AS
(
SELECT @leaveStartDate AS DT
UNION ALL
SELECT DT + 1
FROM calendarTable
WHERE DT<=@leaveEndDate
)

--SELECT * FROM calendarTable
--WHERE DT<=@leaveEndDate

SELECT DT AS leaveDates
FROM calendarTable C
WHERE DT<=@leaveEndDate
AND NOT EXISTS (
SELECT 1
FROM @Events E
WHERE C.DT BETWEEN E.EventFromDate AND E.EventToDate
AND E.IsHoliday = 1
)
AND DATEPART(Dw,C.DT) NOT IN (1,7)

--------------------
Rock n Roll with SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources