How to check the Calendar for an existing Vacation time

Hi Guys

I need to test somethiing

When a person books a holiday by setting a Startdate and an Endate it should check to see if the time has already been Approved or scheduled as vacation, Sick or any other Leave type. This has to be done against the name in the NameFull field.

So I Jack has already booked off Dec 1-3rd and Jill has Already booked of Dec 1 - 3rd and Jack then Books off Dec.1-3rd again it will prompt and say that Jack has already booked that time off.

James

adspmoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HemanthaKumarCommented:
Create a view with name as the I column sorted.

Do a dblookup like this on save or input validation (require to change prompt to failure and success functions resp)

temp := @DBLookup("Notes":"NoCache"; @DBName; FullName; "StartDate");
sDate := @IF(@ISError(temp); @Return(0); temp);
temp := @DBLookup("Notes":"NoCache"; @DBName; FullName; "EndDate");
eDate := @IF(@ISError(temp); @Return(0); temp);
Booked := @Explode( @TextToTime(@Text(sDate)) + "-" + @TextToTime(@Text(eDate)) );
CurrentDates := @Explode( @TextToTime(@Text(StartDate)) + "-" + @TextToTime(@Text(EndDate)) );
@if( @IsMember( CurrentDates; Booked) ; @Prompt([ok]; "Conflict"; "Already you have booked vacation on these dates"); "")

~Hemanth


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HemanthaKumarCommented:
Forgot to do this...Convert CurrentDates and Booked to text in @Ismember function.
0
qwaleteeCommented:
In the simple example you gave (Jack books the same exact set of days twice), it WILL be posible, because there will be an exact match.  But if theer is no exact match, you would not be ablet to key off the dates, because some part of teh dates would not match.

For example, if Jack already booked Dec 1-3, then tried to book Dec 2-4, there is no exact match, even though there is an "overlap match" if you consider all the in-between dates. (Dec 2 is the middle of the first booking and teh start of the second, whiel Dec 3 is the ned of teh first booking and themiddle of the second.)

You can get around this by getting a list of ALL dates a person has booked, so that you get back Dec 1, Dec 2, and Dec 3 for that first booking. You can then compare all the returned dates against all the dates in teh new booking.  That's basically what Hemantha has proposed, but is is somewhat inefficient to do this.

Another way would be to use script.  Have a view sort on the start dates.  Skip entried where the end is after the new attempted start, stop looking when the entry start is after the new attempted end, and anything you find in between indicates an overlap.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

adspmoAuthor Commented:
temp := @DbLookup( "" : "NoCache" ;""; "FullName" ; NameFull;"StartDate");
sDate := @If(@IsError(temp); @Return(0); temp);
temp1 := @DbLookup( "" : "NoCache" ;""; "FullName" ; NameFull;"EndDate");
eDate := @If(@IsError(temp1); @Return(0); temp1);
Booked := @Explode( @TextToTime(@Text(sDate)) + "-" + @TextToTime(@Text(eDate)) );
CurrentDates := @Explode( @TextToTime(@Text(StartDate)) + "-" + @TextToTime(@Text(EndDate)) );
@If( @Text(@IsMember( CurrentDates; Booked)) ; @Prompt([OK]; "Conflict"; "Already you have booked vacation on these dates"); "")

Here is what I have and it fails

Incorrect dataType blabla....NumberExpexted

I will be running this in my Submit button so it will fail and stop if there is a conflict

James
0
HemanthaKumarCommented:
@Text(@IsMember( CurrentDates; Booked)) I meant to be like this

@ISMember(@Text(CurrentDates); @Text(Booked))
0
adspmoAuthor Commented:
bla
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.