Query - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Query

Author

Topic

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2008-09-09 : 14:00:29

Hello All,

I was hoping that someone could help me avoid cursors in my problem. See the code below...


--===== Create the Point table 

 CREATE TABLE #PointTable 
        (
        ID         INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
        PointName  VARCHAR(50),
        DateValue  DATETIME,
        InRange    VARCHAR(1))
        -- InRange is 'Y' if the recorded point is good 
        --         or 'N' if the recorded point is bad

--===== Insert the data into the Point table
--===== For each point, there SHOULD be a record about every 15 minutes.
 INSERT INTO #PointTable 
       ( PointName, DateValue, InRange)
 SELECT 'A', '2004-05-23T14:15:01','Y' UNION ALL
 SELECT 'A', '2004-05-23T14:30:10','N' UNION ALL
-- Note that there is a missing record here.
 SELECT 'A', '2004-05-23T15:00:05','N' UNION ALL
 SELECT 'A', '2004-05-23T15:14:32','Y' UNION ALL
 SELECT 'A', '2004-05-23T15:29:25','Y' UNION ALL
 SELECT 'A', '2004-05-23T15:44:45','N' UNION ALL
 SELECT 'A', '2004-05-23T15:59:01','Y' UNION ALL
 SELECT 'A', '2004-05-23T16:15:10','N' UNION ALL
 SELECT 'A', '2004-05-23T16:30:12','Y' UNION ALL
 SELECT 'A', '2004-05-23T16:46:05','N' UNION ALL
 SELECT 'B', '2004-05-23T14:15:32','N' UNION ALL
 SELECT 'B', '2004-05-23T14:31:25','N' UNION ALL
 SELECT 'B', '2004-05-23T14:45:45','Y' UNION ALL
 SELECT 'B', '2004-05-23T15:00:05','Y' UNION ALL
 SELECT 'C', '2004-05-23T14:15:32','N' UNION ALL
 SELECT 'C', '2004-05-23T14:30:25','N' UNION ALL
 SELECT 'C', '2004-05-23T14:45:45','N' UNION ALL
 SELECT 'C', '2004-05-23T15:00:59','Y'

I need to locate machine failures: that is, get all PNames having at least 3 'N' records in any 35 minute window. For the data above, only point 'C' is bad. 'B' only has 2 records within 3 minutes. 'A' has several bad points, but none within 35 minutes of each other.

Thanks in advance
-P

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-09-09 : 14:58:49

Try this.


CREATE TABLE #PointTable 
        (
        ID         INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
        PointName  VARCHAR(50),
        DateValue  DATETIME,
        InRange    VARCHAR(1))
        -- InRange is 'Y' if the recorded point is good 
        --         or 'N' if the recorded point is bad

--===== Insert the data into the Point table
--===== For each point, there SHOULD be a record about every 15 minutes.
 INSERT INTO #PointTable 
       ( PointName, DateValue, InRange)
 SELECT 'A', '2004-05-23T14:15:01','Y' UNION ALL
 SELECT 'A', '2004-05-23T14:30:10','N' UNION ALL
-- Note that there is a missing record here.
 SELECT 'A', '2004-05-23T15:00:05','N' UNION ALL
 SELECT 'A', '2004-05-23T15:14:32','Y' UNION ALL
 SELECT 'A', '2004-05-23T15:29:25','Y' UNION ALL
 SELECT 'A', '2004-05-23T15:44:45','N' UNION ALL
 SELECT 'A', '2004-05-23T15:59:01','Y' UNION ALL
 SELECT 'A', '2004-05-23T16:15:10','N' UNION ALL
 SELECT 'A', '2004-05-23T16:30:12','Y' UNION ALL
 SELECT 'A', '2004-05-23T16:46:05','N' UNION ALL
 SELECT 'B', '2004-05-23T14:15:32','N' UNION ALL
 SELECT 'B', '2004-05-23T14:31:25','N' UNION ALL
 SELECT 'B', '2004-05-23T14:45:45','Y' UNION ALL
 SELECT 'B', '2004-05-23T15:00:05','Y' UNION ALL
 SELECT 'C', '2004-05-23T14:15:32','N' UNION ALL
 SELECT 'C', '2004-05-23T14:30:25','N' UNION ALL
 SELECT 'C', '2004-05-23T14:45:45','N' UNION ALL
 SELECT 'C', '2004-05-23T15:00:59','Y'


select	a.PointName
from  
	#PointTable  a
where 
	(	Select Count(*)
		from #PointTable  aa 
		where 
			aa.POintName = a.PointName 
		and aa.DateValue between a.DateValue and dateadd(minute,35,a.DateValue) 
		and aa.InRange = 'N'
	) >=3
and   a.InRange = 'N'
group by a.PointName

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2008-09-09 : 19:31:15

Thank you Vinnie881. This query gave the correct results. It worked the way I wanted.

Thanks again
-P

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources