Forum Discussion
jahill
May 26, 2023Copper Contributor
SQL Server Query returns different results from SQL Server 2014 to SQL Server 2019
We are in the processes of migrating to SQL Server 2019 from SQL Server 2014. Our regression testing revealed an issue with 2019. We are executing a query that involves 4 CTE's, some of which use p...
jahill
Jun 01, 2023Copper Contributor
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
-- 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
- olafhelperJun 02, 2023Bronze Contributor
jahill , we still have no chance to reproduce the reported behaviour, because we don't have your database.
Create a srcipt with tables+data+query, where we would be able with to reproduce it.