February 14, 2017 at 6:20 am
Can you please post the table ddl and sample data?
😎
February 14, 2017 at 6:33 am
Guess work here, however, maybe...:USE DevTestDB;
GO
CREATE TABLE #ActivityLog (Radio_name VARCHAR(20),
Date_time DATETIME,
Activity VARCHAR(30));
GO
INSERT INTO #ActivityLog
VALUES ('BBC Radio 1', DATEADD(MINUTE, -4320, GETDATE()), 'Connect'),
('BBC Radio 1', DATEADD(MINUTE, -4290, GETDATE()), 'Disconnect'),
('BBC Radio 1', DATEADD(MINUTE, -4280, GETDATE()), 'Connect'),
('BBC Radio 1', DATEADD(MINUTE, -4200, GETDATE()), 'Disonnect'),
('BBC Radio 1', DATEADD(MINUTE, -4000, GETDATE()), 'Disconnect'), --Intentional Anomalous data
('BBC Radio 1', DATEADD(MINUTE, -3999, GETDATE()), 'Connect'),
('BBC Radio 1', DATEADD(MINUTE, -3500, GETDATE()), 'Disconnect'),
('BBC Radio 1', DATEADD(MINUTE, -2100, GETDATE()), 'Connect'),
('BBC Radio 1', DATEADD(MINUTE, -2059, GETDATE()), 'Connect'), --More intentional Anomalous data
('BBC Radio 1', DATEADD(MINUTE, -1000, GETDATE()), 'Disconnect'),
('BBC Radio 1', DATEADD(MINUTE, -600, GETDATE()), 'Connect'),
('BBC Radio 1', DATEADD(MINUTE, -300, GETDATE()), 'Disconnect'),
('BBC Radio 1', DATEADD(MINUTE, 300, GETDATE()), 'Connect'),
('BBC Radio 1', DATEADD(MINUTE, 650, GETDATE()), 'Disconnect'),
('BBC Radio 1', DATEADD(MINUTE, 800, GETDATE()), 'Connect'),
('BBC Radio 1', DATEADD(MINUTE, 1200, GETDATE()), 'Disconnect');
SELECT *
FROM #ActivityLog;
GO
SELECT C.Radio_name,
C.Date_time,
CAST(C.Date_time AS date) AS DOA,
C.Activity,
DATEDIFF(Minute, D.Date_time, C.Date_time) AS Prior_Disconnect_Minutes
FROM #ActivityLog C
OUTER APPLY (SELECT TOP 1 *
FROM #ActivityLog ca
WHERE ca.Activity = 'Disconnect'
AND ca.Date_time <= C.Date_time
AND ca.Radio_name = C.Radio_name
ORDER BY ca.Date_time DESC) D
WHERE C.Activity = 'Connect'
AND CAST(C.Date_time AS date) BETWEEN CAST(DATEADD(DAY, -1, GETDATE()) AS date) AND CAST(GETDATE() AS date);
DROP TABLE #ActivityLog;
GO
That doesn't quite work as I put anomalous data in there, but I have no idea if your data is going to include that or not. Should be a starting point though, maybe.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 14, 2017 at 9:16 am
Thom,
Your answer got me well within the ballpark where I needed to be. I was expecting to have to use LEAD, but this seems to work better, albeit a little slower. At this point, speed doesn't matter to be, the correct result does. Thank you x1 mil!!
Viewing 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply