I've been told it's ok to post the query.Here is the SQL query that is causing the issue:
-- first, use windowing to find which events are related to each other. If the start_date is > max end_date of the window (all records that came before), then you are starting a new grouping, so is_start = 1
with related as
(
select *, case when start_date <= max(coalesce(end_date, '3000-01-01')) over (partition by oamt_barcode order by start_date rows between unbounded preceding and 1 preceding) then 0 else 1 end as is_start
from work.cmms_time_slice_events
where start_date is not null -- don't consider bad data quality records
and oamt_barcode is not null -- only oamt. Would do again for faults
and event_type_id in (36, 37, 38, 39, 40, 41, 44, 50, 52, 55, 56, 57) -- LR, CT, PR, etc. including uncat_delay
),
-- next, set a cluster_id for each row to relate them to others in the same group (overlapping)
clusters as
(
select oamt_barcode, start_date, end_date, sum(is_start) over (order by oamt_barcode rows unbounded preceding) as cluster_id -- sum works here because they are all 1s or 0s, and each cluster only has 1 start.
from related
),
-- build a single record to define periods where there are things happening. Any remaining gaps within the task boundaries will be uncat delay.
has_maint_drivers as
(
select oamt_barcode, cluster_id, min(start_date) as start_date, max(end_date) as end_date
from clusters
group by oamt_barcode, cluster_id
),
-- invert to find the gaps. This does *NOT* find the gaps between task/fault start and earliest maintenance driver, nor between the last maintenance driver and task/fault complete, which will need to be counted
inverse as
(
select oamt_barcode, end_date as start_date, min(start_date) over(partition by oamt_barcode order by start_date rows between 1 following and 2 following) as end_date
from has_maint_drivers
)
-- need to update this step to include the misses from the last step
select 'UNCAT_OAMT_GAP_' + i.oamt_barcode +'_' + convert(varchar,i.start_date,120) as remarks
, i.oamt_barcode as source_id
,44 as event_type_id
,tr.tail_number
,i.oamt_barcode
,tr.parent_fault_barcode as fault_barcode
,i.start_date
,i.end_date
,'DND' as accountability
from inverse i
left join source.cmms_task_relationships tr on i.oamt_barcode = tr.barcode
left join source.cmms_faults f on f.barcode = tr.parent_fault_barcode
where i.end_date is not null and i.end_date <> i.start_date
and (f.deferred_date not between i.start_date and i.end_date or f.barcode is null or f.deferred_date is null)
and tr.element_type = 'OAMT' and tr.is_current = 1 and coalesce(f.is_current,1) = 1
order by oamt_barcode, remarks, start_date,end_date
Here are the results of the query (only the date fields which is the issue:
Good Results Bad Results
start_date end_date start_date end_date
2018-06-22 01:02:00 2018-06-22 13:04:25 2018-06-21 23:35:40 2018-06-21 23:03:48
2018-06-22 17:57:00 2018-06-22 19:19:00 2018-06-22 13:52:13 2018-06-22 13:05:10
2018-06-22 19:49:00 2018-06-22 19:55:00 2018-06-22 19:49:00 2018-06-22 19:55:00
2018-06-23 03:00:00 2018-06-25 10:18:00 2018-06-22 23:25:00 2018-06-22 21:00:00
2018-06-25 10:36:00 2018-06-25 15:24:00 2018-06-25 10:36:00 2018-06-25 15:24:00
2018-06-25 15:30:00 2018-06-27 14:31:00 2018-06-25 15:30:00 2018-06-27 14:31:00
