• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 160
  • Last Modified:

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
0
SupWang
Asked:
SupWang
  • 5
  • 5
  • 2
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
Andrey_KulikCommented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now