SQL needed

SQL Server Developer >> SQL needed

This is a multi-part message in MIME format.

------=_NextPart_000_0024_01C78680.B04FE3B0
Content-Type: text/plain;
charset="windows-1256"
Content-Transfer-Encoding: quoted-printable

Dear All
I have a table with fields SensorCode (nvarchar), TDate (datetime), =
and Value (float)
If the value exceeds 300 the sensor considered violates the rules.

I need sql statment to get the SensorCodes that have Values over 300 and =
violation duration

The result should be like this:
SensorCode, Avg(Values), ViolationStartDateTime, ViolationEndDateTime

Pls. help
Ahmed

------=_NextPart_000_0024_01C78680.B04FE3B0
Content-Type: text/html;
charset="windows-1256"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dwindows-1256">
<META content=3D"MSHTML 6.00.6000.16414" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Dear All</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>    I have a =
table with fields=20
SensorCode (nvarchar), TDate (datetime), and Value (float)</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>    If the value exceeds =
300 the=20
sensor considered violates the rules.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I need sql statment to get the =
SensorCodes that=20
have Values over 300 and violation duration</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>The result should be like =
this:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>SensorCode, Avg(Values), =
ViolationStartDateTime,=20
ViolationEndDateTime</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Pls. help</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Ahmed</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV></BODY></HTML>

------=_NextPart_000_0024_01C78680.B04FE3B0--

SQL Server203

SQL Server Developer >> SQL needed You have not posted any DDL or sample data, so here's a quick guess:

select SensorCode
,Avg(Values)
,ViolationStartDateTime
,ViolationEndDateTime
from <table>
where (Value > 300)
group by SensorCode
,ViolationStartDateTime
,ViolationEndDateTime

You may not apreciate the way data is grouped but you have to admit this
still makes a good guess.

For a more accurate answer post DDL and sample data.

ML

---
http://milambda.blogspot.com/

SQL Server Developer >> SQL needed Try the following. Note my assumption about the key of your table.

CREATE TABLE tbl (SensorCode NVARCHAR(10) NOT NULL, TDate DATETIME NOT NULL,
Value FLOAT NOT NULL, PRIMARY KEY (SensorCode,TDate));

SELECT SensorCode,
AVG(Value) AS AvgValue,
MIN(TDate) AS ViolationStartDateTime,
MAX(TDate) AS ViolationEndDateTime
FROM
(
SELECT t1.SensorCode, t1.Value, t1.TDate,
MIN(t2.TDate) AS XDate
FROM tbl AS t1
JOIN tbl AS t2
ON t1.SensorCode = t2.SensorCode
AND t1.Value > 300
AND t2.Value <=300
AND t1.TDate < t2.TDate
GROUP BY t1.SensorCode, t1.Value, t1.TDate
) AS T
GROUP BY SensorCode, XDate;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

SQL Server Developer >> SQL needed
> Dear All
> I have a table with fields SensorCode (nvarchar), TDate (datetime), and Value (float)
> If the value exceeds 300 the sensor considered violates the rules.
>
> I need sql statment to get the SensorCodes that have Values over 300 and violation duration
>
> The result should be like this:
> SensorCode, Avg(Values), ViolationStartDateTime, ViolationEndDateTime
>
> Pls. help
> Ahmed

Need more information. I'm making the following inference about DDL
and sample values:

CREATE TABLE MyTable (
SensorCode varchar(10) NOT NULL,
TDate datetime NOT NULL PRIMARY KEY,
Value float NOT NULL
)

INSERT INTO MyTable VALUES ('270','2007-04-01 09:30:00,127.8)
INSERT INTO MyTable VALUES ('281','2007-04-01 09:31:00,140.3)
INSERT INTO MyTable VALUES ('293','2007-04-01 09:32:00,160.12)
INSERT INTO MyTable VALUES ('307','2007-04-01 09:33:00,135.8)
INSERT INTO MyTable VALUES ('320','2007-04-01 09:34:00,142.9)
INSERT INTO MyTable VALUES ('320','2007-04-01 09:35:00,122.8)
INSERT INTO MyTable VALUES ('290','2007-04-01 09:36:00,172.14)
INSERT INTO MyTable VALUES ('280','2007-04-01 09:36:00,192.8)
INSERT INTO MyTable VALUES ('290','2007-04-01 09:37:00,112.10)
INSERT INTO MyTable VALUES ('320','2007-04-01 09:38:00,122.14)
INSERT INTO MyTable VALUES ('310','2007-04-01 09:39:00,152.8)
INSERT INTO MyTable VALUES ('290','2007-04-01 09:40:00,122.16)
INSERT INTO MyTable VALUES ('280','2007-04-01 09:41:00,132.10)
INSERT INTO MyTable VALUES ('270','2007-04-01 09:42:00,172.14)

Please explain what output you would want. Should the three entries
from 9:33 to 9:35 be grouped as two separate violations since the
SensorCode differs? Or as a single violation period spanning the
entire duration that the SensorCode was above 300? If the latter, what
SensorCode value is used to represent the violation?

SQL Server Developer >> SQL needed Thank you very much
this statemnet is very good

Ahmed Hashish

> Try the following. Note my assumption about the key of your table.
>
> CREATE TABLE tbl (SensorCode NVARCHAR(10) NOT NULL, TDate DATETIME NOT
> NULL, Value FLOAT NOT NULL, PRIMARY KEY (SensorCode,TDate));
>
> SELECT SensorCode,
> AVG(Value) AS AvgValue,
> MIN(TDate) AS ViolationStartDateTime,
> MAX(TDate) AS ViolationEndDateTime
> FROM
> (
> SELECT t1.SensorCode, t1.Value, t1.TDate,
> MIN(t2.TDate) AS XDate
> FROM tbl AS t1
> JOIN tbl AS t2
> ON t1.SensorCode = t2.SensorCode
> AND t1.Value > 300
> AND t2.Value <=300
> AND t1.TDate < t2.TDate
> GROUP BY t1.SensorCode, t1.Value, t1.TDate
> ) AS T
> GROUP BY SensorCode, XDate;
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>