Bash Nie
Rank: #46402
22Views
11Comments
Solved
Adding dates that are not overlapping.
Question
Traditional Web
Application Type
Traditional Web
Service Studio Version
11.9.0 (Build 32956)

Hello,

I have the following problem..

  • I have a BeginDate and a EndDate in a form.
  • In an Aggregate I have a lot of records from the database with also BeginDates and EndDates. 
  • The record in the Form needs to be valid. This means that it only can be added to the database, if the BeginDate and EndDate from the form is not overlapping (unique) with ANY BeginDates and EndDates in the Aggregate.

Does anyone know how to solve this problem?
Thanks.


Bash Nie.

0
0
on 2020-11-23
Copy link to comment
Dorine Boudry
Rank: #128
Solution

Hi Bash,

If you don't want overlapping, I would execute an aggregate before adding the new record, with something like this for condition

aggregate.enddate >= form.begindate

AND aggregate.begindate <= form.enddate

AND aggregate.id <> form.id

If this query returns empty, you can go ahead and add/update your form record.


using >= or simply > is debatable, if one ends on same day as other starts, is that considered overlap or not...


Dorine

1
0
on 2020-11-23
Copy link to comment
Shingo Lam
Rank: #493

Unique key for begin and end date attribute is also a solution

0
0
on 2020-11-24
Copy link to comment

Hi Bash,

I would like to suggest you to use Advance SQL widget to check and validate your condition using query. I have done similar thing in a Hotel Booking app where a Room can not be booked more then once for same date.

If you can provide exact condition, I may probably help you writing query for that.


Sanjay

1
0
on 2020-11-24
Copy link to comment
Kilian Hekhuis
mvp_badge
MVP
Rank: #2

Hi Bash,

You aren't looking for overlapping dates, but overlapping periods (defined by their start and end date). Two periods A and B do not overlap when the end date of period B lies before the start date of A, or the start date of B lies after the end date of A. Visually:

So if you want to determine whether there's any overlap, you want to get all periods that do not conform to that filter. So say you have an Entity MyEvent that has StartDate and EndDate Attributes, you create an Aggregate that gets all MyEvent records with a Filter that says

not (MyEvent.StartDate > Form.Record.EndDate or MyEvent.EndDate < Form.Record.StartDate)
1
0
on 2020-11-24
Copy link to comment
Swapnil
Rank: #329

Add Unique index on begin date and end date attribute.  it will add records to be only when it is not overlapping

Hope this helps

Regards,

Swapnil

2
2
on 2020-11-24
Copy link to comment
Bash Nie
Rank: #46402

Thanks for all your help, it was really usefull. 

I marked the answer of Dorine as the solution, since she was the first.

Regards,

Bash Nie



0
0
on 2020-11-24
Copy link to comment