SQL Challenge Time in State - 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)

SQL Challenge Time in State

Author

Topic

qlowell
Starting Member

4 Posts

Posted - 2008-06-25 : 16:50:12

I'm wondering if someone could offer suggestions for the most efficient method of accomplishing the following. There will be a lot of data (ie: more than 500K values). I've got to generate a state report from a table of data.

Data:
DateTime, Name, Value
--------------------------
6/20/2008 9:58:00, Compressor3, 0
6/20/2008 9:58:45, Compressor1, 0
6/20/2008 9:59:09, Compressor2, 1
6/20/2008 10:01:00, Compressor1, 1
6/20/2008 10:12:00, Compressor2, 0
6/20/2008 10:14:43, Compressor1, 0
6/20/2008 10:16:12, Compressor2, 1
6/20/2008 10:22:31, Compressor3, 1
etc.

I'm looking to create a report of all the states for any given range. In this example it would be from 10am - 11am.

Output
Name, StartDateTime, EndDateTime, State
Compressor1, 6/20/2008 10:00:00, 6/20/2008 10:01:00, 0
Compressor2, 6/20/2008 10:00:00, 6/20/2008 10:12:00, 1
Compressor3, 6/20/2008 10:00:00, 6/20/2008 10:22:31, 0
Compressor1, 6/20/2008 10:01:00, 6/20/2008 10:14:43, 1
Compressor2, 6/20/2008 10:12:00, 6/20/2008 10:16:12, 0

Some issues are:
- the data won't always be in the sequence 0-1-0-1 it could be 0-0-1-0 or 0-1-1-0. There are technical reasons for this but it happens.
- there won't necessarily be a value at the start and stop times requested.

I've gotten pretty close using a cross join but not quite and I'm really looking for the most efficient method to do this.

I appreciate any help.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-25 : 17:08:14

How is state calculated?

DECLARE	@Sample TABLE (dt DATETIME, Name VARCHAR(20), Value INT)

INSERT	@Sample
SELECT	'6/20/2008  9:58:00', 'Compressor3', 0 UNION ALL
SELECT	'6/20/2008  9:58:45', 'Compressor1', 0 UNION ALL
SELECT	'6/20/2008  9:59:09', 'Compressor2', 1 UNION ALL
SELECT	'6/20/2008 10:01:00', 'Compressor1', 1 UNION ALL
SELECT	'6/20/2008 10:12:00', 'Compressor2', 0 UNION ALL
SELECT	'6/20/2008 10:14:43', 'Compressor1', 0 UNION ALL
SELECT	'6/20/2008 10:16:12', 'Compressor2', 1 UNION ALL
SELECT	'6/20/2008 10:22:31', 'Compressor3', 1

;WITH Yak (dt, Name, Value, RecID)
AS (
	SELECT	dt,
		Name,
		Value,
		ROW_NUMBER() OVER (PARTITION BY Name ORDER BY dt)
	FROM	(
			SELECT	dt,
				Name,
				Value
			FROM	@Sample
			WHERE	dt >= '6/20/2008 10:00:00'
				AND dt < '6/20/2008 11:00:00'

			UNION

			SELECT	'6/20/2008 10:00:00',
				Name,
				NULL
			FROM	@Sample
		) AS d
)

SELECT		y1.Name,
		y1.dt,
		y2.dt,
		1
FROM		Yak AS y1
INNER JOIN	Yak AS y2 ON y2.Name = y1.Name
WHERE		y1.RecID = y2.RecID - 1
ORDER BY	y1.dt,
		y2.dt

E 12°55'05.25"
N 56°04'39.16"

qlowell
Starting Member

4 Posts

Posted - 2008-06-25 : 17:28:52

State is simply the max time in each state. So if the value is 1 then I need to look backward for the earliest 0. It may have been zero in between (ie. 0-1-1-0-0-0-0-0-1). If the value is zero then I need to look ahead until the latest 1. At least that's my thinking.

So to say this another way - when the value is one the machine is running when the value is zero the machine is not running.

thanks for your help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-25 : 17:32:36

[code]DECLARE @Sample TABLE (dt DATETIME, Name VARCHAR(20), Value INT)

INSERT @Sample
SELECT '6/20/2008 9:58:00', 'Compressor3', 0 UNION ALL
SELECT '6/20/2008 9:58:45', 'Compressor1', 0 UNION ALL
SELECT '6/20/2008 9:59:09', 'Compressor2', 1 UNION ALL
SELECT '6/20/2008 10:01:00', 'Compressor1', 1 UNION ALL
SELECT '6/20/2008 10:12:00', 'Compressor2', 0 UNION ALL
SELECT '6/20/2008 10:14:43', 'Compressor1', 0 UNION ALL
SELECT '6/20/2008 10:16:12', 'Compressor2', 1 UNION ALL
SELECT '6/20/2008 10:22:31', 'Compressor3', 1

;WITH Yak (dt, Name, RecID, maxDT)
AS (
SELECT dt,
Name,
ROW_NUMBER() OVER (PARTITION BY Name ORDER BY dt),
MAX(dt) OVER (PARTITION BY Name)
FROM (
SELECT dt,
Name
FROM @Sample
WHERE dt >= '6/20/2008 10:00:00'
AND dt < '6/20/2008 11:00:00'

UNION

SELECT '6/20/2008 10:00:00',
Name
FROM @Sample
) AS d
)

SELECT y1.Name,
y1.dt,
y2.dt,
CASE WHEN y2.dt = y2.maxDT THEN 1 ELSE 0 END AS [State]
FROM Yak AS y1
INNER JOIN Yak AS y2 ON y2.Name = y1.Name
WHERE y1.RecID = y2.RecID - 1
ORDER BY y1.Name,
y1.dt[/code]

E 12°55'05.25"
N 56°04'39.16"

qlowell
Starting Member

4 Posts

Posted - 2008-06-25 : 18:04:08

Very Cool.
It looks like it works but it will take me some time to verify.
When I run this on my data set state is always 0.

qlowell
Starting Member

4 Posts

Posted - 2008-06-26 : 08:37:24

Unfortunately, it doesn't actually work. Rather than try to explain it if you run this sample data you will see what I mean.

INSERT @Sample
SELECT '6/20/2008 9:58:00', 'Compressor1', 0 UNION ALL
SELECT '6/20/2008 10:01:00', 'Compressor1', 0 UNION ALL
SELECT '6/20/2008 10:04:09', 'Compressor1', 1 UNION ALL
SELECT '6/20/2008 10:07:00', 'Compressor1', 1 UNION ALL
SELECT '6/20/2008 10:12:00', 'Compressor1', 0 UNION ALL
SELECT '6/20/2008 10:14:43', 'Compressor1', 0 UNION ALL
SELECT '6/20/2008 10:16:12', 'Compressor1', 1 UNION ALL
SELECT '6/20/2008 10:22:00', 'Compressor1', 0 UNION ALL
SELECT '6/20/2008 10:28:43', 'Compressor1', 0 UNION ALL
SELECT '6/20/2008 10:36:12', 'Compressor1', 1 UNION ALL
SELECT '6/20/2008 10:42:31', 'Compressor1', 1 UNION ALL
SELECT '6/20/2008 10:54:18', 'Compressor1', 0

The output should look like this:
'Compressor1','6/20/2008 10:00:00','6/20/2008 10:04:09', 0
'Compressor1','6/20/2008 10:04:09','6/20/2008 10:12:00', 1
'Compressor1','6/20/2008 10:12:00','6/20/2008 10:16:12', 0
'Compressor1','6/20/2008 10:16:12','6/20/2008 10:22:00', 1
'Compressor1','6/20/2008 10:22:00','6/20/2008 10:36:12', 0
'Compressor1','6/20/2008 10:36:12','6/20/2008 10:54:18', 1

Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources