Filter, group and totalize data...

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

Filter, group and totalize data...

Author

Topic

dchristopherson
Starting Member

10 Posts

Posted - 2011-10-24 : 17:29:16

OK, I'm comfortable with basic/simple SQL syntax and operations, but don't have much experience with more advanced functions or nesting, etc.

That said, I have the following question. I have a large data set (example below) that includes data from two natural gas generators, each with their own exhaust heat recovery systems. Each generator has its own set of instrumentation and data, while the heat recovery units share their instrumentation. So, if one record shows a heat recovery rate of one million BTUs per hour, I need to compare that to some data point on the generators in order to determine which generator is running and credit the amount of heat recovered to that generator. Typically, I look at the flow rates of the generator cooling pumps...i.e. if pump one is running, generator 1 is running. The generators will not run simultaneously.

SAMPLE DATA:
timestamp point_id _VAL
2009-01-01 00:00:35.000 H12_GEN_TEMP_LOOP_BTU -0.689089442083547
2009-01-01 00:00:35.000 H130110FT -9.81209831820706E-05
2009-01-01 00:00:35.000 H130210FT 3.2485090516607
2009-01-01 00:01:35.000 H12_GEN_TEMP_LOOP_BTU -0.758669696928981
2009-01-01 00:01:35.000 H130110FT -9.81209831820706E-05
2009-01-01 00:01:35.000 H130210FT 3.2485090516607
2009-01-01 00:02:35.000 H12_GEN_TEMP_LOOP_BTU -0.617989228203714
2009-01-01 00:02:35.000 H130110FT -9.81209831820706E-05
2009-01-01 00:02:35.000 H130210FT 3.2485090516607

SQL used to grab this data:


SELECT TOP 9 [timestamp]
       ,[point_id]
       ,[_VAL]
  FROM [WRP_2009].[dbo].[LAP_H_AI]
  WHERE point_id = 'H130110FT'
  OR point_id = 'H130210FT'
  OR point_id = 'H12_GEN_TEMP_LOOP_BTU'

Two things to note: First is that the time stamps for each of the three datapoints will be identical, if it helps. Second is that based on the numbers above, I will need some way to work around the "drift" in the instrumentation. For example, any data for flows (H130110FT and H130210FT) less than 10 GPM is sensor error and should be regarded as zero. In the sample data above, that would mean that neither generator is running and any BTU recovery data is erroneous.

My end goal is to split the heat recovery values into three sets:
1) Heat from Generator 1
2) Heat from Generator 2
3) Erroneous Data.

Sets one and two would each then be summed, and divided by 60. This would convert from an heat transfer rate (btus per hour, logged each minute) to a total amount of heat transferred (btus).

Thanks in advance...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-25 : 00:00:00

so what should be your output from above data? can you show that also?

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

dchristopherson
Starting Member

10 Posts

Posted - 2011-10-25 : 08:36:45

visakh16:

Of course. As I mentioned previously, the above data is erroneous and would be disregarded. My anticipated output would be two separate tables, both containing data only for [point_id] = H12_GEN_TEMP_LOOP_BTU. The data in each output table would be a subset of the data in the main table, with no overlap. Let me show the following hypothetical data set (and results) as an example.

timestamp point_id _VAL
2009-01-01 10:58:00.000 H12_GEN_TEMP_LOOP_BTU 1.55
2009-01-01 10:58:00.000 H130110FT 0.00
2009-01-01 10:58:00.000 H130210FT 210
2009-01-01 10:59:00.000 H12_GEN_TEMP_LOOP_BTU 1.25
2009-01-01 10:59:00.000 H130110FT 0.00
2009-01-01 10:59:00.000 H130210FT 205
2009-01-01 11:00:00.000 H12_GEN_TEMP_LOOP_BTU 0.75
2009-01-01 11:00:00.000 H130110FT 200
2009-01-01 11:00:00.000 H130210FT 0.00
2009-01-01 11:01:00.000 H12_GEN_TEMP_LOOP_BTU 1.05
2009-01-01 11:01:00.000 H130110FT 203
2009-01-01 11:01:00.000 H130210FT 0.00

OK, for the above data, I can see that heat was recovered for the period between 10:58 and 11:01. I fan tell from the pump flow rates that for the 10:58 and 10:59 data points that Generator #2 was running (H130210FT is the flow transmitter for Gen2's cooling water pump). Between 10:59 and 11:00, the plant switched over to generator #1. My resulting tables would look like this:

[tableGEN1]
timestamp point_id _VAL
2009-01-01 11:00:00.000 H12_GEN_TEMP_LOOP_BTU 0.75
2009-01-01 11:01:00.000 H12_GEN_TEMP_LOOP_BTU 1.05

[tableGEN2]
timestamp point_id _VAL
2009-01-01 10:58:00.000 H12_GEN_TEMP_LOOP_BTU 1.55
2009-01-01 10:59:00.000 H12_GEN_TEMP_LOOP_BTU 1.25

Note also that this is a static data set on an "offline" database. I can do this in a few steps, if necessary. I think it may also be easier to remove the erroneous data as the first step. For example, LOOP_BTU values less than 0.20 are essentially meaningless and could be replaced with a 0.00. This might simplify things because they could end up in either table and not skew the results (a sum of all _VAL values). In this case, All LOOP_BTU data could just be sorted into a new table based on the larger flow rate value with the same timestamp.

Thanks for taking the time to help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-26 : 00:50:17

[code]
insert into tableGEN1(timestamp, point_id, _VAL)
select timestamp,'H12_GEN_TEMP_LOOP_BTU' , sum(case when [point_id]='H12_GEN_TEMP_LOOP_BTU' THEN [_Val] ELSE 0 END)
from yourtable
group by timestamp
having sum(case when [point_id]='H130110FT' THEN [_Val] ELSE 0 END) >0.00
and
sum(case when [point_id]='H12_GEN_TEMP_LOOP_BTU' THEN [_Val] ELSE 0 END)>0.20

insert into tableGEN2(timestamp, point_id, _VAL)
select timestamp,'H12_GEN_TEMP_LOOP_BTU' , sum(case when [point_id]='H12_GEN_TEMP_LOOP_BTU' THEN [_Val] ELSE 0 END)
from yourtable
group by timestamp
having sum(case when [point_id]='H130210FT' THEN [_Val] ELSE 0 END) >0.00
and
sum(case when [point_id]='H12_GEN_TEMP_LOOP_BTU' THEN [_Val] ELSE 0 END)>0.20
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources