Merge tables based on date range

[leemsiska]

Hello, 

A bit stuck and confused with the table merge process. I have two tables, Date Range, which has my daily date range of when a job should be executed on a particular day, and with that I have an SLA for that day. Then I have the actual job execution time - start and end time. I would like to merge both tables based on the job name, and the range which that job executed on the day falls under. In the even that the job execution for the day is out of range or did not trigger, I should be able to indicate that in the output table. Possibly merge is not the best solution, I just need to be able to look up the Date Range table with the start time to determine if that job executed on the day, then using End time to compare if that is > than SLA time, then mark it as SLA Breach.

Date Range
Job Name	Start Time Range	End Time Range	SLA TIME
Batch JOB A	9/12/2021 23:30	9/13/2021 6:30	9/13/2021 2:30
Batch JOB B	9/12/2021 23:30	9/13/2021 6:30	9/13/2021 2:30
Batch JOB C	9/12/2021 23:30	9/13/2021 6:30	9/13/2021 2:30
Batch JOB D	9/12/2021 23:30	9/13/2021 6:30	9/13/2021 2:30
Batch JOB A	9/13/2021 23:30	9/14/2021 6:30	9/14/2021 2:30
Batch JOB B	9/13/2021 23:30	9/14/2021 6:30	9/14/2021 2:30
Batch JOB C	9/13/2021 23:30	9/14/2021 6:30	9/14/2021 2:30
Batch JOB D	9/13/2021 23:30	9/14/2021 6:30	9/14/2021 2:30

Job Execution
Job Name	Start Time	End Time
Batch JOB A	9/12/2021 23:59	9/13/2021 0:32
Batch JOB B	9/13/2021 1:25	9/13/2021 2:45
Batch JOB C	9/13/2021 7:30	9/13/2021 9:32
Batch JOB D	9/12/2021 23:47	9/12/2021 23:53
Batch JOB A	9/14/2021 2:31	9/14/2021 3:12
Batch JOB B	9/14/2021 9:30	9/14/2021 10:01

Desired results

Job Name	Start Time Range	End Time Range	SLA TIME	Job Name	Start Time	End Time	SLA Breach	Job Not Triggered in Range
Batch JOB A	9/12/2021 23:30	9/13/2021 6:30	9/13/2021 2:30	Batch JOB A	9/12/2021 23:59	9/13/2021 0:32	N
Batch JOB B	9/12/2021 23:30	9/13/2021 6:30	9/13/2021 2:30	Batch JOB B	9/13/2021 1:25	9/13/2021 2:45	N
Batch JOB C	9/12/2021 23:30	9/13/2021 6:30	9/13/2021 2:30					Y
Batch JOB D	9/12/2021 23:30	9/13/2021 6:30	9/13/2021 2:30	Batch JOB D	9/12/2021 23:47	9/12/2021 23:53	N
Batch JOB A	9/13/2021 23:30	9/14/2021 6:30	9/14/2021 2:30	Batch JOB A	9/14/2021 0:31	9/14/2021 3:12	Y
Batch JOB B	9/13/2021 23:30	9/14/2021 6:30	9/14/2021 2:30					Y
Batch JOB C	9/13/2021 23:30	9/14/2021 6:30	9/14/2021 2:30					Y
Batch JOB D	9/13/2021 23:30	9/14/2021 6:30	9/14/2021 2:30					Y

Jobs that will not match 

Batch JOB B	9/14/2021 9:30	9/14/2021 10:01
Batch JOB C	9/13/2021 7:30	9/13/2021 9:32

I am pulling data from Azure App Insight via a query 

[Anonymous]

Hi @leemsiska ,

According to your description, I did the following

1.Merge two tables

2.Add a custom column named Job Not Triggered in Range.

3.Add a conditional column named SLA Breach.

Result:

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[leemsiska]

Hi @Anonymous

Thanks for responding to the request for help. I followed your steps and did not quite resolve the issue. Partly this is due to my fault as I did not present the scenario exactly as my actual data set. In the example I shared earlier, this assumes a 1:1 match between the two tables. In actual fact, table on the left (Date Range) has more records than the execution table, as this could be due to the job missing from schedule. My data set has 376 records on left and 151 on right table 

[Anonymous]

Hi @leemsiska ,

I believe Merge can complete your operation. Merge also has several join types you can choose to try.

About join types, you can refer to

Choose the Right Merge Join Type in Power BI

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.