October 27, 2014 at 9:41 am
I've got an interesting puzzle that I'm really struggling to get my head around. I work with a system that receives events from field systems. One of these events is a system status check represented here by incidenttype 1, basically it's an 'everything's OK alarm'. If this event isn't received, another event, incidenttype 6, is generated by the system. What I need to find is the first occasion that a 6 occurs for a particular combination of service, order and equipment then the next time the combination calls in i.e. an event other than a 6 is received and return these on the same row.
I think this is a lot more complicated than it first seems. Each combination of equipment, order and service can have multiple bouts of call fails on any day, each subject can have more than one service and each service can have more than one set of equipment assigned on a given day.
I've tried to show the different cases below. In my examples I need to find the EventDate from RowNo 1 and the NextDate from RowNo 2 because this these are the first and last examples in this bout of failures. I then need to return the EventDate from row 3 and the NextDate from row 4, in this case the equipment being changed ends the bout of failures. I then need to return both the Eventdate and NextDate from row 5 because this was a one-off failure i.e. it was immediately followed by another event. After that I need the Eventdate from row 6 and NextDate from row 7. I then need the EventDate from row 8 and the NextDate from row 13. Finally I need the EventDate from row 14 and the NextDate from row 16.
I'm pretty sure that a Tally table will be the answer to this but I've no idea where to fit it in. The number of different permutations of event, service, date\time and equipment do not make things easy.
use tempdb
go
create table #tmp_History
(
ServiceIDsmallint
,IncidentTypeID tinyint
,IncidentDatedatetime
)
create table #tmp_service
(
ServiceIDsmallint primary key
,LocationID smallint
,OrderIDsmallint
)
create table #tmp_unit
(
EquipmentTypechar(7)
,EquipmentIDint
,LocationIDsmallint
,StartDatedatetime
,EndDatedatetime
)
insert into #tmp_History
select 10002,1,dateadd(minute,-1480,Getdate()) union all ---System check-in; occurs every 60 minutes unless another event is received
select 10002,4,dateadd(minute,-1460,Getdate()) union all --Normal event
select 10002,1,dateadd(minute,-1400,Getdate()) union all
select 10002,1,dateadd(minute,-1340,Getdate()) union all
select 10002,1,dateadd(minute,-1280,Getdate()) union all
select 10002,6,dateadd(minute,-1220,Getdate()) union all -- Check-in fail; occurs every 60 minutes when check-in is not received
select 10002,6,dateadd(minute,-1160,Getdate()) union all
select 10002,6,dateadd(minute,-1100,Getdate()) union all
select 10002,1,dateadd(minute,-1040,Getdate()) union all -- Check-ins can resume without another event to trigger them
select 10002,5,dateadd(minute,-1011,Getdate()) union all
select 10002,1,dateadd(minute,-951,Getdate()) union all --Next check-in occurs 60 minutes after event type 5
select 10002,1,dateadd(minute,-891,Getdate()) union all
select 10002,1,dateadd(minute,-831,Getdate()) union all
select 10002,1,dateadd(minute,-771,Getdate()) union all
select 10002,1,dateadd(minute,-711,Getdate()) union all
select 10000,1,dateadd(minute,-1380,Getdate()) union all
select 10000,1,dateadd(minute,-1440,Getdate()) union all
select 10000,1,dateadd(minute,-1320,Getdate()) union all
select 10000,1,dateadd(minute,-1260,Getdate()) union all
select 10000,6,dateadd(minute,-1200,Getdate()) union all
select 10000,6,dateadd(minute,-1140,Getdate()) union all
select 10000,7,dateadd(minute,-1057,Getdate()) union all -- Any other event causes check-ins to be resumed
select 10000,1,dateadd(minute,-997,Getdate()) union all
select 10000,1,dateadd(minute,-937,Getdate()) union all
select 10000,6,dateadd(minute,-877,Getdate()) union all -- Random check-in fail
select 10000,6,dateadd(minute,-817,Getdate()) union all
select 10000,6,dateadd(minute,-757,Getdate()) union all -- Last event from old unit not always the same
select 10000,9,dateadd(minute,-710,Getdate()) union all -- First event from the new unit, not always the same
select 10000,1,dateadd(minute,-650,Getdate()) union all -- Check-ins resume on new unit
select 10000,6,dateadd(minute,-590,Getdate()) union all --Failure on new unit
select 10000,1,dateadd(minute,-530,Getdate()) union all
select 10001,1,dateadd(minute,-1450,Getdate()) union all
select 10001,6,dateadd(minute,-1390,Getdate()) union all
select 10001,6,dateadd(minute,-1330,Getdate()) union all
select 10001,1,dateadd(minute,-1270,Getdate()) union all
select 10001,7,dateadd(minute,-1258,Getdate()) union all
select 10001,6,dateadd(minute,-1188,Getdate()) union all
select 10001,6,dateadd(minute,-1128,Getdate()) union all
select 10001,6,dateadd(minute,-1068,Getdate()) union all
select 10001,6,dateadd(minute,-1008,Getdate()) union all
select 10001,6,dateadd(minute,-948,Getdate()) union all
select 10001,6,dateadd(minute,-888,Getdate()) union all
select 10001,1,dateadd(minute,-828,Getdate()) union all
select 10001,1,dateadd(minute,-768,Getdate()) union all
select 10001,1,dateadd(minute,-708,Getdate()) union all
select 10001,1,dateadd(minute,-648,Getdate()) union all
select 10001,1,dateadd(minute,-588,Getdate())
insert into #tmp_service
select 10000, 20000, 30000 union all
select 10001, 20001, 30001 union all
select 10002, 20002, 30000
insert into #tmp_unit
select 'ThisOne',345678, 20000, '2014-10-21 19:45:56.526', dateadd(minute,-712,Getdate()) union all
select 'ThisOne',345670, 20002, '2014-10-22 22:53:01.289', null union all
select 'ThisOne',345679, 20000, dateadd(minute,-710,Getdate()), null union all
select 'ThatOne',345678, 20001,'2014-10-20 18:37:18.478',null
/*This is the query I'm using to find the initial tranche of events*/
select
ts.orderid
,ts.serviceid
,th.incidenttypeid
,th.incidentdate
,Kit = tu.equipmenttype + cast(tu.equipmentid as varchar)
,RowNo = row_number() over (partition by th.serviceid, tu.equipmenttype, tu.equipmentid order by th.incidentdate)
into #tmp_event
from #tmp_history th
join #tmp_service ts on ts.serviceid = th.serviceid
join #tmp_unit tuon tu.locationid = ts.locationid
where tu.startdate <= th.incidentdate
and isnull(tu.enddate,getdate()) >= th.incidentdate
order by th.incidentdate
/*This is the query I'm using to find the next event after a 6 event*/
select
RowNo = row_number() over (order by te.serviceid,te.incidentdate) --This is not part of the query. I've included it to allow identification of the different rows.
,te.Orderid
,te.serviceid
,te.kit
,EventDate = te.incidentdate
,EventType = te.incidenttypeid
,NextDate = ne.incidentdate
,NextType = ne.incidenttypeid
from #tmp_event te
join #tmp_event ne on ne.serviceId = te.serviceid
and ne.kit = te.kit
where ne.rowno = te.rowno + 1
and te.incidenttypeid = 6
order by te.serviceid
drop table #tmp_history, #tmp_service,#tmp_unit,#tmp_event
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
October 27, 2014 at 10:37 pm
Nice problem, just what's needed to get the brain started in the morning:-D
😎
/* BASE_DATA brings together everything that is needed, adds
a group identifier for {service,order,equipment} INC_RNK
and a sequence number for each group.
*/
;WITH BASE_DATA AS
(
SELECT
TH.IncidentDate
,CAST(TH.IncidentTypeID AS INT) AS IncidentTypeID
,DENSE_RANK() OVER
(
PARTITION BY (SELECT NULL)
ORDER BY TH.ServiceID
,TS.OrderID
,TU.EquipmentID
) AS INC_RNK
,ROW_NUMBER() OVER
(
PARTITION BY TH.ServiceID
,TS.OrderID
,TU.EquipmentID
ORDER BY TH.IncidentDate
) AS INC_RID
,TH.ServiceID
,TS.OrderID
,TU.EquipmentID
,TU.LocationID
,TU.EquipmentType
FROM #tmp_History TH
INNER JOIN #tmp_service TS
ON TH.ServiceID = TS.ServiceID
INNER JOIN #tmp_unit TU
ON TS.LocationID = TU.LocationID
)
/* FLAGGED_SET joins two instances of the BASE_DATA set for:
1. Filter out subsequent events of the same type
2. Mark start events where previous event ne 6 and current eq 6
3. Mark end events where previous event eq 6 and the current ne 6
NOTE all event filtering happens here, additions and exclusions should
be limited to the logic of the start and end flags.
*/
,FLAGGED_SET AS
(
SELECT
BD.INC_RID
,BD.INC_RNK
,CASE WHEN ABS(SIGN(BD.IncidentTypeID - B2.IncidentTypeID)) = 1 AND BD.IncidentTypeID = 6 THEN 1 ELSE 0 END AS START_FLG
,CASE WHEN ABS(SIGN(BD.IncidentTypeID - B2.IncidentTypeID)) = 1 AND B2.IncidentTypeID = 6 THEN 1 ELSE 0 END AS END_FLG
,BD.OrderID
,BD.ServiceID
,BD.EquipmentType
,BD.EquipmentID
,BD.IncidentDate
,BD.IncidentTypeID
FROM BASE_DATA BD
LEFT OUTER JOIN BASE_DATA B2
ON BD.INC_RNK = B2.INC_RNK
AND BD.INC_RID = B2.INC_RID + 1
)
/* ALL_INTRESTING_EVENTS filters the set, adding a
sequence number for self-join in the final output.
*/
,ALL_INTRESTING_EVENTS AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY FS.INC_RNK
ORDER BY FS.INC_RID
) AS INC_KEY
,FS.INC_RNK
,FS.OrderID
,FS.ServiceID
,FS.EquipmentType
,FS.EquipmentID
,FS.IncidentDate
,FS.IncidentTypeID
,FS.START_FLG
,FS.END_FLG
FROM FLAGGED_SET FS
WHERE FS.END_FLG = 1
OR FS.START_FLG = 1
)
/* Final set with yet another self join
for the end date and type.
*/
SELECT
ESTART.OrderID AS OrderID
,ESTART.ServiceID AS ServiceID
,ESTART.EquipmentTypeAS EquipmentType
,ESTART.EquipmentID AS EquipmentID
,ESTART.IncidentDate AS EventDate
,ESTART.IncidentTypeID AS EventType
,EEND.IncidentDate AS NextDate
,EEND.IncidentTypeID AS NextType
FROM ALL_INTRESTING_EVENTS ESTART
INNER JOIN ALL_INTRESTING_EVENTS EEND
ON ESTART.INC_RNK = EEND.INC_RNK
AND ESTART.INC_KEY = EEND.INC_KEY - 1
WHERE ESTART.START_FLG = 1
AND EEND.END_FLG = 1;
Results
OrderID ServiceID EquipmentType EquipmentID EventDate EventType NextDate NextType
------- --------- ------------- ----------- ----------------------- ----------- ----------------------- -----------
30000 10000 ThisOne 345678 2014-10-27 08:37:25.473 6 2014-10-27 11:00:25.473 7
30000 10000 ThisOne 345678 2014-10-27 14:00:25.473 6 2014-10-27 16:47:25.473 9
30000 10000 ThisOne 345678 2014-10-27 18:47:25.473 6 2014-10-27 19:47:25.473 1
30000 10000 ThisOne 345679 2014-10-27 08:37:25.473 6 2014-10-27 11:00:25.473 7
30000 10000 ThisOne 345679 2014-10-27 14:00:25.473 6 2014-10-27 16:47:25.473 9
30000 10000 ThisOne 345679 2014-10-27 18:47:25.473 6 2014-10-27 19:47:25.473 1
30001 10001 ThatOne 345678 2014-10-27 05:27:25.473 6 2014-10-27 07:27:25.473 1
30001 10001 ThatOne 345678 2014-10-27 08:49:25.473 6 2014-10-27 14:49:25.473 1
30000 10002 ThisOne 345670 2014-10-27 08:17:25.473 6 2014-10-27 11:17:25.473 1
October 28, 2014 at 4:27 am
Thanks Eirikur, I'll have a look at that as soon as I get chance. I think that the only things I hadn't tried in one combination or another were the ABS and SIGN which I'll dig into later.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply