View : Report consecutives wrong values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

View : Report consecutives wrong values

Author

Topic

rischfre
Starting Member

15 Posts

Posted - 2014-07-02 : 12:51:30

Hi

I have a table with such structure :


CREATE TABLE [dbo].[Tb_Data](
	[Id_Agent] [int] NOT NULL,
	[Status] [bit] NOT NULL,
	[Timestamp] [int] NOT NULL
)

this table stored for each Agent (ID_Agent) its status (Status) at a moment in time (Timestamp).

so for each agent may be false or true during several timestamp. How can i report how many consecutive Status for a given agen were wrong (and when).

1,0,1000

1,1,1010

1,0,1020

1,0,1030

1,1,1040

1,1,1050

1,1,1060

1,0,1070

1,0,1080

2,0,1000

2,0,1010

2,0,1020

2,1,1030

2,0,1040

2,0,1050

I would expect :
1,10,1010 (Agente 1 was wrong 10 seconds (1020-1010) starting at 1010
1,30,1040 (Agente 1 was wrong 30 seconds (1070-1040) starting at 1040
2,10,1030 (Agente 2 was wrong 10 seconds (1040-1030) starting at 1030

As i have to relationate in the same table consecutives values i even do not know how to start creating the view.

Thank you for your help / advise

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-07-02 : 13:24:58

[code]DECLARE @Tb_Data TABLE (
[Id_Agent] [int] NOT NULL,
[Status] [bit] NOT NULL,
[Timestamp] [int] NOT NULL
)

INSERT @Tb_Data VALUES

(1,0,1000 ),
(1,1,1010 ),
(1,0,1020 ),
(1,0,1030 ),
(1,1,1040 ),
(1,1,1050 ),
(1,1,1060 ),
(1,0,1070 ),
(1,0,1080 ),
(2,0,1000 ),
(2,0,1010 ),
(2,0,1020 ),
(2,1,1030 ),
(2,0,1040 ),
(2,0,1050 )

;WITH Cte1 AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY ID_Agent ORDER BY TimeStamp ASC) RowNum
FROM @Tb_Data
),
Cte AS
(
SELECT *, RowNum - ROW_NUMBER() OVER (PARTITION BY ID_Agent ORDER BY TimeStamp ASC) GroupNum
FROM Cte1
WHERE Status = 1
)

SELECT Id_Agent, COUNT(*) * 10, MIN(TimeStamp)
FROM Cte
GROUP BY Id_Agent, GroupNum
[/code]

rischfre
Starting Member

15 Posts

Posted - 2014-07-02 : 16:07:26

Thanks that would help if in between each timestamp there are always 10 but it's not the case.
Sometimes they are bigger or smaller. I have to find the biggest and the smallest of each interval in order to make the difference.

Thank you very much for the effor

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-07-02 : 16:50:58

Might be a better way, but you could use the query I posted with a outer apply can to the original table:

SELECT
	T.Id_Agent, D.MaxTime - T.MinTime, T.MinTime
FROM
(
	SELECT Id_Agent, MAX(TimeStamp) AS MaxTime, MIN(TimeStamp)  AS MinTime
	FROM Cte AS Cte
	GROUP BY Id_Agent, GroupNum
) AS T
OUTER APPLY
(
	SELECT MIN(Timestamp) AS MaxTime
	FROM @Tb_Data
	WHERE Id_Agent = T.Id_Agent
	AND Timestamp > T.MaxTime
) AS D

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-02 : 18:03:26

[code]SELECT s.ID_Agent,
ISNULL(f.[Timestamp], s.[Timestamp]) - MIN(s.[Timestamp]) AS Period,
MIN(s.[Timestamp]) AS FromTime
FROM @Tb_Data AS s
OUTER APPLY (
SELECT TOP(1) x.[Timestamp]
FROM @Tb_Data AS x
WHERE x.ID_Agent = s.ID_Agent
AND x.[Timestamp] > s.[Timestamp]
AND x.[Status] = 0
ORDER BY x.[Timestamp]
) AS f
WHERE s.[Status] = 1
GROUP BY s.ID_Agent,
ISNULL(f.[Timestamp], s.[Timestamp]);[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

rischfre
Starting Member

15 Posts

Posted - 2014-07-03 : 11:29:47

Thank you very much, your SQL has been very usefull.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources