Check whether leave already applied or not? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Check whether leave already applied or not?

Author

Topic

dhinasql
Posting Yak Master

195 Posts

Posted - 2012-09-27 : 08:31:51

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)

Table structure:
FromDate ToDate FromSession ToSession
12/09/2012 13/09/2012 0 0
19/09/2012 23/09/2012 2 0

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.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-27 : 08:48:48

[code]IF EXISTS
(
SELECT * FROM table1
WHERE fromdate <= @ToDate
AND ToDate >= @FromDate
AND fromSession >= @FromSession
AND ToSession <= @ToSession
)
SELECT 1
ELSE
SELECT 0;[/code]

dhinasql
Posting Yak Master

195 Posts

Posted - 2012-09-27 : 10:09:16

It is not working for Date and Session combination

For Example, Find Sample value below

FromDate TODate FromSession ToSession
2012-09-11 2012-09-14 2 1
2012-09-11 2012-09-11 1 1

Input Details below

FromDate - 11/09/2012
ToDate - 11/09/2012
FromSession - 0
ToSession - 0

Output should be 1 , But now it is returing 0

Looking forward

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-27 : 10:16:03

[code]
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;
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dhinasql
Posting Yak Master

195 Posts

Posted - 2012-09-28 : 07:52:09

Thanks Visakh, Its working fine..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-28 : 10:18:23

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources