I need to check whether leave has been alrady applied for the selected dates. I am also checking the seesions(Both Session--0 or First Session--1 or Second Session--2)
When user try to apply for the leave, I have to pass the FromDate, ToDate and FromSession, ToSession values as a Input and i want to return output parameter as Whether the Leave already Applied or Not for the Date / Session
We have to consider all the possible synarios, User Some time select sessions like "FromSession" - 1 , "ToSession" - 2 , Which means he want to apply leave for both session (full day ), Some user may apply leave like "FromSession" - 0 , ToSession - 0 , Which means he is trying to apply for both session.
Like that we have to consider all the possible session compination and Date Compination.
For Example When i pass FromDate - 12/09/2012 ToDate - 12/09/2012 FromSession - 0 ToSession - 0
SP should Return output as 1
(0 - Not applied for the requested Date / Session , 1 - Leave Already Applied )
All your help much appreciated, let me know if you need more details to get the expected result.
IF EXISTS
(
SELECT * FROM table1
WHERE fromdate <= @ToDate
AND ToDate >= @FromDate
AND fromSession >= @FromSession
AND ToSession <= @ToSession
)
SELECT 1
ELSE
SELECT 0;
IF EXISTS
(
SELECT * FROM table1
WHERE fromdate <= @ToDate
AND ToDate >= @FromDate
AND (fromSession >= @FromSession OR @FromSession=0)
AND (ToSession <= @ToSession OR @ToSession =0 )
)
SELECT 1
ELSE
SELECT 0;
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
Check whether leave already applied or not?
Posted - 09/27/2012 : 08:31:51
