Array find items with overlapping dates

[nycnfc1]

Hi all,

    I am using PA, Sharepoint, teams, etc to make a time off request process and have most of it done now working on some formatting stuff. I would like to add a column to mark when the requests (sharepoint items) have overlapping start or end dates. I have my items in an array that look like the following in Powerautomate.

[
  {
    "id": "231",
    "start_date": "2024-09-26",
    "end_date": "2024-10-02",
    "rank": "S7"
  },
  {
    "id": "267",
    "start_date": "2024-09-23",
    "end_date": "2024-10-01",
    "rank": "S6"
  },
  {
    "id": "133",
    "start_date": "2024-07-29",
    "end_date": "2024-08-02",
    "rank": "S12"
  },
  {
    "id": "236",
    "start_date": "2024-07-01",
    "end_date": "2024-07-12",
    "rank": "S9"
  },
  {
    "id": "223",
    "start_date": "2024-07-01",
    "end_date": "2024-07-05",
    "rank": "S2"
  },
  {
    "id": "248",
    "start_date": "2024-03-12",
    "end_date": "2024-03-12",
    "rank": "S12"
  }
]

I was trying to do a for each on this array, see if there were items with overlapping dates and if so add them to another array (to then mark the formatting column as true). For example, the first two entries in the list above have overlapping days from Sept 26 to October 1. I thought I could do this with filter array, but I can't seem to figure out the UI on this.. 

How could I find the overlapping dates using Powerautomate?? 

[v-yueyun-msft]

Hi , @nycnfc1 

Thanks for your sample array json , i am not surely understand "overlapping dates" in your side. Can you give me an detailed example output which you need in the end?

Best Regards,

Yueyun Zhang

[nycnfc1]

Thanks for the reply.. I guess I should have put sample data that showed what I mean, I added one for the example. These are requests for vacation and I have a filtered set of a certain department that can only have one or two people out on the same day. So, I am trying to find if a request will overlap with other requests and deny it.

That would be something like this:

RequestList list comes from the filtered Sharepoint list data, CurrentRequest is the submitted form.

Apply to each RequestList (foreach RequestList as Request):

    IF ( CurrentRequest['STARTDATE'] between Request ['STARTDATE'] and Request ['ENDDATE'] ) OR ( CurrentRequest['ENDDATE'] between Request ['STARTDATE'] and Request ['ENDDATE'] )

          Add Request to an array (OverlappingRequestList)

If OverlappingRequestList > 0

    Update Sharepoint Items by ID in the request list and the current request.

And I can't figure out the syntax of how to input this.. I was playing with Filter Array, that should do what I want I think. 

Edit: I realized I messed up my logic.. But the comparison isn't exactly my problem in the first place. 

[Chriddle · Accepted solution]

The idea is to add a new property (date_arr) with an array of days to each item.

Overlapping dates are just an intersection of these arrays

Compose

Your array

Spoiler

[
  {
    "id": "231",
    "start_date": "2024-09-26",
    "end_date": "2024-10-02",
    "rank": "S7"
  },
  {
    "id": "133",
    "start_date": "2024-07-29",
    "end_date": "2024-08-02",
    "rank": "S12"
  },
  {
    "id": "236",
    "start_date": "2024-07-01",
    "end_date": "2024-07-12",
    "rank": "S9"
  },
  {
    "id": "223",
    "start_date": "2024-07-01",
    "end_date": "2024-07-05",
    "rank": "S2"
  },
  {
    "id": "248",
    "start_date": "2024-03-12",
    "end_date": "2024-03-12",
    "rank": "S12"
  }
]

Initialize variable

Name: NewData

Type: Array

Value: []

Apply to each

outputs('Compose')

Select

From

range(
	0,
	if(
		greater(
			indexOf(
				dateDifference(items('Apply_to_each')['start_date'], items('Apply_to_each')['end_date']),
				'.'
			),
			0
		),
		add(int(first(split(dateDifference(items('Apply_to_each')['start_date'], items('Apply_to_each')['end_date']), '.'))), 1),
		1
	)
)

 Map

addDays(items('Apply_to_each')['start_date'], item(), 'yyyy-MM-dd')

Append to array variable

Name: NewData

Value

addProperty(items('Apply_to_each'), 'date_arr', body('Select'))

Compose 2

Calculates the overlapping days of the 3rd and 4th item in the array

intersection(
	variables('NewData')[2]['date_arr'],
	variables('NewData')[3]['date_arr']
)

[nycnfc1]

Thanks this is a really helpful and detailed reply! Not exactly what I am trying to achieve in that compose, but I should be able to get it by looping through the results.. Rather than getting the specific days of overlap, I need the ID of the overlapping requests.  

Thanks again!