Jason,

I think it can be done in Tableau with judicious filtering of the joins.

I performed the joins in Tableau Prep.

I joined AlertsSheet to MaintenanceSheet on MonitorID

and then joined that result to MonitorSheet on MonitorID

I tried adding a few alerts, but wasn't sure how you wanted to handle them.
I made one alert outside of any maintenance window, and another that

barely overlapped a maintenance window. I filtered out the former using:

[Start DateTime Alert]>[End DateTime Maint]

OR

[End DateTime Alert]<[Start DateTime Maint]

and kept the latter. But maybe you want to keep all even if completely

outside a maintenance window?

I calculated the uncovered portion on the start side using:

IF [Start DateTime Alert]>[Start DateTime Maint] THEN 0

ELSE DATEDIFF('second',[Start DateTime Alert],[Start DateTime Maint])/86400

END

and similarly for the uncovered end portion:

IF [End DateTime Alert]<[End DateTime Maint] THEN 0

ELSE DATEDIFF('second',[End DateTime Maint],[End DateTime Alert])/86400

END

This all may be completely off base.

Please see workbook v10.3, tflx, and xlsx attached in the Forum Thread:

Determining single field value off multiple rows in related data-source

Jason,

I'm not sure that I caught the drift, but I think the idea would be to join all alerts to all maintenance periods

(for one monitor) and then filter down the set to just those that are of interest.

Understandably, if your tables are prohibitively large, then this join would be suboptimal for performance.

But to reduce this, further conditions could be added to the join (only those on the same day, and even

same hour).

Would be grateful if you would adjust the previously attached xlsx to more closely match your scenario.