TSQL query help
-
Hi All,
Need small help on TSQL query.
There is a table with server info , status and rundate of a script. Basically they is a script which runs against the servers and if the script fine, it updates status column.
if script fails, it updates the table with status = failed.My requirement is, if a server has failed status for 3 consequetive days/dates, then in the output show those list of servers along with the count of failures.
so, in the output I need to see Srv4 and Srv5 along with no of days it is failing and status column as 'Failed'.Below is some dummy data.
create table #temp
( Srvname varchar(100),
status varchar(100),
run_dt datetime
)insert into #temp
select 'Srv1','Success','2017-02-20 11:50:20.010'
union all
select 'Srv1','Success','2017-02-19 11:50:37.490'
union all
select 'Srv1','Success','2017-02-18 11:50:47.400'
union all
select 'Srv1','Success','2017-02-17 11:50:20.010'
union all
select 'Srv2','Failed','2017-02-20 11:50:20.010'
union all
select 'Srv2','Success','2017-02-19 11:50:37.490'
union all
select 'Srv2','Failed','2017-02-18 11:50:47.400'
union all
select 'Srv2','Success','2017-02-17 11:50:20.010'
union all
select 'Srv3','Success','2017-02-20 11:50:20.010'
union all
select 'Srv3','Success','2017-02-19 11:50:20.010'
union all
select 'Srv3','Success','2017-02-18 11:50:20.010'
union all
select 'Srv3','Success','2017-02-17 11:50:20.010'
union all
select 'Srv4','Failed','2017-02-20 11:50:20.010'
union all
select 'Srv4','Failed','2017-02-19 11:50:20.010'
union all
select 'Srv4','Failed','2017-02-18 11:50:20.010'
union all
select 'Srv4','Failed','2017-02-17 11:50:20.010'
union all
select 'Srv5','Failed','2017-02-20 11:50:20.010'
union all
select 'Srv5','Failed','2017-02-19 11:50:20.010'
union all
select 'Srv5','Failed','2017-02-18 11:50:20.010'
union all
select 'Srv5','Failed','2017-02-17 11:50:20.010'select * from #temp
order by srvnameThank you.
-
This is a 'Gaps and Islands' question. You're looking for all islands of size 4 or greater.
http://www.sqlservercentral.com/articles/T-SQL/71550/ -
I think this works... test it on a bigger dataset:
SELECT *
FROM (
SELECT SrvName
, [status]
, run_dt
, ROW_NUMBER() OVER (PARTITION BY SrvName, [Status] ORDER BY run_dt) AS FailNumber
FROM #temp
) x
WHERE FailNumber>3
AND [status] = 'Failed';Doing a Count of Failures per server from here is just a totals query.
- pietlinden - Monday, February 20, 2017 12:32 AM
Not sure that solution works properly, if you add in a success row to the middle of Srv5, it still showing as consecutive failures.
insert into #temp(Srvname, status, run_dt)
values('Srv5','Success','2017-02-18 22:50:20.010');Here a couple of queries to try
SELECT SrvName, [Status], COUNT(*) AS NoOfDays
FROM (
SELECT SrvName
, [status]
, run_dt
, ROW_NUMBER() OVER (PARTITION BY SrvName ORDER BY run_dt) AS rn1
, ROW_NUMBER() OVER (PARTITION BY SrvName, [Status] ORDER BY run_dt) AS rn2
FROM #temp
) x
GROUP BY SrvName, [Status], rn1-rn2
HAVING COUNT(*) > 3
AND [status] = 'Failed';WITH cte1 AS (
SELECT Srvname, [Status], run_dt,
CASE WHEN LAG([Status]) OVER(PARTITION BY Srvname ORDER BY run_dt) = [Status] THEN 0 ELSE 1 END AS isstart
FROM #temp
),
cte2 AS (
SELECT Srvname, [Status], run_dt, SUM(isstart) OVER(PARTITION BY Srvname ORDER BY run_dt) AS rv
FROM cte1
)
SELECT Srvname, [Status], COUNT(*) AS NoOfDays
FROM cte2
GROUP BY Srvname, [Status], rv
HAVING COUNT(*) >= 3 AND [Status] = 'Failed'
ORDER BY srvname;____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Just so folks are aware, pietlinden's solution will work, but may have incorrect results if that script fails to run on a given day. If that fact is detected and corrected, that's fine, but if it's not, you could potentially identify a failure group that shouldn't actually be one because let's assume that the day the script failed is also a day no failures occur, but the 2 days prior had a failure, and the day after also did (there is at least one other similar scenario). The script he provided will NOT detect that missing date, but because the 3 days of data that it has (in the sample data) would all be failures and would be in sequence, it would identify a failure, when had the script run on all days, it would not have identified it.
Here's a query that will identify missing data:
SET NOCOUNT ON;CREATE table #temp (
Srvname varchar(100),
[status] varchar(100),
run_dt datetime
);INSERT INTO #temp (Srvname, [status], run_dt)
SELECT 'Srv1','Success','2017-02-20 11:50:20.010'
UNION ALL
SELECT 'Srv1','Success','2017-02-19 11:50:37.490'
UNION ALL
SELECT 'Srv1','Success','2017-02-18 11:50:47.400'
UNION ALL
SELECT 'Srv1','Success','2017-02-17 11:50:20.010'
UNION ALL
SELECT 'Srv2','Failed','2017-02-20 11:50:20.010'
UNION ALL
SELECT 'Srv2','Success','2017-02-19 11:50:37.490'
UNION ALL
SELECT 'Srv2','Failed','2017-02-18 11:50:47.400'
UNION ALL
SELECT 'Srv2','Success','2017-02-17 11:50:20.010'
UNION ALL
SELECT 'Srv3','Success','2017-02-20 11:50:20.010'
UNION ALL
SELECT 'Srv3','Success','2017-02-19 11:50:20.010'
UNION ALL
SELECT 'Srv3','Success','2017-02-18 11:50:20.010'
UNION ALL
SELECT 'Srv3','Success','2017-02-17 11:50:20.010'
UNION ALL
SELECT 'Srv4','Failed','2017-02-20 11:50:20.010'
UNION ALL
SELECT 'Srv4','Failed','2017-02-19 11:50:20.010'
UNION ALL
SELECT 'Srv4','Failed','2017-02-18 11:50:20.010'
UNION ALL
SELECT 'Srv4','Failed','2017-02-17 11:50:20.010'
UNION ALL
SELECT 'Srv5','Failed','2017-02-20 11:50:20.010'
UNION ALL
SELECT 'Srv5','Failed','2017-02-19 11:50:20.010'
UNION ALL
SELECT 'Srv5','Failed','2017-02-18 11:50:20.010'
UNION ALL
SELECT 'Srv5','Failed','2017-02-17 11:50:20.010'
UNION ALL
SELECT 'Srv6','Failed','2017-02-27 11:50:20.010'
UNION ALL
SELECT 'Srv6','Failed','2017-02-26 11:50:20.010'
UNION ALL
SELECT 'Srv6','Failed','2017-02-24 11:50:20.010'
UNION ALL
SELECT 'Srv6','Failed','2017-02-23 11:50:20.010';/*
SELECT *
FROM #temp
ORDER BY Srvname, run_dt;
*/DECLARE @MIN_DATE AS date;
DECLARE @MAX_DATE AS date;SELECT @MIN_DATE = MIN(run_dt), @MAX_DATE = MAX(run_dt)
FROM #temp;WITH NUMBERS AS (
SELECT 1 AS N UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
ALL_DATES AS (SELECT TOP (DATEDIFF(day, @MIN_DATE, @MAX_DATE) + 1)
DATEADD(day, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, @MIN_DATE) AS THE_DATE
FROM NUMBERS AS N1, NUMBERS AS N2, NUMBERS AS N3, NUMBERS AS N4
),
ALL_SERVERS AS (SELECT DISTINCT Srvname
FROM #temp
),
ALL_SERVERS_ALL_DATES AS (SELECT S.Srvname, D.THE_DATE
FROM ALL_SERVERS AS S
CROSS APPLY ALL_DATES AS D
),
FAILURE_GROUPS AS (SELECT T.Srvname, D.THE_DATE, T.[status],
DATEDIFF(day, LAG(D.THE_DATE, 2) OVER(PARTITION BY T.Srvname ORDER BY D.THE_DATE), D.THE_DATE) AS LAG2_DIFF,
DATEDIFF(day, LAG(D.THE_DATE, 1) OVER(PARTITION BY T.Srvname ORDER BY D.THE_DATE), D.THE_DATE) AS LAG1_DIFF,
DATEDIFF(day, D.THE_DATE, LEAD(D.THE_DATE, 1) OVER(PARTITION BY T.Srvname ORDER BY D.THE_DATE)) AS LEAD1_DIFF,
DATEDIFF(day, D.THE_DATE, LEAD(D.THE_DATE, 2) OVER(PARTITION BY T.Srvname ORDER BY D.THE_DATE)) AS LEAD2_DIFF,
CASE
WHEN DATEDIFF(day, D.THE_DATE, LEAD(D.THE_DATE, 1) OVER(PARTITION BY T.Srvname ORDER BY D.THE_DATE)) = 1
AND DATEDIFF(day, D.THE_DATE, LEAD(D.THE_DATE, 2) OVER(PARTITION BY T.Srvname ORDER BY D.THE_DATE)) = 2 THEN 1
WHEN DATEDIFF(day, LAG(D.THE_DATE, 1) OVER(PARTITION BY T.Srvname ORDER BY D.THE_DATE), D.THE_DATE) = 1
AND DATEDIFF(day, D.THE_DATE, LEAD(D.THE_DATE, 1) OVER(PARTITION BY T.Srvname ORDER BY D.THE_DATE)) = 1 THEN 1
WHEN DATEDIFF(day, LAG(D.THE_DATE, 2) OVER(PARTITION BY T.Srvname ORDER BY D.THE_DATE), D.THE_DATE) = 2
AND DATEDIFF(day, LAG(D.THE_DATE, 1) OVER(PARTITION BY T.Srvname ORDER BY D.THE_DATE), D.THE_DATE) = 1 THEN 1
ELSE 0
END AS IS_FAILURE_GROUP
FROM #temp AS T
INNER JOIN ALL_DATES AS D
ON CAST(T.run_dt AS date) = D.THE_DATE
WHERE T.[status] = 'Failed'
),
MISSING_DATA AS (SELECT DISTINCT SD.Srvname + ' - MISSING DATA' AS Srvname, CAST(NULL AS int) AS FailureCount,
SD.THE_DATE AS MinFailDate, SD.THE_DATE AS MaxFailDate
FROM ALL_SERVERS_ALL_DATES AS SD
LEFT OUTER JOIN #temp AS T
ON SD.Srvname = T.Srvname
AND SD.THE_DATE = CAST(T.run_dt AS date)
WHERE T.Srvname IS NULL
)
SELECT G.Srvname, COUNT(*) AS FailureCount, MIN(G.THE_DATE) AS MinFailDate, MAX(G.THE_DATE) AS MaxFailDate
FROM FAILURE_GROUPS AS G
WHERE G.IS_FAILURE_GROUP = 1
GROUP BY G.Srvname
UNION ALL
SELECT Srvname, FailureCount, MinFailDate, MaxFailDate
FROM MISSING_DATA
ORDER BY Srvname;DROP TABLE #temp;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Here's a very simple alternative which also might work:
SELECT
Srvname,
[status] = 'Failed',
MIN(run_dt),
MAX(run_dt),
grp = dd-rn,
ConsecutiveDays = COUNT(*)
FROM (
SELECT *,
dd = DATEDIFF(DAY,0,run_dt),
rn = ROW_NUMBER() OVER(PARTITION BY Srvname ORDER BY run_dt)
FROM #temp
WHERE status = 'Failed'
) d
GROUP BY Srvname, dd-rn
HAVING COUNT(*) > 3
[font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]For better assistance in answering your questions, please read this[/url].
Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply