Help With Query

Question

Help With Query

phood

Old Hand

Points: 318
More actions
March 3, 2011 at 10:03 am

#388965

I need help with a query. For some reason I just can't get my hands around how to do this.
I have a table with 4 colums: meter_data_dmd_time, meter_data1, station, meter_data_meter_id
I have 20 stations and each station has a meter and that meter takes a sample every 15 minutes.
I need to sum all the meter readings for each HOUR between two time periods and take the average.
If I execute the query below I will get summed readings for each 15 minute interval. I need them for each hour so I need to sum the 15 minute intervals and divide by 4 for the average.
Here is the query:
select meter_data_dmd_time, sum(meter_data1) from meter_data_table
where meter_data_dmd_time between '2011-02-01' and '2011-03-01'
group by meter_data_dmd_time
I get results like this
2011-02-01 00:00:00.00094879.8065838623
2011-02-01 00:15:00.00094641.639319458
2011-02-01 00:30:00.00093853.449708252
2011-02-01 00:45:00.00093683.9327050781
2011-02-01 01:00:00.00093699.4391833496
2011-02-01 01:15:00.00093065.4496533203
2011-02-01 01:30:00.00092885.5480053711
2011-02-01 01:45:00.00092696.2549700928
2011-02-01 02:00:00.00092791.7338574219
I need all the 00 hour readings summed and averaged (divide by 4) and so on for each hour of each day in the between clause.
Any help would be greatly appreciated as I just am having a heck of time with this. I am a novice sql person but I'm sure I've done this before. ugggghhh

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Please provide table definition and ready to use sample data as described in the first link in my signature. Also, please provide your expected result based on those samples together with what you've tried so far.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

phood Old Hand Points: 318 More actions · Answer 2

Based on your forum etiquette I did a hack job on this post so I deleted what was in here and will post again in a bit when I get it right. Hopefully you never saw what was there! 😀

Paul

LutzM SSC Guru Points: 107049 More actions · Answer 3

phood (3/3/2011)
Based on your forum etiquette I did a hack job on this post so I deleted what was in here and will post again in a bit when I get it right. Hopefully you never saw what was there! 😀
Paul

You'll never know :-D;-)

(But it doesn't matter either...)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

phood Old Hand Points: 318 More actions · Answer 4

Absolutely right, I will never know for sure! Okay, I am pasting what I HOPE is correct below based on your well written (by the way) etiquette page. The data I have for the insert is for ONE HOUR so the output I need would be:

Feb 1 2011 00:00AM 94264.7070791626

It would continue hour by hour summing and averaging each hour's data. Here is what you asked for and again, I HOPE I got it right. I used the CREATE TO new query window on my table so hopefully that was okay.

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#paulstable','U') IS NOT NULL

DROP TABLE #paulstable

--===== Create the test table with

CREATE TABLE #paulstable(

[Meter_Data_DMD_Time] [datetime] NOT NULL,

[Meter_Data1] [float] NULL,

[Station] [varchar](40) NOT NULL,

[Meter_Data_Meter_ID] [varchar](10) NULL,

PRIMARY KEY CLUSTERED

(

[Meter_Data_DMD_Time] ASC,

[Station] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

--===== All Inserts into the IDENTITY column

SET IDENTITY_INSERT #paulstable ON

--===== Insert the test data into the test table

INSERT INTO #paulstable

(meter_data_dmd_time, meter_data1, station, meter_data_meter_id)

select 'Feb 1 2011 12:00AM','0','AIRP GATEWAY','0' union all

select 'Feb 1 2011 12:00AM','0','AIRP VMEA CR','0' union all

select 'Feb 1 2011 12:00AM','0','AIRP WTP','0' union all

select 'Feb 1 2011 12:00AM','18657.7','Airport Sub','0' union all

select 'Feb 1 2011 12:00AM','-12.9022','BATT CHST 125','0' union all

select 'Feb 1 2011 12:00AM','-11.5837','BATT CHST 346','0' union all

select 'Feb 1 2011 12:00AM','9356.27','Battery Heights','0' union all

select 'Feb 1 2011 12:00AM','0','LOM GT DSL','0' union all

select 'Feb 1 2011 12:00AM','2298.79','Lomar Sub 600','0' union all

select 'Feb 1 2011 12:00AM','3705.3','Lomar Sub 601','0' union all

select 'Feb 1 2011 12:00AM','0','MIC GT GAST','0' union all

select 'Feb 1 2011 12:00AM','10029.7','Micron Sub 500','0' union all

select 'Feb 1 2011 12:00AM','9850.92','Micron Sub 501','0' union all

select 'Feb 1 2011 12:00AM','8336.21','Micron Sub 502','0' union all

select 'Feb 1 2011 12:00AM','11555.9','Micron Sub 503','0' union all

select 'Feb 1 2011 12:00AM','12624','Point of Woods','0' union all

select 'Feb 1 2011 12:00AM','0','PRINCE DEAN1','0' union all

select 'Feb 1 2011 12:00AM','0','PRINCE DEAN2','0' union all

select 'Feb 1 2011 12:00AM','8489.51','Prince William S','0' union all

select 'Feb 1 2011 12:15AM','0','AIRP GATEWAY','0' union all

select 'Feb 1 2011 12:15AM','0','AIRP VMEA CR','0' union all

select 'Feb 1 2011 12:15AM','0','AIRP WTP','0' union all

select 'Feb 1 2011 12:15AM','18674.2','Airport Sub','0' union all

select 'Feb 1 2011 12:15AM','-12.9433','BATT CHST 125','0' union all

select 'Feb 1 2011 12:15AM','-11.6278','BATT CHST 346','0' union all

select 'Feb 1 2011 12:15AM','9403.09','Battery Heights','0' union all

select 'Feb 1 2011 12:15AM','0','LOM GT DSL','0' union all

select 'Feb 1 2011 12:15AM','2305.33','Lomar Sub 600','0' union all

select 'Feb 1 2011 12:15AM','3691.49','Lomar Sub 601','0' union all

select 'Feb 1 2011 12:15AM','0','MIC GT GAST','0' union all

select 'Feb 1 2011 12:15AM','10102.3','Micron Sub 500','0' union all

select 'Feb 1 2011 12:15AM','9830.12','Micron Sub 501','0' union all

select 'Feb 1 2011 12:15AM','8104.26','Micron Sub 502','0' union all

select 'Feb 1 2011 12:15AM','11654.1','Micron Sub 503','0' union all

select 'Feb 1 2011 12:15AM','12486.7','Point of Woods','0' union all

select 'Feb 1 2011 12:15AM','0','PRINCE DEAN1','0' union all

select 'Feb 1 2011 12:15AM','0','PRINCE DEAN2','0' union all

select 'Feb 1 2011 12:15AM','8414.64','Prince William S','0' union all

select 'Feb 1 2011 12:30AM','0','AIRP GATEWAY','0' union all

select 'Feb 1 2011 12:30AM','0','AIRP VMEA CR','0' union all

select 'Feb 1 2011 12:30AM','0','AIRP WTP','0' union all

select 'Feb 1 2011 12:30AM','18329.8','Airport Sub','0' union all

select 'Feb 1 2011 12:30AM','-13.286','BATT CHST 125','0' union all

select 'Feb 1 2011 12:30AM','-11.8725','BATT CHST 346','0' union all

select 'Feb 1 2011 12:30AM','9217.25','Battery Heights','0' union all

select 'Feb 1 2011 12:30AM','0','LOM GT DSL','0' union all

select 'Feb 1 2011 12:30AM','2326.16','Lomar Sub 600','0' union all

select 'Feb 1 2011 12:30AM','3693.28','Lomar Sub 601','0' union all

select 'Feb 1 2011 12:30AM','0','MIC GT GAST','0' union all

select 'Feb 1 2011 12:30AM','10197.9','Micron Sub 500','0' union all

select 'Feb 1 2011 12:30AM','9812.85','Micron Sub 501','0' union all

select 'Feb 1 2011 12:30AM','8088.44','Micron Sub 502','0' union all

select 'Feb 1 2011 12:30AM','11706.1','Micron Sub 503','0' union all

select 'Feb 1 2011 12:30AM','12150.1','Point of Woods','0' union all

select 'Feb 1 2011 12:30AM','0','PRINCE DEAN1','0' union all

select 'Feb 1 2011 12:30AM','0','PRINCE DEAN2','0' union all

select 'Feb 1 2011 12:30AM','8356.71','Prince William S','0' union all

select 'Feb 1 2011 12:45AM','0','AIRP GATEWAY','0' union all

select 'Feb 1 2011 12:45AM','0','AIRP VMEA CR','0' union all

select 'Feb 1 2011 12:45AM','0','AIRP WTP','0' union all

select 'Feb 1 2011 12:45AM','18342.8','Airport Sub','0' union all

select 'Feb 1 2011 12:45AM','-13.3583','BATT CHST 125','0' union all

select 'Feb 1 2011 12:45AM','-11.8578','BATT CHST 346','0' union all

select 'Feb 1 2011 12:45AM','9049.57','Battery Heights','0' union all

select 'Feb 1 2011 12:45AM','0','LOM GT DSL','0' union all

select 'Feb 1 2011 12:45AM','2331.21','Lomar Sub 600','0' union all

select 'Feb 1 2011 12:45AM','3664.14','Lomar Sub 601','0' union all

select 'Feb 1 2011 12:45AM','0','MIC GT GAST','0' union all

select 'Feb 1 2011 12:45AM','10168.5','Micron Sub 500','0' union all

select 'Feb 1 2011 12:45AM','9856.63','Micron Sub 501','0' union all

select 'Feb 1 2011 12:45AM','8114.43','Micron Sub 502','0' union all

select 'Feb 1 2011 12:45AM','11670.9','Micron Sub 503','0' union all

select 'Feb 1 2011 12:45AM','12158','Point of Woods','0' union all

select 'Feb 1 2011 12:45AM','0','PRINCE DEAN1','0' union all

select 'Feb 1 2011 12:45AM','0','PRINCE DEAN2','0' union all

select 'Feb 1 2011 12:45AM','8352.94','Prince William S','0'

--===== Set the identity insert back to normal

SET IDENTITY_INSERT #paulstable OFF

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 5

Just an FYI, always do a run on your code first before posting it, if able. You didn't have an Identity column in the temp table, so SET failed. No biggie though. It would also help to have two hours to work with, but again, not a big deal.

Alright, you're obviously familiar with the easy way to do this:

SELECT AVG( meter_data1) FROM #paulstable

Now, that'll just average all the data, and not sum by hour and THEN average.

So, you were on the right track, but the trick will be to getting your #'s figured out.

This code should do it. However, I didn't get the same total you did, but I'll leave that to you to confirm. The CTE gets the sum by hour. The second call to the CTE figures out your average across the timespan.

You can see what the cte returns by simply uncommenting the select * from cte and not running the last statement.

DECLARE @startDT DATETIME,

@endDT DATETIME

SELECT @startDT = 'Feb 1 2011 12:00AM',

@endDT = 'Feb 1 2011 2:00PM'

;with cte AS

(

SELECT

DATEDIFF( hh, @startDT, meter_data_dmd_time) AS hourGroup,

SUM( meter_data1) AS TotalMeterData

FROM

#paulstable

WHERE

meter_data_dmd_time >= @startDT and meter_data_dmd_time <= @endDT

GROUP BY

DATEDIFF( hh, @startDT, meter_data_dmd_time)

)

-- SELECT * FROM cte

SELECT

@startDT as BeginningTime,

@endDT AS EndingTime,

AVG( TotalMeterData) AS DataAverage

FROM

cte

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 6

This:

DECLARE @startDT DATETIME,

@endDT DATETIME

SELECT @startDT = 'Feb 1 2011 12:00AM',

@endDT = 'Feb 1 2011 2:00PM'

SELECT CAST( Meter_Data_DMD_Time AS DATE) Date_Part

,DATEPART(hh ,Meter_Data_DMD_Time) Hour_Part

,AVG(Meter_Data1) AVG_Reading

FROM #paulstable

WHERE meter_data_dmd_time >= @startDT and meter_data_dmd_time <= @endDT

GROUP BY CAST( Meter_Data_DMD_Time AS DATE), DATEPART(hh ,Meter_Data_DMD_Time)

phood Old Hand Points: 318 More actions · Answer 7

Let me provide you with more data. If I run your statement against all the data as you left it, I only get one result for multiple hours. Each hour should have it's own set of data. Also, if I run your statements for just start time of 12am to 12:59am, the result, I believe, is the average of all the data, but still must be divided by 4 to get the actual average. If I divide your result by 4 I get exactly what I need. It is all the data summed up and divided by 4 for each hour. The next post, don't think it was yours, is on the right track with providing hour by hour data, but the result is incorrect. Before I give you more data let's see if I can explain it better with a little less data because there's a lot of data to work with.

Let's say I only have two stations and I will use feb 1 2011 as the day and 12am to 2am as the time between data sets. I truly appreciate your help. Thanks for bearing with me and my novice abilities.

1200am station1 = 10, station2 = 20

1215am station1 = 11, station2 = 18

1230am station1 = 19, station2 = 11

1245am station1 = 15, station2 = 15

100am station1 = 20, station2 = 16

115am station1 = 14, station2 = 22

130am station1 = 10, station2 = 17

145am station1 = 12, station2 = 45

So the result should be for the hour of 12am ((10 + 20) + (11+18) + (19 + 11) + (15 + 15))/4 or 29.75

The result for the hour of 1am ((20 + 16) + (14 + 22) + (10 + 17) + (12 + 45))/4 or 39

A result query would look like

2011-02-01 00:00 29.75

2011-02-01 01:00 39

and so on for each hour of each day. Sometimes I'm not so good with explaining myself, sorry.

Here is more data and I didn't provide you with it the first time because I just thought it was going to be A LOT of data.