Solved

How to check the date range not repeate on the exist record?

Posted on 2001-07-10
12
152 Views
Last Modified: 2010-04-06
Hi all,
I have a employee holiday database, its structure is:
EmpNo     StartDay     EndDay     Part

(Part field option:  Morning, Afternoon, AllTheDay)

before post a new record, I need to check its range not repeate on the exist record.

e.g.
already have this record:
1   2001-4-16    2001-4-20    AllTheDay

the user can't add the following records.
1   2001-4-18    2001-4-22    AllTheDay
or
1   2001-4-20    2001-4-20    AllTheDay
or
1   2001-4-20    2001-4-20    Afternoon
??

How to do it?  Thanks.

Regards, supwang
0
Comment
Question by:SupWang
  • 5
  • 5
  • 2
12 Comments
 
LVL 2

Expert Comment

by:aubs
ID: 6272225
You could run something like  

SELECT * FROM EMPTABLE
WHERE ((:START BETWEEN STARTDAY AND ENDDAY)
OR (:FINISH BETWEEN STARTDAY AND ENDDAY))
AND (PART=:PART)
AND (EMPNO=:EMPNO)


Fill in the parameters at run time with the proposed new holiday bits:
START
FINISH
PART
EMPNO

If you get any records back, then there must be a clash.

regards

Aubs
0
 
LVL 2

Author Comment

by:SupWang
ID: 6272374
Hi Aubs,
Where can I find out the help of "BETWEEN" function?  
and I want to create the TQuery at runtime. Could you give me more codes?

Thanks, supwang
0
 
LVL 2

Expert Comment

by:aubs
ID: 6272416
Check out

http://w3.one.net/~jhoffman/sqltut.htm

Depends how you allow users to select the dates. If you are using a couple of TDateTimPickers you could do
something like this:


with query1 do begin
  Active:= False;
  sql.Clear;
  sql.add('SELECT * FROM EMPTABLE');
  sql.add('WHERE ((:START BETWEEN STARTDAY AND ENDDAY)');
  sql.add('OR (:FINISH BETWEEN STARTDAY AND ENDDAY))');
  sql.add('AND (PART=:PART)');
  sql.add('AND (EMPNO=:EMPNO)');
  ParamByName('START').AsDate:= DateTimePickerStart.Date;
  ParamByName('FINISH').AsDate:= DateTimePickerFinish.Date;
  ParamByName('PART').AsString:= edit1.text;//or use a TRadiogroup
  ParamByName('EMPNO').AsInteger:= strToInt(edit2.text);
  try
    Active:= True;
    if recordcount>0 then
      showmessage('Duplicate dates');

  finally
     Active:= False;
  end;
end;


Sorry, in a bit of a hurry. This is untested and just written from memory. You may need to do some debugging!

Aubs
0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 2

Expert Comment

by:aubs
ID: 6272546
Check out

http://w3.one.net/~jhoffman/sqltut.htm

Depends how you allow users to select the dates. If you are using a couple of TDateTimPickers you could do
something like this:


with query1 do begin
  Active:= False;
  sql.Clear;
  sql.add('SELECT * FROM EMPTABLE');
  sql.add('WHERE ((:START BETWEEN STARTDAY AND ENDDAY)');
  sql.add('OR (:FINISH BETWEEN STARTDAY AND ENDDAY))');
  sql.add('AND (PART=:PART)');
  sql.add('AND (EMPNO=:EMPNO)');
  ParamByName('START').AsDate:= DateTimePickerStart.Date;
  ParamByName('FINISH').AsDate:= DateTimePickerFinish.Date;
  ParamByName('PART').AsString:= edit1.text;//or use a TRadiogroup
  ParamByName('EMPNO').AsInteger:= strToInt(edit2.text);
  try
    Active:= True;
    if recordcount>0 then
      showmessage('Duplicate dates');

  finally
     Active:= False;
  end;
end;


Sorry, in a bit of a hurry. This is untested and just written from memory. You may need to do some debugging!

Aubs
0
 
LVL 2

Expert Comment

by:Andrey_Kulik
ID: 6273496
hi

aubs: what about next sample :)

already have this record:
1   2001-4-16    2001-4-20    AllTheDay

your query check out the folowing request and say OK
1   2001-4-10    2001-4-22    AllTheDay



SupWang: do some chages

SELECT * FROM EMPTABLE
WHERE ((:START > ENDDAY)
OR (:FINISH < STARTDAY ENDDAY))
AND (PART=:PART)
AND (EMPNO=:EMPNO)

Andrey

0
 
LVL 2

Expert Comment

by:Andrey_Kulik
ID: 6273499
oops!

SELECT * FROM EMPTABLE
WHERE ((:START > ENDDAY)
OR (:FINISH < STARTDAY))
AND (PART=:PART)
AND (EMPNO=:EMPNO)
0
 
LVL 2

Expert Comment

by:aubs
ID: 6273568
Hey I didn't say it was perfect :-)

You are right Andrey, but your SQL:



                     already have this record:
                     1   2001-4-16    2001-4-20    AllTheDay

                     your query check out the folowing request and say NOT OK!
                     1   2001-4-21    2001-4-22    AllTheDay

A compromise (and a bit messy):

with query1 do begin
                      Active:= False;
                      sql.Clear;
                      sql.add('SELECT * FROM EMPTABLE');
                      sql.add('WHERE ((((:START BETWEEN STARTDAY AND ENDDAY)');
                      sql.add('OR (:FINISH BETWEEN STARTDAY AND ENDDAY))');
                      sql.add('AND (PART=:PART))');
                      sql.add('OR ((STARTDAY BETWEEN :START AND :FINISH)');
                      sql.add('OR (ENDDAY BETWEEN :START AND :FINISH)))');
                      sql.add('AND (EMPNO=:EMPNO)');
                      ParamByName('START').AsDate:= DateTimePickerStart.Date;
                      ParamByName('FINISH').AsDate:= DateTimePickerFinish.Date;
                      ParamByName('PART').AsString:= edit1.text;//or use a TRadiogroup
                      ParamByName('EMPNO').AsInteger:= strToInt(edit2.text);
                      try
                        Active:= True;
                        if recordcount>0 then
                          showmessage('Duplicate dates');

                      finally
                         Active:= False;
                      end;
                     end;
0
 
LVL 2

Expert Comment

by:Andrey_Kulik
ID: 6273655
yes

1   2001-4-16    2001-4-20    AllTheDay
1   2001-4-21    2001-4-22    AllTheDay

this records have an intersect...

check the range logic


Andrey
0
 
LVL 2

Expert Comment

by:aubs
ID: 6275971
A better method:

with query1 do begin
                                          Active:= False;
                                          sql.Clear;
                                          sql.add('SELECT * FROM EMPTABLE');
                                          sql.add('WHERE ((((:START BETWEEN STARTDAY AND ENDDAY)');
                                          sql.add('OR (:FINISH BETWEEN STARTDAY AND ENDDAY))');
                                          sql.add('AND (PART=:PART))');
                                          sql.add('OR (:START < STARTDAY AND :FINISH>ENDDAY))');
                                          sql.add('AND (EMPNO=:EMPNO)');
                                          ParamByName('START').AsDate:= DateTimePickerStart.Date;
                                          ParamByName('FINISH').AsDate:= DateTimePickerFinish.Date;
                                          ParamByName('PART').AsString:= edit1.text;//or use a TRadiogroup
                                          ParamByName('EMPNO').AsInteger:= strToInt(edit2.text);
                                          try
                                            Active:= True;
                                            if recordcount>0 then
                                              showmessage('Duplicate dates');

                                          finally
                                             Active:= False;
                                          end;
                                         end;
0
 
LVL 2

Expert Comment

by:Andrey_Kulik
ID: 6276038
SELECT * FROM EMPTABLE
WHERE ((:START > ENDDAY)
OR (:FINISH < STARTDAY))
AND (PART=:PART)
AND (EMPNO=:EMPNO)

quite enough
0
 
LVL 2

Author Comment

by:SupWang
ID: 6280530
Hi,
Andrey_Kulik's sql is better. but still not enough.
it can't do the following:

exist record:
2001-4-20    2001-4-20    morning

the user can't insert
2001-4-20    2001-4-20    AllTheDay

but can insert
2001-4-20    2000-4-20    afternoon


more ideas?

Regards, supwang
0
 
LVL 2

Accepted Solution

by:
Andrey_Kulik earned 200 total points
ID: 6280987
SELECT * FROM EMPTABLE
WHERE ((:START > ENDDAY)
OR (:FINISH < STARTDAY))
AND ( (:PART = AllTheDay) OR ((:PART = Morning) AND (PART = Afternoon)) OR ((:PART = Afternoon) AND (PART = Morning)))
AND (EMPNO=:EMPNO)

may be so...

0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question