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
 Multiplication of two specific rows of data

Author  Topic 

dchristopherson
Starting Member

10 Posts
Posted - 2010-12-16 : 10:38:01
Let me preface by stating that I am COMPLETE newbie and that only basic SELECTs and INSERTs are within my realm of expertice. That said, I am more than thankful for any help I can get specific to this issue or even advice on where to look for relevant examples/tutorials.

I need to perform some mathematical operations on data within a table and insert the result as a new record. For example:

NewTagName = (OldTag1 - OldTag2) * OldTag3 * 500.4 * @CorrectionFactor

Under ideal conditions, the table contains one set of data, meaning that every TagName only occurs once and all datetime values are identical. Under real conditions I can't be certain of this. The table may contain several minutes of data, meaning that the TagNames can't be considered unique identifiers. However, I can be certain that the timestamps will be within, say, 5 seconds of each other. So, I need a solution that first selects the OldTags to be used and then groups them based on the the value of the Date_Time column within 5 seconds.

My expected order of operation is as follows:

Define variable CorrectionFactor
SELECT All rows with OldTag1, OldTag2, OldTag3
Compare the datetime values and group the data into sets, each set containing a single instance of OldTag1, OldTag2, OldTag3
Perform the mathematical operation on each group
Insert the result into the table with the following column values:

Date_Time: equal to Date_Time from OldTag1
TagName: NewTagName
Value: as calculated from equation above
EU: NULL

The data table in use looks like the example below. Every minute, a new set of data is added for 20 collected data points. The sample here contains 2 minutes of data.

Thanks for helping,

-Dylan


Date_Time	TagName	                                                        Value	                EU
2010-12-14 19:59:47.000	BacNet1.FCB.AnalogOutput_3001006_GWP1-O.PresentValue	0	                NULL
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
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site