Click here to Skip to main content
14,423,590 members
Rate this:
Please Sign up or sign in to vote.
See more:
I am storing leaves in a table where the start date and end date are only date part and the time is stored as 'FN' and 'AN' for forenoon and after noon,

for example storing a full day leave is as below

Fromdate : 2016-03-03, fromLeaveTime : 'Full Day'
Todate: 2016-03-03, toLeaveTime:'FN'

fromLeaveTime and toLeaveTime may contain values 'FN', 'AN' or 'Full Day'

how to validate whether a leave is already applied?

if a person apply for half day how can I validate?

sample data is as follows

Id	UserId	FromDate	ToDate		Totaldays		FromLeaveTime	ToLeaveTime
78	118		2016-03-03	2016-03-03	1				Full Day		Full Day
79	118		2016-03-04	2016-03-04	0.5				FN				FN
80	118		2016-03-07	2016-03-07	0.5				AN				AN
81	118		2016-03-08	2016-03-09	1.5				Full Day		FN
82	118		2016-03-10	2016-03-11	1				AN				FN


What I have tried:

IF EXISTS(SELECT * FROM WorkFromHome WHERE UserId=@Id AND ((@FromDate between FromDate and ToDate) OR (@ToDate between FromDate and ToDate)))
	 BEGIN
	 
	 
	 
	 RAISERROR('Already Applied',16,1);
	 return;
	  END
Posted
Updated 8-Mar-16 0:26am
v5
Comments
[no name] 3-Mar-16 0:20am
   
You want to check "half day validation with @FromDate & @EndDate" or it will check differently?
Jijutj 3-Mar-16 2:42am
   
I have updated the question with sample data,
Maciej Los 3-Mar-16 5:34am
   
The question is still not clear...
Remember, we can't read in your mind or direct from your screen. We haven't even an access to your HDD. So, be more specific and provide more details about your issue.
Maciej Los 3-Mar-16 1:45am
   
Is there any primary key, which determines what 'job' has already been started?
Please, Improve question and provide more details.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Suppose You have following data in database)
From :03-03-2016 To:04-03-2016
Then your query will be like
IF EXISTS(SELECT * FROM WorkFromHome WHERE UserId=@Id AND ((@FromDate >= FromDate and ToDate) OR (@ToDate <= FromDate and ToDate)))
BEGIN
	 RAISERROR('Already Applied',16,1);
	 return;
END


As it can be the fromdate itself, so between won't work.

Let me know if you have further query.
   
Comments
Jijutj 3-Mar-16 2:32am
   
I have updated the question with sample data
Er. Puneet Goel 3-Mar-16 2:38am
   
Can you please tell a scenario where my query fails so that i can provide better solution.
Jijutj 3-Mar-16 2:52am
   
suppose I have added a leave from 2016-03-04 FN to 2016-03-04 FN means half day, I need to add AN (after noon ) leave
Er. Puneet Goel 3-Mar-16 4:05am
   
Will it always me either AN or FN incase From and to date are same ?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100