SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 View : Report consecutives wrong values
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rischfre
Starting Member

Spain
15 Posts

Posted - 07/02/2014 :  12:51:30  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 07/02/2014 :  13:24:58  Show Profile  Reply with Quote
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
Go to Top of Page

rischfre
Starting Member

Spain
15 Posts

Posted - 07/02/2014 :  16:07:26  Show Profile  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 07/02/2014 :  16:50:58  Show Profile  Reply with Quote
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

Edited by - Lamprey on 07/02/2014 16:51:32
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 07/02/2014 :  18:03:26  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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]);



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

rischfre
Starting Member

Spain
15 Posts

Posted - 07/03/2014 :  11:29:47  Show Profile  Reply with Quote
Thank you very much, your SQL has been very usefull.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000