Query question - 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 question

Author

Topic

djorre
Yak Posting Veteran

94 Posts

Posted - 2009-06-21 : 10:46:28

Sorry people I already have another question

Because of your very good help I could create this query/table:

TimeStamp Sensor1
2009-06-23 14:32:04.417 1
2009-06-23 14:32:28.017 1
2009-06-23 14:33:32.020 1
2009-06-23 14:33:42.017 NULL
2009-06-23 14:33:51.013 1

Because this is a puls-logging of an electricity measure device (100imp/kWh), this 1's need to be replaced by the time between them, and that is an indication for power consumption (Watt).

So I need to convert this (assume its a table) into

TimeStamp Sensor1
2009-06-23 14:32:04.417 0
2009-06-23 14:32:28.017 24 seconds
2009-06-23 14:33:32.020 4 seconds
2009-06-23 14:33:42.017 NULL
2009-06-23 14:33:51.013 19 seconds

I'm sorry for asking but for you guys it seems so easy : )

greetings Joris

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-21 : 10:59:24

[code]
DECLARE @data TABLE
(
[timestamp] datetime,
Sensor1 int
)
INSERT INTO @data
SELECT '2009-06-23 14:32:04.417', 1 UNION ALL
SELECT '2009-06-23 14:32:28.017', 1 UNION ALL
SELECT '2009-06-23 14:32:32.020', 1 UNION ALL
SELECT '2009-06-23 14:33:42.017', NULL UNION ALL
SELECT '2009-06-23 14:33:51.013', 1

;WITH data ([timestamp], Sensor1, row_no)
AS
(
SELECT [timestamp], Sensor1,
row_no = row_number() OVER (ORDER BY [timestamp])
FROM @data
)
SELECT d1.[timestamp],
Sensor1 = CASE WHEN d1.Sensor1 IS NOT NULL
THEN DATEDIFF(second, ISNULL(d2.[timestamp], d1.[timestamp]), d1.[timestamp])
ELSE NULL
END
FROM data d1
left JOIN data d2 ON d1.row_no = d2.row_no + 1
WHERE d1.row_no > d2.row_no
OR d2.row_no IS NULL

/*
timestamp Sensor1
------------------------- -----------
2009-06-23 14:32:04.417 0
2009-06-23 14:32:28.017 24
2009-06-23 14:32:32.020 4
2009-06-23 14:33:42.017 NULL
2009-06-23 14:33:51.013 9

(5 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

djorre
Yak Posting Veteran

94 Posts

Posted - 2009-06-21 : 12:05:28

Last value should not be 9 is should be 19

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-21 : 12:06:28

quote:
Originally posted by djorre

Last value should not be 9 is should be 19

why ? pls explain

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-21 : 12:13:08

this ? skip the NULL line when calculating the time diff ?


DECLARE @data TABLE
(
	[timestamp]	datetime, 
	Sensor1		int
)
INSERT INTO @data
SELECT	'2009-06-23 14:32:04.417', 1	UNION ALL
SELECT	'2009-06-23 14:32:28.017', 1	UNION ALL
SELECT	'2009-06-23 14:33:32.020', 1	UNION ALL
SELECT	'2009-06-23 14:33:42.017', NULL	UNION ALL
SELECT	'2009-06-23 14:33:51.013', 1 

;WITH data ([timestamp], Sensor1, row_no)
AS
(
	SELECT	[timestamp], Sensor1, 
		row_no = row_number() OVER (PARTITION BY Sensor1 ORDER BY [timestamp])
	FROM	@data
)
SELECT	d1.[timestamp], 
	Sensor1 = CASE 	WHEN d1.Sensor1 IS NOT NULL
			THEN DATEDIFF(second, ISNULL(d2.[timestamp], d1.[timestamp]), d1.[timestamp])
			ELSE NULL
			END
FROM	data d1
	left JOIN data d2	ON	d1.row_no = d2.row_no + 1
				AND	d1.Sensor1	IS NOT NULL
				AND	d2.Sensor1	IS NOT NULL
WHERE	d1.row_no	> d2.row_no
OR	d2.row_no	IS NULL
ORDER BY d1.[timestamp]

/*
timestamp                                              Sensor1     
------------------------------------------------------ ----------- 
2009-06-23 14:32:04.417                                0
2009-06-23 14:32:28.017                                24
2009-06-23 14:33:32.020                                64
2009-06-23 14:33:42.017                                NULL
2009-06-23 14:33:51.013                                19

(5 row(s) affected)
*/

KH
[spoiler]Time is always against us[/spoiler]

djorre
Yak Posting Veteran

94 Posts

Posted - 2009-06-21 : 12:18:39

sorry it needed to be indeed just like you say with 64 and 19, i will test it immediatebly

djorre
Yak Posting Veteran

94 Posts

Posted - 2009-06-21 : 13:14:52

Think I do something wrong this is the code

DECLARE @data TABLE
(
[Tijd] datetime,
Opbrengst int
)

DECLARE @xdata TABLE
(
[Tijd] datetime,
Opbrengst int
)

quote:
INSERT INTO @data
select
isnull(tabel1.thetime,tabel2.thetime) as Tijd,
tabel1.opbrengst FROM
(
select cast(s2.thetime as datetime) as thetime, s1.opbrengst
from LabJackLog...Logging#csv as s1
join LabJackLog...Logging#csv as s2
on round(s2.thetime*24*60*60,1) = round(s1.thetime*24*60*60,1)-0.2 and
s1.opbrengst = 1 and
s2.opbrengst = 0
) as tabel1

full outer join
(
select cast(s2.thetime as datetime) as thetime, s1.verbruik
from LabJackLog...Logging#csv as s1
join LabJackLog...Logging#csv as s2
on round(s2.thetime*24*60*60,1) = round(s1.thetime*24*60*60,1)-0.2 and
s1.verbruik = 1 and
s2.verbruik = 0
) as tabel2

on tabel1.thetime = tabel2.thetime
;WITH data ([Tijd], Opbrengst, row_no)
AS
(
SELECT [Tijd], Opbrengst,
row_no = row_number() OVER (ORDER BY [Tijd])
FROM @data --where Opbrengst is not null-- this solved it
)

--select * from data--
Tijd Opbrengst row_no
2009-06-23 18:28:43.817 1 1
2009-06-23 18:29:00.013 NULL 2
2009-06-23 18:30:17.017 1 3
2009-06-23 18:31:53.817 1 4
2009-06-23 18:33:31.817 1 5
2009-06-23 18:33:52.013 NULL 6
2009-06-23 18:34:21.417 1 7
2009-06-23 18:34:34.017 NULL 8
2009-06-23 18:35:06.833 NULL 9
2009-06-23 18:35:11.813 1 10
2009-06-23 18:35:39.417 NULL 11
2009-06-23 18:36:44.817 NULL 12

INSERT into @xdata
SELECT tabel5.Tijd,
tabel5.Opbrengst
FROM
(
SELECT top 100 percent d1.[Tijd],
Opbrengst = CASE WHEN d1.Opbrengst IS NOT NULL
THEN DATEDIFF(second, ISNULL(d2.[Tijd], d1.[Tijd]), d1.[Tijd])
ELSE NULL
END
FROM data d1
left JOIN data d2 ON d1.row_no = d2.row_no + 1
AND d1.Opbrengst IS NOT NULL
AND d2.Opbrengst IS NOT NULL
WHERE d1.row_no > d2.row_no
OR d2.row_no IS NULL
ORDER BY d1.[Tijd]) as tabel5

--select * from @xdata--
Tijd Opbrengst
2009-06-23 18:28:43.817 0
2009-06-23 18:29:00.013 NULL
2009-06-23 18:30:17.017 0
2009-06-23 18:31:53.817 96
2009-06-23 18:33:31.817 98
2009-06-23 18:33:52.013 NULL
2009-06-23 18:34:21.417 0
2009-06-23 18:34:34.017 NULL
2009-06-23 18:35:06.833 NULL
2009-06-23 18:35:11.813 0
2009-06-23 18:35:39.417 NULL
2009-06-23 18:36:44.817 NULL

djorre
Yak Posting Veteran

94 Posts

Posted - 2009-06-21 : 13:42:30

I added from @data where Opbrengst is not null this gives me

Tijd Opbrengst Verbruik
2009-06-23 18:28:43.817 0 NULL
2009-06-23 18:29:00.013 NULL 0
2009-06-23 18:30:17.017 94 NULL
2009-06-23 18:31:53.817 96 NULL
2009-06-23 18:33:31.817 98 NULL
2009-06-23 18:33:52.013 NULL 292
2009-06-23 18:34:21.417 50 NULL
2009-06-23 18:34:34.017 NULL 42
2009-06-23 18:35:06.833 NULL 32
2009-06-23 18:35:11.813 50 NULL
2009-06-23 18:35:39.417 NULL 33
2009-06-23 18:36:44.817 NULL 65

Seems fine to me thanks a lot!!!

quote:

USE [LabJackLogging]
GO
/****** Object: StoredProcedure [dbo].[sp_test2] Script Date: 06/21/2009 19:46:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_test2]

AS
BEGIN

DECLARE @data TABLE
(
[Tijd] datetime,
Opbrengst int
)

DECLARE @xdata TABLE
(
[Tijd] datetime,
Opbrengst int
)

DECLARE @data2 TABLE
(
[Tijd] datetime,
Verbruik int
)

DECLARE @xdata2 TABLE
(
[Tijd] datetime,
Verbruik int
)

INSERT INTO @data
select
isnull(tabel1.thetime,tabel2.thetime) as Tijd,
tabel1.opbrengst FROM
(
select cast(s2.thetime as datetime) as thetime, s1.opbrengst
from LabJackLog...Logging#csv as s1
join LabJackLog...Logging#csv as s2
on round(s2.thetime*24*60*60,1) = round(s1.thetime*24*60*60,1)-0.2 and
s1.opbrengst = 1 and
s2.opbrengst = 0
) as tabel1

full outer join
(
select cast(s2.thetime as datetime) as thetime, s1.verbruik
from LabJackLog...Logging#csv as s1
join LabJackLog...Logging#csv as s2
on round(s2.thetime*24*60*60,1) = round(s1.thetime*24*60*60,1)-0.2 and
s1.verbruik = 1 and
s2.verbruik = 0
) as tabel2

on tabel1.thetime = tabel2.thetime
;WITH data ([Tijd], Opbrengst, row_no)
AS
(
SELECT [Tijd], Opbrengst,
row_no = row_number() OVER (ORDER BY [Tijd])
FROM @data where Opbrengst is not null
)

INSERT into @xdata
SELECT tabel5.Tijd,
tabel5.Opbrengst
FROM
(
SELECT d3.[Tijd],
Opbrengst = CASE WHEN d3.Opbrengst IS NOT NULL
THEN DATEDIFF(second, ISNULL(d4.[Tijd], d3.[Tijd]), d3.[Tijd])
ELSE NULL
END
FROM data d3
left JOIN data d4 ON d3.row_no = d4.row_no + 1
AND d3.Opbrengst IS NOT NULL
WHERE d3.row_no > d4.row_no
OR d4.row_no IS NULL
) as tabel5

INSERT INTO @data2
select
isnull(tabel1.thetime,tabel2.thetime) as Tijd,
tabel2.Verbruik FROM
(
select cast(s2.thetime as datetime) as thetime, s1.opbrengst
from LabJackLog...Logging#csv as s1
join LabJackLog...Logging#csv as s2
on round(s2.thetime*24*60*60,1) = round(s1.thetime*24*60*60,1)-0.2 and
s1.opbrengst = 1 and
s2.opbrengst = 0
) as tabel1

full outer join
(
select cast(s2.thetime as datetime) as thetime, s1.verbruik
from LabJackLog...Logging#csv as s1
join LabJackLog...Logging#csv as s2
on round(s2.thetime*24*60*60,1) = round(s1.thetime*24*60*60,1)-0.2 and
s1.verbruik = 1 and
s2.verbruik = 0
) as tabel2

on tabel1.thetime = tabel2.thetime
;WITH data ([Tijd], Verbruik, row_no)
AS
(
SELECT [Tijd], Verbruik,
row_no = row_number() OVER (ORDER BY [Tijd])
FROM @data2 where Verbruik is not null
)

INSERT into @xdata2
SELECT tabel5.Tijd,
tabel5.Verbruik
FROM
(
SELECT d3.[Tijd],
Verbruik = CASE WHEN d3.Verbruik IS NOT NULL
THEN DATEDIFF(second, ISNULL(d4.[Tijd], d3.[Tijd]), d3.[Tijd])
ELSE NULL
END
FROM data d3
left JOIN data d4 ON d3.row_no = d4.row_no + 1
AND d3.Verbruik IS NOT NULL
WHERE d3.row_no > d4.row_no
OR d4.row_no IS NULL
) as tabel5

select
isnull(xdata.Tijd, xdata2.Tijd) as Tijd,
xdata.Opbrengst,
xdata2.Verbruik
FROM
(select * FROM @xdata) as xdata
full outer join
(select * FROM @xdata2) as xdata2
on xdata.Tijd = xdata2.Tijd
order by Tijd

END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources