help write 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 2008 Forums

Transact-SQL (2008)

help write query

Author

Topic

grisha
Starting Member

9 Posts

Posted - 2012-04-21 : 15:43:38

There is a table with two fields:
-Dat, date / time (the interval between measurements varying)
-Val, is the value of the measurand

We need to find from this table start time and end time and the average measured value, all the time intervals when the value of the measured value over time @T was greater than @V

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-04-21 : 16:14:31

can you please provide sample data and desire output so that all can try
to solve your probs..

grisha
Starting Member

9 Posts

Posted - 2012-04-21 : 17:02:24

The original table
Dat Val
2012-04-06 10:24:31.0000000 16
2012-04-06 10:24:38.0000000 50
2012-04-06 10:25:06.0000000 28
2012-04-06 10:25:07.0000000 19
2012-04-06 10:25:26.0000000 25
2012-04-06 10:25:40.0000000 20
2012-04-06 10:25:55.0000000 31
2012-04-06 10:26:04.0000000 10
2012-04-06 10:26:20.0000000 14
2012-04-06 10:26:42.0000000 11
2012-04-06 10:26:45.0000000 41
2012-04-06 10:26:49.0000000 14
2012-04-06 10:27:15.0000000 44
2012-04-06 10:27:35.0000000 50
2012-04-06 10:27:59.0000000 33
2012-04-06 10:28:28.0000000 59
2012-04-06 10:28:37.0000000 39
2012-04-06 10:28:50.0000000 50
2012-04-06 10:29:15.0000000 53
2012-04-06 10:29:18.0000000 36
2012-04-06 10:29:26.0000000 31
2012-04-06 10:29:54.0000000 57
2012-04-06 10:30:14.0000000 49
2012-04-06 10:30:22.0000000 25

if V=30 and T=2 min
These conditions are responsible:

2012-04-06 10:27:15.0000000 44
2012-04-06 10:27:35.0000000 50
2012-04-06 10:27:59.0000000 33
2012-04-06 10:28:28.0000000 59
2012-04-06 10:28:37.0000000 39
2012-04-06 10:28:50.0000000 50
2012-04-06 10:29:15.0000000 53
2012-04-06 10:29:18.0000000 36
2012-04-06 10:29:26.0000000 31
2012-04-06 10:29:54.0000000 57
2012-04-06 10:30:14.0000000 49

So need to get

StartD EndD AvgVal
2012-04-06 10:27:15.0000000 2012-04-06 10:30:14.0000000 45.454

I gave an example where there is an interval satisfying the requirements, but these intervals may be several

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-21 : 17:03:39

how do you determine intervals? is it another user input?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

grisha
Starting Member

9 Posts

Posted - 2012-04-21 : 17:08:58

This constant

T>=2 min
and
V>=30

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-21 : 17:12:29

sorry how did you manage to get all those rows selected? Based on what calculation for v=30 and t=2?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

grisha
Starting Member

9 Posts

Posted - 2012-04-21 : 17:19:38

At first, I sort the table by date ([Dat])

Then look through all the records

I'm looking for when the value of [Val] more than 30 and remember the date of the event.
Then, when looking for [Val] fell below 30, and remember this date
If the duration of the interval found more than 2 minutes, then I store the start date intrval found, the end date found intrval, calculates the average value found in the [Val] intrval.
I continue to search for the next interval

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-21 : 18:43:06

[code]
;With CTE()
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY [Dat]) AS Rn,*
FROM table
)

SELECT MIN(c1.[Dat]) AS StartDate,MAX(c1.[Dat]) AS EndDate, AVG(Val * 1.0) AS MeasuredVal
FROM CTE c1
CROSS APPLY (SELECT TOP 1 [Dat]
FROM CTE
WHERE [Dat] > c.[Dat]
AND Val < 30
ORDER BY Rn
)c2
WHERE c1.Val >= 30
GROUP BY c2.[Dat]
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

grisha
Starting Member

9 Posts

Posted - 2012-04-21 : 19:47:16

Thanks for your help
Lacked only controls the time interval.
Necessary data calculated as

With CTE(Rn,DT,Val )
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY [Dt]) AS Rn,DT,Val
FROM table
)

SELECT MIN(c1.[DT]) AS StartDate, MAX(c1.[DT]) AS EndDate, DATEDIFF(minute, MIN(c1.[DT]), MAX(c1.[DT])) As Interval , AVG(Val * 1.0) AS MeasuredVal
FROM CTE c1
CROSS APPLY (SELECT TOP 1 [DT]
FROM CTE
WHERE [DT] > c1.[DT]
AND Val < 30
ORDER BY Rn
)c2
WHERE c1.Val >= 30
GROUP BY c2.[DT]
Having DATEDIFF(minute, MIN(c1.[DT]), MAX(c1.[DT]))>2

The resulting

StartDate EndDate Interval MeasuredVal
---------------------- ---------------------- ----------- ----------------------
2012-04-06 10:27:15.00 2012-04-06 10:30:14.00 3 45,4545454545455

As can be simplified in order not to calculate the query multiple times MIN(c1.[DT])
use
Having DATEDIFF(minute, StartDate, EndDate)>2
Not work!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-21 : 19:56:41

[code]
;With CTE(Rn,DT,Val )
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY [Dt]) AS Rn,DT,Val
FROM table
)
SELECT *
FROM
(
SELECT MIN(c1.[DT]) AS StartDate, MAX(c1.[DT]) AS EndDate, AVG(Val * 1.0) AS MeasuredVal
FROM CTE c1
CROSS APPLY (SELECT TOP 1 [DT]
FROM CTE
WHERE [DT] > c1.[DT]
AND Val < 30
ORDER BY Rn
)c2
WHERE c1.Val >= 30
GROUP BY c2.[DT]
)t
Having DATEDIFF(minute,StartDate, EndDate)>2
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

grisha
Starting Member

9 Posts

Posted - 2012-04-21 : 20:05:27

Like
Thanks
It work!

SELECT ROW_NUMBER() OVER (ORDER BY [Dt]) AS Rn,DT,Val
FROM table
)

SELECT t.StartDate, t.EndDate, t.MeasuredVal
FROM
(
SELECT MIN(c1.[DT]) AS StartDate, MAX(c1.[DT]) AS EndDate, AVG(Val * 1.0) AS MeasuredVal
FROM CTE c1
CROSS APPLY (SELECT TOP 1 [DT]
FROM CTE
WHERE [DT] > c1.[DT]
AND Val < 30
ORDER BY Rn
)c2
WHERE c1.Val >= 30
GROUP BY c2.[DT]
) t
GROUP BY t.StartDate, t.EndDate, t.MeasuredVal
Having DATEDIFF(minute, t.StartDate, t.EndDate)>2

With a large Volume of data would not it be faster to iterate over the sorted records cursor?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-22 : 13:15:33

nope...i dont think cursor will perform better. Can you test it using large dataset and report the result?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

grisha
Starting Member

9 Posts

Posted - 2012-04-23 : 14:15:52

i am create 73000 records
amd with cursor work 3 seconds
with CROSS APPLY 4.2 minute
:(

You can speed up the query without cursor ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-23 : 15:00:37

is this any better?


;With CTE(Rn,DT,Val )
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY [Dt]) AS Rn,DT,Val 
FROM table
)
SELECT *
FROM
(
SELECT MIN(c1.[DT]) AS StartDate, MAX(c1.[DT]) AS EndDate,  AVG(Val * 1.0) AS MeasuredVal
FROM CTE c1
CROSS APPLY (SELECT MIN([DT]) AS [DT]
FROM CTE 
WHERE [DT] > c1.[DT]
AND Val < 30
)c2
WHERE c1.Val >= 30
GROUP BY c2.[DT]
)t
Having DATEDIFF(minute,StartDate, EndDate)>2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

grisha
Starting Member

9 Posts

Posted - 2012-04-23 : 15:33:14

Error message:

Message 8121, Level 16, State 1, Line 21
The column "t.StartDate" is invalid in the HAVING, because it is not contained in an aggregate function or the sentence GROUP BY.
Message 8121, Level 16, State 1, Line 21
The column "t.EndDate" is invalid in the HAVING, because it is not contained in an aggregate function or the sentence GROUP BY.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-23 : 15:54:42

quote:
Originally posted by grisha

Error message:

Message 8121, Level 16, State 1, Line 21
The column "t.StartDate" is invalid in the HAVING, because it is not contained in an aggregate function or the sentence GROUP BY.
Message 8121, Level 16, State 1, Line 21
The column "t.EndDate" is invalid in the HAVING, because it is not contained in an aggregate function or the sentence GROUP BY.

I think you added it to wrong place as I dont think posted query will throw this error

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

grisha
Starting Member

9 Posts

Posted - 2012-04-23 : 16:33:47

Now does not work slower than with cursor.
But not quite understand how it works
will teach sql!

Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources