[SOLVED] Grouping sets of data for manipulation

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

General SQL Server Forums

New to SQL Server Programming

[SOLVED] Grouping sets of data for manipulation

Author

Topic

dchristopherson
Starting Member

10 Posts

Posted - 2010-12-20 : 10:25:42

I'd like some help regarding writing the following query:

I have a data table that looks like the example below [Table1]. Every minute, 20 rows are added that contain measured values from several analog sensors. At a regular interval a query is run that moves data from this table into another. Most of the time this table contains ONLY 20 lines of data, all taken from a single time. I'll call this a set. However, the table may contain more than one set of data, with timestamps changing by approximately 1 minute. My goal is to SELECT 3 rows from a given set and write them to a new table (Table2 example below, with desired output shown below Table1). Please note that in the given set(s) of data the Date_Time values are identical. In the actual datbase they are NOT. They can be assumed to be within ~5 seconds, though.


Date_Time	TagName	                                                        Value	                EU
2010-12-14 20:05:19.000	BacNet1.FCB.AnalogInput_3000812_OA-T.PresentValue	14.503324508667	        NULL
2010-12-14 20:05:19.000	BacNet1.FCB.AnalogInput_3000873_PH-T.PresentValue	76.4392013549805	NULL
2010-12-14 20:05:19.000	BacNet1.FCB.AnalogInput_3000881_HWCE-T.PresentValue	116.347183227539	NULL
2010-12-14 20:05:19.000	BacNet1.FCB.AnalogInput_3000882_HWCL-T.PresentValue	114.867370605469	NULL
2010-12-14 20:05:19.000	BacNet1.FCB.AnalogInput_3000887_DA-T.PresentValue	101.547485351563	NULL
2010-12-14 20:05:19.000	BacNet1.FCB.AnalogInput_3000895_EAHR-T.PresentValue	71.9544372558594	NULL
2010-12-14 20:05:19.000	BacNet1.FCB.AnalogInput_3000902_MA-T.PresentValue	79.6642150878906	NULL
2010-12-14 20:05:19.000	BacNet1.FCB.AnalogInput_3000906_RA-T.PresentValue	79.0135269165039	NULL
2010-12-14 20:05:20.000	BacNet1.FCB.AnalogInput_3001007_GLYR-T.PresentValue	37.868766784668	        NULL
2010-12-14 20:05:20.000	BacNet1.FCB.AnalogInput_3001008_GLYS-T.PresentValue	37.4429359436035	NULL
2010-12-14 20:05:20.000	BacNet1.FCB.AnalogInput_3001009_INFLOOR-T.PresentValue	103.629348754883	NULL
2010-12-14 20:05:20.000	BacNet1.FCB.AnalogInput_3001010_SOLAR_PNL-T.PresentValue	150.736358642578	NULL
2010-12-14 20:05:20.000	BacNet1.FCB.AnalogInput_3001011_SOLAR_STRG-T.PresentValue	124.501129150391	NULL
2010-12-14 20:05:20.000	BacNet1.FCB.AnalogInput_3001100_OA-H.PresentValue	67.1446914672852	NULL
2010-12-14 20:05:20.000	BacNet1.FCB.AnalogInput_3001290_GEO-FLOW.PresentValue	46.0946998596191	NULL
2010-12-14 20:05:20.000	BacNet1.FCB.AnalogOutput_3000884_HC-O.PresentValue	0	                NULL
2010-12-14 20:05:20.000	BacNet1.FCB.AnalogOutput_3000891_PH-O.PresentValue	0	                NULL
2010-12-14 20:05:20.000	BacNet1.FCB.AnalogOutput_3000893_RAD-O.PresentValue	100	                NULL
2010-12-14 20:05:20.000	BacNet1.FCB.AnalogOutput_3000974_GWP2-O.PresentValue	100	                NULL
2010-12-14 20:05:20.000	BacNet1.FCB.AnalogOutput_3001006_GWP1-O.PresentValue	0	                NULL
2010-12-14 20:10:51.000	BacNet1.FCB.AnalogInput_3000812_OA-T.PresentValue	14.4943180084229	NULL
2010-12-14 20:10:51.000	BacNet1.FCB.AnalogInput_3000873_PH-T.PresentValue	45.6761817932129	NULL
2010-12-14 20:10:51.000	BacNet1.FCB.AnalogInput_3000881_HWCE-T.PresentValue	114.49104309082	        NULL
2010-12-14 20:10:51.000	BacNet1.FCB.AnalogInput_3000882_HWCL-T.PresentValue	91.543098449707	        NULL
2010-12-14 20:10:51.000	BacNet1.FCB.AnalogInput_3000887_DA-T.PresentValue	101.505241394043	NULL
2010-12-14 20:10:51.000	BacNet1.FCB.AnalogInput_3000895_EAHR-T.PresentValue	73.7096862792969	NULL
2010-12-14 20:10:51.000	BacNet1.FCB.AnalogInput_3000902_MA-T.PresentValue	55.599739074707	        NULL
2010-12-14 20:10:51.000	BacNet1.FCB.AnalogInput_3000906_RA-T.PresentValue	77.1669006347656	NULL
2010-12-14 20:10:51.000	BacNet1.FCB.AnalogInput_3001007_GLYR-T.PresentValue	37.5416069030762	NULL
2010-12-14 20:10:51.000	BacNet1.FCB.AnalogInput_3001008_GLYS-T.PresentValue	37.4456596374512	NULL
2010-12-14 20:10:51.000	BacNet1.FCB.AnalogInput_3001009_INFLOOR-T.PresentValue	109.100593566895	NULL
2010-12-14 20:10:51.000	BacNet1.FCB.AnalogInput_3001010_SOLAR_PNL-T.PresentValue	133.765502929688	NULL
2010-12-14 20:10:52.000	BacNet1.FCB.AnalogInput_3001011_SOLAR_STRG-T.PresentValue	125.259948730469	NULL
2010-12-14 20:10:52.000	BacNet1.FCB.AnalogInput_3001100_OA-H.PresentValue	67.1344833374023	NULL
2010-12-14 20:10:52.000	BacNet1.FCB.AnalogInput_3001290_GEO-FLOW.PresentValue	46.0804634094238	NULL
2010-12-14 20:10:52.000	BacNet1.FCB.AnalogOutput_3000884_HC-O.PresentValue	0	                NULL
2010-12-14 20:10:52.000	BacNet1.FCB.AnalogOutput_3000891_PH-O.PresentValue	0.357897788286209	NULL
2010-12-14 20:10:52.000	BacNet1.FCB.AnalogOutput_3000893_RAD-O.PresentValue	100	                NULL
2010-12-14 20:10:52.000	BacNet1.FCB.AnalogOutput_3000974_GWP2-O.PresentValue	100	                NULL

The output below is the VALUE of three sensors from above. Specifically, GLYR-T.PresentValue, GLYS-T.PresentValue and GEO-FLOW.PresentValue. The Date_Time should be taken from the timestamp of GEO-FLOW.PresentValue.


Date_Time	        TagName1Value           TagName2Value           TagName3Value
2010-12-14 20:05:20.000	37.868766784668	        37.4429359436035	46.0946998596191
2010-12-14 20:10:52.000 37.5416069030762	37.4456596374512        46.0804634094238

Thanks.

SparkByte
Yak Posting Veteran

60 Posts

Posted - 2010-12-20 : 11:25:12

Is this a string value 'BacNet1.FCB.AnalogInput_3001007_GLYR-T.PresentValue' for GLYR-T.PresentValue?

If it is, do you already have a script/(SP) that parses this string into the components you need?

Thank You,

John

dchristopherson
Starting Member

10 Posts

Posted - 2010-12-20 : 12:04:25

quote:
Originally posted by SparkByte

Is this a string value 'BacNet1.FCB.AnalogInput_3001007_GLYR-T.PresentValue' for GLYR-T.PresentValue?

If it is, do you already have a script/(SP) that parses this string into the components you need?

Thank You,

John

I'm not sure that I completely understand your question, but I will try to explain. 'BacNet1.FCB.AnalogInput_3001007_GLYR-T.PresentValue' is a Value from the column TagName in Table1. The entire entry includes a date/time value, a TagName (to identify a physical sensor monitoring some variable), and a Value a value for the measured variable (and a NULL value for Engineering Units). In plain english, this can be read as "At [time] the sensor 'BacNet1.FCB.AnalogInput_3001007_GLYR-T.PresentValue' reported a value of 37.86..."

In Table2, I do not nead the actual TagName, just the reported value for that sensor.

SparkByte
Yak Posting Veteran

60 Posts

Posted - 2010-12-20 : 14:35:32

Well Not sure if this helps but,

Using your example data and a function to split the String data.

This gets the data your looking for, but it still needs to be pivoted to display it the way you asked for. But this is a little past my expertise at the moment. In SQL 2005 I am sure this could be done with the new PIVOT function.

Anyway here is what i was ale to cobble together. See if this is close.



Create Table #Example
(
	Date_Time DateTime,
	TagName VarChar(75),
	SensorValue decimal(18, 12),
	EU Varchar(5)
);

Insert Into #Example Values('2010-12-14 20:05:19.000', 'BacNet1.FCB.AnalogInput_3000812_OA-T.PresentValue', 14.503324508667,Null);
Insert Into #Example Values('2010-12-14 20:05:19.000','BacNet1.FCB.AnalogInput_3000812_OA-T.PresentValue',14.503324508667,Null);
Insert Into #Example Values('2010-12-14 20:05:19.000','BacNet1.FCB.AnalogInput_3000873_PH-T.PresentValue',76.4392013549805,Null);
Insert Into #Example Values('2010-12-14 20:05:19.000','BacNet1.FCB.AnalogInput_3000881_HWCE-T.PresentValue',116.347183227539,Null);
Insert Into #Example Values('2010-12-14 20:05:19.000','BacNet1.FCB.AnalogInput_3000882_HWCL-T.PresentValue',114.867370605469,Null);
Insert Into #Example Values('2010-12-14 20:05:19.000','BacNet1.FCB.AnalogInput_3000887_DA-T.PresentValue',101.547485351563,Null);
Insert Into #Example Values('2010-12-14 20:05:19.000','BacNet1.FCB.AnalogInput_3000895_EAHR-T.PresentValue',71.9544372558594,Null);
Insert Into #Example Values('2010-12-14 20:05:19.000','BacNet1.FCB.AnalogInput_3000902_MA-T.PresentValue',79.6642150878906,Null);
Insert Into #Example Values('2010-12-14 20:05:19.000','BacNet1.FCB.AnalogInput_3000906_RA-T.PresentValue',79.0135269165039,Null);
Insert Into #Example Values('2010-12-14 20:05:20.000','BacNet1.FCB.AnalogInput_3001007_GLYR-T.PresentValue',37.868766784668,Null);
Insert Into #Example Values('2010-12-14 20:05:20.000','BacNet1.FCB.AnalogInput_3001008_GLYS-T.PresentValue',37.4429359436035,Null);
Insert Into #Example Values('2010-12-14 20:05:20.000','BacNet1.FCB.AnalogInput_3001009_INFLOOR-T.PresentValue',103.629348754883,Null);

Update #Example
	Set  TagName = (Select Items From dbo.fn_Split(TagName,'_') Where Items In ('GLYR-T.PresentValue', 'GLYS-T.PresentValue', 'GLYS-T.PresentValue') )

--Select * From #Example

	Select 
		Distinct(Date_Time),
		TagName,
		SensorValue 
	From #Example 
	Where TagName is not null
	group by Date_Time, TagName, SensorValue


Drop Table #Example

And the function I used. (easily found vis Google.)

Create FUNCTION [dbo].[fn_Split](@String varchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (ID int, Items nvarchar(4000))
AS

BEGIN
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)
DECLARE @ID int

SELECT @INDEX = 1, @ID = 1
WHILE @INDEX !=0

BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX = CHARINDEX(@Delimiter,@String)
-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
IF @INDEX !=0
SELECT @SLICE = LEFT(@String,@INDEX - 1)
ELSE
SELECT @SLICE = @String
-- PUT THE ITEM INTO THE RESULTS SET
INSERT INTO @Results(ID, Items) VALUES(@ID, @SLICE)
SELECT @ID = @ID + 1
-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SELECT @String = RIGHT(@String,LEN(@String) - @INDEX)
-- BREAK OUT IF WE ARE DONE
IF LEN(@String) = 0 BREAK
END
Return
END

Thank You,

John

dchristopherson
Starting Member

10 Posts

Posted - 2010-12-21 : 12:48:30

Thanks for your help, John. I solved this issue by creating a separate table that stored the data in a different format. See example:

Date_Time     Calculated_TagName      Calculated_Value     TagName1_Value     TagName2_Value     TagName3_Value
[timestamp]   [predefined string]     NULL                 36.4500251         34.586248          43.8469

and the query looked like this:

UPDATE Table1
SET Calculated_Value=([Tagname1_Value] - [TagName2_Value]) * [TagName3_Value]

The result was just what I needed with very little SQL manipulation.

SparkByte
Yak Posting Veteran

60 Posts

Posted - 2010-12-21 : 13:38:35

How did you mark the topic with [SOLVED]??

Thank You,

John

dchristopherson
Starting Member

10 Posts

Posted - 2010-12-23 : 08:16:25

I edited the subject line of my original post. I use some forums that have the [OPEN],[CLOSED],[SOLVED], etc. prefixes available and it's a really nice feature....so I do it manually where I can.

-Dylan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources