June 3, 2011 at 4:04 am
Dears,
I have a table with following columns and test data for a particular employee :
LeaveRequestID, LeaveType, LeaveFromDate, LeaveToDate
1, Annual, 12/06/2010, 15/07/2010
2, Medical, 12/06/2010, 15/07/2010
3, Emergency, 12/06/2010, 15/07/2010
lets say someone is applying for a Annual leave and he will enter from and to date of his leave. i need to validate the entered 2 date range against the table mentioned above. For ex: he will enter From date : 15/06/2010 and To date: 13/07/2010. So in this case the selected date range is in valid as there is already an entry in the table which overlaps with the record in the table.
so what is the optimal and performance wise best way to validate this scenario using a stored procedure?
please advice,
thanks in advance,
Ammar
June 3, 2011 at 8:37 am
As a first attempt how about this:
SET LANGUAGE BRITISH
CREATE TABLE Leave (
LeaveID int IDENTITY(1,1),
LeaveType varchar(50),
LeavefromDate date,
LeaveToDate date)
INSERT INTO Leave
SELECT 'Annual','12/06/2010','15/07/2010' UNION ALL
SELECT 'Medical','12/06/2010','15/07/2010' UNION ALL
SELECT 'Emergency','12/06/2010','15/07/2010'
GO
CREATE PROCEDURE AddNewLeave @NewStart date, @NewEnd date, @LeaveType varchar(50)
AS
BEGIN
IF (SELECT SUM(CASE WHEN LeavefromDate < @NewStart AND LeaveToDate > @NewEnd THEN 1 ELSE 0 END) AS Overlap FROM Leave WHERE LeaveType = @LeaveType) = 0
INSERT INTO Leave
SELECT @LeaveType,@NewStart, @NewEnd
ELSE
RAISERROR ('OverLap',16,1)
END
GO
EXEC AddNewLeave '15/06/2010', '13/07/2010', 'Annual'
I would assume you would also store an employee ID so this would need to be added to the table, then filter on it also.
MCITP SQL 2005, MCSA SQL 2012
June 3, 2011 at 9:16 am
thanks Taylor for your valuable reply and appreciate a lot. by the way me also tried some other way something similar to you and the implementation as below:
CREATE TABLE LeaveRequest(
LeaveID int IDENTITY(1,1),
LeaveType varchar(50),
STARTDATE Date date,
ENDDATE date)
INSERT INTO LeaveRequest
SELECT 'Annual','2011-05-01','2011-05-24' UNION ALL
SELECT 'Medical','2011-05-01','2011-05-17' UNION ALL
SELECT 'Emergency','2011-05-02','2011-05-10'
GO
the test run code to validate the scenario:
DECLARE @FROMDATE DATETIME
DECLARE @TODATE DATETIME
DECLARE @LeaveType INT
SET @FROMDATE = '2011-03-01'
SET @TODATE='2011-05-15'
SET @LeaveType=1
SELECT * FROM
dbo.LeaveRequest
WHERE [EmployeeNo]='E024' AND ([STARTDATE] >= @FROMDATE or
[ENDDATE] >= @FROMDATE) AND ([STARTDATE] <= @TODATE)
if there any mistakes, appreciates your comments.
June 3, 2011 at 4:45 pm
The same problem I solved with a trigger. It works fast even with multi-row updates and inserts, because it checks overlapping only of id's that are "touched" (using INSERTED table within a trigger).
June 5, 2011 at 11:17 am
thanks a lot Vedran Kesegic and CELKO. 🙂
June 8, 2011 at 10:34 am
CELKO (6/3/2011)
Now, download the Rick Snodgrass book on Temporal Queries in SQL from the University of Arizona website (it is free).
Dr. Snodgrass has a ton of books and papers listed on his site all relating to temporal databases. Can you point out the one you are referring to? I'd appreciate it.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply