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

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
LVL 2
SupWangAsked:
Who is Participating?
 
Andrey_KulikConnect With a Mentor Commented:
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
 
aubsCommented:
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
 
SupWangAuthor Commented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
aubsCommented:
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
 
aubsCommented:
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
 
Andrey_KulikCommented:
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
 
Andrey_KulikCommented:
oops!

SELECT * FROM EMPTABLE
WHERE ((:START > ENDDAY)
OR (:FINISH < STARTDAY))
AND (PART=:PART)
AND (EMPNO=:EMPNO)
0
 
aubsCommented:
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
 
Andrey_KulikCommented:
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
 
aubsCommented:
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
 
Andrey_KulikCommented:
SELECT * FROM EMPTABLE
WHERE ((:START > ENDDAY)
OR (:FINISH < STARTDAY))
AND (PART=:PART)
AND (EMPNO=:EMPNO)

quite enough
0
 
SupWangAuthor Commented:
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
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.

All Courses

From novice to tech pro — start learning today.