SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Check whether leave already applied or not?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dhinasql
Posting Yak Master

194 Posts

Posted - 09/27/2012 :  08:31:51  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/27/2012 :  08:48:48  Show Profile  Reply with Quote
IF EXISTS 
(
	SELECT * FROM table1
	WHERE fromdate <= @ToDate
	AND ToDate >= @FromDate
	AND fromSession >= @FromSession
	AND ToSession <= @ToSession
) 
	SELECT 1
ELSE
	SELECT 0;
Go to Top of Page

dhinasql
Posting Yak Master

194 Posts

Posted - 09/27/2012 :  10:09:16  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/27/2012 :  10:16:03  Show Profile  Reply with Quote

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/

Go to Top of Page

dhinasql
Posting Yak Master

194 Posts

Posted - 09/28/2012 :  07:52:09  Show Profile  Reply with Quote
Thanks Visakh, Its working fine..
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/28/2012 :  10:18:23  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000