Consecutive timespan

Question

Post reply

Consecutive timespan

christopher.traveis

Newbie

Points: 6
More actions
January 27, 2009 at 3:48 pm

#128270

Hello all,
I have been working on a query where i need to find when a value is > 90 for 30 consecutive minutes. I have a query that can find all of the times where the value was greater than 90 but i cannot figure out how to tell if it had happened for 30 consecutive minutes. The original query is in SQL Server 2005 and I call a view in SSRS.
SELECT
STP.FacilityDisplayName,
STP.WsgDisplayName,
STP.ZoneDisplayName,
STP.SystemDisplayName,
STP.ElementDisplayName,
STP.PointTypeDisplayName,
ROUND(CONVERT(decimal(10,2),F.PointValue), 5) AS PointValue,
STP.PointTypeUnitSymbol AS Unit,
D.NormalDate,
T.NormalTime,
F.DateID,
F.TimeID,
STP.ElementTypeFullName,
STP.PointTypeID,
STP.ElementID,
STP.SystemID,
STP.FacilityID
FROM
tbl_hca_DimSiteToPoint STP
INNER JOIN tbl_hca_FactSensor F ON F.SiteToPointID = STP.SiteToPointID
INNER JOIN uv_hca_NormalDate D ON D.Day_Key = F.DateID
INNER JOIN uv_hca_NormalTime T ON T.Time_Key = F.TimeID
WHERE ISNUMERIC(PointValue) = 1
AND STP.PointTypeID = '51D00464-C53D-4DDD-990D-C2068291C2BD'
AND STP.ElementTypeFullName = 'Cooling Coil'
There condition to tell whether or not the value is over 90 is in SSRS.
Another thing is lets say the value was > 90 for 15 minutes dropped below on the 16th minute and went back above 90 on the 17th minute, the 17th minute would now need to become minute one and the evaluation would need to start over.
Thanks,
Christopher

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Andy Reilly Hall of Fame Points: 3677 More actions · Answer 1

Sorry but without table structures and some data it would be very difficult to recreate your query

Can you post the table create statement and some dummy data to work with

Cheers

Andy

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 2

Christopher,

Andrew is correct... take a look at the link in my signature below to help us help you. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

christopher.traveis Newbie Points: 6 More actions · Answer 3

Hello again,

Forgive my amateur mistake, my apologies.

Here is an example of the table along with values:

IF EXISTS

(

SELECT TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = '#mytable'

)

DROP TABLE #mytable

CREATE TABLE #mytable

(

FacilityDisplayName nvarchar(64) NULL,

WsgDisplayName nvarchar(64) NULL,

ZoneDisplayName nvarchar(64) NULL,

SystemDisplayName nvarchar(64) NULL,

ElementDisplayName nvarchar(64) NULL,

PointTypeDisplayName nvarchar(64) NULL,

PointValue nvarchar(255) NULL,

PointTypeUnitSymbol nvarchar(255) NULL,

NormalDate nvarchar(97) NULL,

NormalTime nvarchar(95) NULL,

DateID int NOT NULL,

TimeID int NOT NULL

)

SET IDENTITY_INSERT #mytable ON

INSERT INTO #mytable

(FacilityDisplayName, WsgDisplayName, ZoneDisplayName, SystemDisplayName, ElementDisplayName,

PointTypeDisplayName, PointValue, PointTypeUnitSymbol, NormalDate, NormalTime, DateID, TimeID)

SELECT 'Facility1','WSG1','Zone1','System1','Element1','PointType1',91,'%','01/21/2009','1:00 AM','20090121','100' UNION ALL

SELECT 'Facility1','WSG1','Zone1','System1','Element1','PointType1',100,'%','01/21/2009','1:03 AM','20090121','103' UNION ALL

SELECT 'Facility1','WSG1','Zone1','System1','Element1','PointType1',93,'%','01/21/2009','1:07 AM','20090121','107' UNION ALL

SELECT 'Facility1','WSG1','Zone1','System1','Element1','PointType1',99,'%','01/21/2009','1:12 AM','20090121','112' UNION ALL

SELECT 'Facility1','WSG1','Zone1','System1','Element1','PointType1',98,'%','01/21/2009','1:15 AM','20090121','115' UNION ALL

SELECT 'Facility1','WSG1','Zone1','System1','Element1','PointType1',90,'%','01/21/2009','1:21 AM','20090121','121' UNION ALL

SELECT 'Facility1','WSG1','Zone1','System1','Element1','PointType1',89,'%','01/21/2009','1:26 AM','20090121','126' UNION ALL

SELECT 'Facility1','WSG1','Zone1','System1','Element1','PointType1',85,'%','01/21/2009','1:32 AM','20090121','132' UNION ALL

SELECT 'Facility1','WSG1','Zone1','System1','Element1','PointType1',90,'%','01/21/2009','1:40 AM','20090121','140' UNION ALL

SELECT 'Facility1','WSG1','Zone1','System1','Element1','PointType1',91,'%','01/21/2009','1:43 AM','20090121','143' UNION ALL

SELECT 'Facility1','WSG1','Zone1','System1','Element1','PointType1',91,'%','01/21/2009','1:50 AM','20090121','150' UNION ALL

SELECT 'Facility1','WSG1','Zone1','System1','Element1','PointType1',91,'%','01/21/2009','1:54 AM','20090121','154' UNION ALL

SELECT 'Facility1','WSG1','Zone1','System1','Element1','PointType1',91,'%','01/21/2009','1:59 AM','20090121','159' UNION ALL

SELECT 'Facility1','WSG1','Zone1','System1','Element1','PointType1',91,'%','01/21/2009','2:06 AM','20090121','206' UNION ALL

SELECT 'Facility1','WSG1','Zone1','System1','Element1','PointType1',91,'%','01/21/2009','2:11 AM','20090121','211'

SET IDENTITY_INSERT #mytable OFF

And here is an updated version of the query:

SELECT

FacilityDisplayName,

WsgDisplayName,

ZoneDisplayName,

SystemDisplayName,

ElementDisplayName,

PointTypeDisplayName,

ROUND(CONVERT(decimal(10,2),PointValue), 5) AS PointValue,

PointTypeUnitSymbol AS Unit,

NormalDate,

NormalTime,

DateID,

TimeID

FROM #mytable

This is ultimately going to be for a report, and what I am trying to do is walk the values of the table and show where the PointValue is > 90% for 30 consecutive minutes.

The time is not uniform because the values are only inserted into the table when they are polled. I had started looking into cursors but then read a post by you Jeff that said they were awfully slow.