February 14, 2017 at 6:10 am
This is going to be a difficult one to explain, I'll do my best:
DECLARE @StartDate AS DATE = '1/1/2017'
DECLARE @EndDate AS DATE = '1/31/2017'
SELECT Radio_Name,
Date_Time,
CAST(Date_Time AS DATE) AS DOA,
Activity
FROM Activity_Log
WHERE
(Activity = 'AVL Failure' OR
Activity = 'AVL Restart')
AND CAST(Date_Time AS DATE) >= @StartDate
AND CAST(Date_Time AS DATE) <= @EndDate
ORDER BY DOA, Radio_Name, Date_Time
This query returns the data I need, but I need to parse it further. An "AVL Restart" indicates a connection by an AVL unit, and an "AVL Failure" indicates a disconnection. What my supervisors are asking for is only for disconnects, and how long the disconnect was for. The problem is the logic isn't consistent. While a connect will always be followed by a disconnect and vice-versa, the log will not always start with a disconnect or connect and not end that way either. So I'm unsure how to code a LEAD query to sort through and only show disconnects that have a connect on the same day right after, to show the total amount of time the unit was disconnected... if that makes sense.
*edit*
Adding table structure and sample data:
COLUMN_NAME DATA_TYPE
ID bigint
Date_Time datetime
Activity varchar
Radio_Name varchar
See attachment for sample data
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 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply