Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Avg minute data per hour beetween a time frame 1

Status
Not open for further replies.
NerdTop72

NerdTop72

Programmer
Mar 14, 2005
117
US
I have data logged that I need to average per hour between a date range and I am clueless where to start. I am using Microsoft SQL Server 2000 to access old data...

table name = ftb_analogs
fields:
DateandTime as datetime
tag as smallint
val as float

I am trying to grab data between July 1, 2009 and October 1, 2009 and average the information in the val field per hour and then the average per day. the tag field would be always equal to 3.

So the output would be
AVG Per hour
date and time average per hour
july 1, 2009 01:00 | 35.29

AVG Per Day
date and time average per day
july 1, 2009 | 35.29

Thanks!
 
Sort by date Sort by votes
Code: 
-- AVG by HOUR
SELECT  DATEADD(dd, 0, DATEDIFF(dd, 0, DateandTime )) AS DateandTime,
        DATEPART(hh, DateandTime ) AS MyHour,
        AVG([Val]) AS [Val],
        Tag
FROM  ftb_analogs
WHERE DateandTime >= '20090701' 
  AND DateandTime <  '20091002'
  AND Tag = 3 -- I'm not sure about this condition
GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, DateandTime ))
        ,DATEPART(hh, DateandTime )

-- AVG by day
SELECT  DATEADD(dd, 0, DATEDIFF(dd, 0, DateandTime )) AS DateandTime,
        AVG([Val]) AS [Val],
        Tag
FROM  ftb_analogs
WHERE DateandTime >= '20090701' 
  AND DateandTime <  '20091002'
  AND Tag = 3 -- I'm not sure about this condition
GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, DateandTime ))

NOT TESTED!


Borislav Borissov
VFP9 SP2, SQL Server
 
Upvote 0 Downvote
Thanks for the response! I got some weird results
Code: 
SELECT  DATEADD(dd, 0, DATEDIFF(dd, 0, DateandTime )) AS DateandTime,
        DATEPART(hh, DateandTime ) AS MyHour,
        AVG([Val]) AS [Val]
FROM  ftb_analogs
WHERE DateandTime >= '20090701' 
  AND DateandTime <  '20091002'
  AND Tag = 3 -- I'm not sure about this condition
GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, DateandTime ))
        ,DATEPART(hh, DateandTime )

It seemed to have missed some data, below there was some information on 9-11-2009 at 17:00 and it is 0 average. there are a couple other dates as well missing...
Code: 
2009-09-02 00:00:00.000	0	0.0
2009-09-03 00:00:00.000	0	0.0
2009-09-04 00:00:00.000	0	0.0
2009-09-05 00:00:00.000	0	0.0
2009-09-10 00:00:00.000	0	0.0
2009-09-11 00:00:00.000	0	0.0
2009-09-12 00:00:00.000	0	0.0
2009-09-13 00:00:00.000	0	0.0
2009-09-14 00:00:00.000	0	0.0
2009-09-19 00:00:00.000	0	0.0
2009-09-20 00:00:00.000	0	0.0
2009-09-21 00:00:00.000	0	0.0
2009-09-22 00:00:00.000	0	0.0
2009-09-27 00:00:00.000	0	0.0
2009-09-28 00:00:00.000	0	0.0
2009-09-29 00:00:00.000	0	0.0
2009-09-30 00:00:00.000	0	0.0
2009-09-02 00:00:00.000	1	0.0
2009-09-03 00:00:00.000	1	0.0
2009-09-04 00:00:00.000	1	0.0
2009-09-05 00:00:00.000	1	0.0
2009-09-10 00:00:00.000	1	0.0
2009-09-11 00:00:00.000	1	0.0
2009-09-12 00:00:00.000	1	0.0
2009-09-13 00:00:00.000	1	0.0
2009-09-15 00:00:00.000	1	0.0
2009-09-18 00:00:00.000	1	0.0
2009-09-20 00:00:00.000	1	0.0
2009-09-21 00:00:00.000	1	0.0
2009-09-23 00:00:00.000	1	0.0
2009-09-26 00:00:00.000	1	0.0
2009-09-28 00:00:00.000	1	0.0
2009-09-29 00:00:00.000	1	0.0
2009-09-02 00:00:00.000	2	0.0
2009-09-03 00:00:00.000	2	0.0
2009-09-04 00:00:00.000	2	0.0
2009-09-05 00:00:00.000	2	0.0
2009-09-10 00:00:00.000	2	0.0
2009-09-11 00:00:00.000	2	0.0
2009-09-12 00:00:00.000	2	0.0
2009-09-13 00:00:00.000	2	0.0
2009-09-16 00:00:00.000	2	0.0
2009-09-18 00:00:00.000	2	0.0
2009-09-19 00:00:00.000	2	0.0
2009-09-21 00:00:00.000	2	0.0
2009-09-24 00:00:00.000	2	0.0
2009-09-26 00:00:00.000	2	0.0
2009-09-27 00:00:00.000	2	0.0
2009-09-29 00:00:00.000	2	0.0
2009-09-02 00:00:00.000	3	0.0
2009-09-03 00:00:00.000	3	0.0
2009-09-04 00:00:00.000	3	0.0
2009-09-05 00:00:00.000	3	0.0
2009-09-10 00:00:00.000	3	0.0
2009-09-11 00:00:00.000	3	0.0
2009-09-12 00:00:00.000	3	0.0
2009-09-13 00:00:00.000	3	0.0
2009-09-17 00:00:00.000	3	0.0
2009-09-18 00:00:00.000	3	0.0
2009-09-19 00:00:00.000	3	0.0
2009-09-20 00:00:00.000	3	0.0
2009-09-25 00:00:00.000	3	0.0
2009-09-26 00:00:00.000	3	0.0
2009-09-27 00:00:00.000	3	0.0
2009-09-28 00:00:00.000	3	0.0
2009-08-31 00:00:00.000	4	0.0
2009-09-01 00:00:00.000	4	0.0
2009-09-06 00:00:00.000	4	0.0
2009-09-07 00:00:00.000	4	0.0
2009-09-08 00:00:00.000	4	0.0
2009-09-09 00:00:00.000	4	0.0
2009-09-10 00:00:00.000	4	0.0
2009-09-14 00:00:00.000	4	0.0
2009-09-15 00:00:00.000	4	0.0
2009-09-16 00:00:00.000	4	0.0
2009-09-17 00:00:00.000	4	0.0
2009-09-18 00:00:00.000	4	0.0
2009-09-23 00:00:00.000	4	0.0
2009-09-24 00:00:00.000	4	0.0
2009-09-25 00:00:00.000	4	0.0
2009-09-26 00:00:00.000	4	0.0
2009-08-31 00:00:00.000	5	0.0
2009-09-01 00:00:00.000	5	0.0
2009-09-06 00:00:00.000	5	0.0
2009-09-07 00:00:00.000	5	0.0
2009-09-08 00:00:00.000	5	0.0
2009-09-09 00:00:00.000	5	0.0
2009-09-11 00:00:00.000	5	0.0
2009-09-14 00:00:00.000	5	0.0
2009-09-15 00:00:00.000	5	0.0
2009-09-16 00:00:00.000	5	0.0
2009-09-17 00:00:00.000	5	0.0
2009-09-19 00:00:00.000	5	0.0
2009-09-22 00:00:00.000	5	0.0
2009-09-24 00:00:00.000	5	0.0
2009-09-25 00:00:00.000	5	0.0
2009-09-27 00:00:00.000	5	0.0
2009-09-30 00:00:00.000	5	0.0
2009-08-31 00:00:00.000	6	0.0
2009-09-01 00:00:00.000	6	0.0
2009-09-06 00:00:00.000	6	0.0
2009-09-07 00:00:00.000	6	0.0
2009-09-08 00:00:00.000	6	0.0
2009-09-09 00:00:00.000	6	0.0
2009-09-12 00:00:00.000	6	0.0
2009-09-14 00:00:00.000	6	0.0
2009-09-15 00:00:00.000	6	0.0
2009-09-16 00:00:00.000	6	0.0
2009-09-17 00:00:00.000	6	0.0
2009-09-20 00:00:00.000	6	0.0
2009-09-22 00:00:00.000	6	0.0
2009-09-23 00:00:00.000	6	0.0
2009-09-25 00:00:00.000	6	0.0
2009-09-28 00:00:00.000	6	0.0
2009-09-30 00:00:00.000	6	0.0
2009-08-31 00:00:00.000	7	0.0
2009-09-01 00:00:00.000	7	0.0
2009-09-06 00:00:00.000	7	0.0
2009-09-07 00:00:00.000	7	0.0
2009-09-08 00:00:00.000	7	0.0
2009-09-09 00:00:00.000	7	5.5825895468393965
2009-09-13 00:00:00.000	7	0.0
2009-09-14 00:00:00.000	7	0.0
2009-09-15 00:00:00.000	7	0.0
2009-09-16 00:00:00.000	7	0.0
2009-09-17 00:00:00.000	7	0.0
2009-09-21 00:00:00.000	7	0.0
2009-09-22 00:00:00.000	7	0.0
2009-09-23 00:00:00.000	7	0.0
2009-09-24 00:00:00.000	7	0.0
2009-09-29 00:00:00.000	7	0.0
2009-09-30 00:00:00.000	7	0.0
2009-09-02 00:00:00.000	8	0.0
2009-09-03 00:00:00.000	8	0.0
2009-09-04 00:00:00.000	8	0.0
2009-09-05 00:00:00.000	8	0.0
2009-09-11 00:00:00.000	8	0.0
2009-09-12 00:00:00.000	8	0.0
2009-09-13 00:00:00.000	8	0.0
2009-09-14 00:00:00.000	8	0.0
2009-09-18 00:00:00.000	8	0.0
2009-09-19 00:00:00.000	8	0.0
2009-09-20 00:00:00.000	8	0.0
2009-09-21 00:00:00.000	8	0.0
2009-09-22 00:00:00.000	8	0.0
2009-09-28 00:00:00.000	8	6.5394817352294918
2009-09-29 00:00:00.000	8	0.0
2009-09-30 00:00:00.000	8	0.0
2009-09-02 00:00:00.000	9	0.0
2009-09-03 00:00:00.000	9	0.0
2009-09-04 00:00:00.000	9	0.0
2009-09-05 00:00:00.000	9	0.0
2009-09-10 00:00:00.000	9	0.0
2009-09-12 00:00:00.000	9	0.0
2009-09-13 00:00:00.000	9	0.0
2009-09-15 00:00:00.000	9	0.0
2009-09-18 00:00:00.000	9	0.0
2009-09-19 00:00:00.000	9	0.0
2009-09-20 00:00:00.000	9	0.0
2009-09-21 00:00:00.000	9	0.0
2009-09-23 00:00:00.000	9	0.0
2009-09-28 00:00:00.000	9	11.129911740620932
2009-09-29 00:00:00.000	9	0.0
2009-09-30 00:00:00.000	9	0.0
2009-09-02 00:00:00.000	10	0.0
2009-09-03 00:00:00.000	10	0.0
2009-09-04 00:00:00.000	10	0.0
2009-09-05 00:00:00.000	10	0.0
2009-09-10 00:00:00.000	10	0.0
2009-09-11 00:00:00.000	10	0.0
2009-09-13 00:00:00.000	10	0.0
2009-09-16 00:00:00.000	10	0.0
2009-09-18 00:00:00.000	10	0.0
2009-09-19 00:00:00.000	10	0.0
2009-09-20 00:00:00.000	10	0.0
2009-09-21 00:00:00.000	10	0.0
2009-09-24 00:00:00.000	10	0.0
2009-09-26 00:00:00.000	10	0.0
2009-09-27 00:00:00.000	10	0.0
2009-09-02 00:00:00.000	11	0.0
2009-09-03 00:00:00.000	11	0.0
2009-09-04 00:00:00.000	11	0.0
2009-09-05 00:00:00.000	11	0.0
2009-09-10 00:00:00.000	11	0.0
2009-09-11 00:00:00.000	11	0.0
2009-09-12 00:00:00.000	11	0.0
2009-09-17 00:00:00.000	11	0.0
2009-09-18 00:00:00.000	11	0.0
2009-09-19 00:00:00.000	11	0.0
2009-09-20 00:00:00.000	11	0.0
2009-09-21 00:00:00.000	11	0.0
2009-09-25 00:00:00.000	11	0.0
2009-09-26 00:00:00.000	11	0.0
2009-09-27 00:00:00.000	11	0.0
2009-08-31 00:00:00.000	12	0.0
2009-09-01 00:00:00.000	12	0.0
2009-09-06 00:00:00.000	12	0.0
2009-09-07 00:00:00.000	12	0.0
2009-09-08 00:00:00.000	12	0.0
2009-09-09 00:00:00.000	12	0.0
2009-09-10 00:00:00.000	12	0.0
2009-09-15 00:00:00.000	12	0.0
2009-09-16 00:00:00.000	12	0.0
2009-09-17 00:00:00.000	12	0.0
2009-09-18 00:00:00.000	12	0.0
2009-09-22 00:00:00.000	12	0.0
2009-09-23 00:00:00.000	12	10.650666634241739
2009-09-24 00:00:00.000	12	0.0
2009-09-25 00:00:00.000	12	0.0
2009-09-26 00:00:00.000	12	0.0
2009-08-31 00:00:00.000	0	0.0
2009-09-01 00:00:00.000	0	0.0
2009-09-06 00:00:00.000	0	0.0
2009-09-07 00:00:00.000	0	0.0
2009-09-08 00:00:00.000	0	0.0
2009-09-09 00:00:00.000	0	0.0
2009-09-15 00:00:00.000	0	0.0
2009-09-16 00:00:00.000	0	0.0
2009-09-17 00:00:00.000	0	0.0
2009-09-18 00:00:00.000	0	0.0
2009-09-23 00:00:00.000	0	0.0
2009-09-24 00:00:00.000	0	0.0
2009-09-25 00:00:00.000	0	0.0
2009-09-26 00:00:00.000	0	0.0
2009-08-31 00:00:00.000	1	0.0
2009-09-01 00:00:00.000	1	0.0
2009-09-06 00:00:00.000	1	0.0
2009-09-07 00:00:00.000	1	0.0
2009-09-08 00:00:00.000	1	0.0
2009-09-09 00:00:00.000	1	0.0
2009-09-14 00:00:00.000	1	0.0
2009-09-16 00:00:00.000	1	0.0
2009-09-17 00:00:00.000	1	0.0
2009-09-19 00:00:00.000	1	0.0
2009-09-22 00:00:00.000	1	0.0
2009-09-24 00:00:00.000	1	0.0
2009-09-25 00:00:00.000	1	0.0
2009-09-27 00:00:00.000	1	0.0
2009-09-30 00:00:00.000	1	0.0
2009-08-31 00:00:00.000	2	0.0
2009-09-01 00:00:00.000	2	0.0
2009-09-06 00:00:00.000	2	0.0
2009-09-07 00:00:00.000	2	0.0
2009-09-08 00:00:00.000	2	0.0
2009-09-09 00:00:00.000	2	0.0
2009-09-14 00:00:00.000	2	0.0
2009-09-15 00:00:00.000	2	0.0
2009-09-17 00:00:00.000	2	0.0
2009-09-20 00:00:00.000	2	0.0
2009-09-22 00:00:00.000	2	0.0
2009-09-23 00:00:00.000	2	0.0
2009-09-25 00:00:00.000	2	0.0
2009-09-28 00:00:00.000	2	0.0
2009-09-30 00:00:00.000	2	0.0
2009-08-31 00:00:00.000	3	0.0
2009-09-01 00:00:00.000	3	0.0
2009-09-06 00:00:00.000	3	0.0
2009-09-07 00:00:00.000	3	0.0
2009-09-08 00:00:00.000	3	0.0
2009-09-09 00:00:00.000	3	0.0
2009-09-14 00:00:00.000	3	0.0
2009-09-15 00:00:00.000	3	0.0
2009-09-16 00:00:00.000	3	0.0
2009-09-21 00:00:00.000	3	0.0
2009-09-22 00:00:00.000	3	0.0
2009-09-23 00:00:00.000	3	0.0
2009-09-24 00:00:00.000	3	0.0
2009-09-29 00:00:00.000	3	0.0
2009-09-30 00:00:00.000	3	0.0
2009-09-02 00:00:00.000	4	0.0
2009-09-03 00:00:00.000	4	0.0
2009-09-04 00:00:00.000	4	0.0
2009-09-05 00:00:00.000	4	0.0
2009-09-11 00:00:00.000	4	0.0
2009-09-12 00:00:00.000	4	0.0
2009-09-13 00:00:00.000	4	0.0
2009-09-19 00:00:00.000	4	0.0
2009-09-20 00:00:00.000	4	0.0
2009-09-21 00:00:00.000	4	0.0
2009-09-22 00:00:00.000	4	0.0
2009-09-27 00:00:00.000	4	0.0
2009-09-28 00:00:00.000	4	0.0
2009-09-29 00:00:00.000	4	0.0
2009-09-30 00:00:00.000	4	0.0
2009-09-02 00:00:00.000	5	0.0
2009-09-03 00:00:00.000	5	0.0
2009-09-04 00:00:00.000	5	0.0
2009-09-05 00:00:00.000	5	0.0
2009-09-10 00:00:00.000	5	0.0
2009-09-12 00:00:00.000	5	0.0
2009-09-13 00:00:00.000	5	0.0
2009-09-18 00:00:00.000	5	0.0
2009-09-20 00:00:00.000	5	0.0
2009-09-21 00:00:00.000	5	0.0
2009-09-23 00:00:00.000	5	0.0
2009-09-26 00:00:00.000	5	0.0
2009-09-28 00:00:00.000	5	0.0
2009-09-29 00:00:00.000	5	0.0
2009-09-02 00:00:00.000	6	0.0
2009-09-03 00:00:00.000	6	0.0
2009-09-04 00:00:00.000	6	0.0
2009-09-05 00:00:00.000	6	0.0
2009-09-10 00:00:00.000	6	0.0
2009-09-11 00:00:00.000	6	0.0
2009-09-13 00:00:00.000	6	0.0
2009-09-18 00:00:00.000	6	0.0
2009-09-19 00:00:00.000	6	0.0
2009-09-21 00:00:00.000	6	0.0
2009-09-24 00:00:00.000	6	0.0
2009-09-26 00:00:00.000	6	0.0
2009-09-27 00:00:00.000	6	0.0
2009-09-29 00:00:00.000	6	0.0
2009-09-02 00:00:00.000	7	0.0
2009-09-03 00:00:00.000	7	0.0
2009-09-04 00:00:00.000	7	0.0
2009-09-05 00:00:00.000	7	0.0
2009-09-10 00:00:00.000	7	0.0
2009-09-11 00:00:00.000	7	0.0
2009-09-12 00:00:00.000	7	0.0
2009-09-18 00:00:00.000	7	0.0
2009-09-19 00:00:00.000	7	0.0
2009-09-20 00:00:00.000	7	0.0
2009-09-26 00:00:00.000	7	0.0
2009-09-27 00:00:00.000	7	0.0
2009-09-28 00:00:00.000	7	0.0
2009-08-31 00:00:00.000	8	0.0
2009-09-01 00:00:00.000	8	0.0
2009-09-06 00:00:00.000	8	0.0
2009-09-07 00:00:00.000	8	0.0
2009-09-08 00:00:00.000	8	0.0
2009-09-09 00:00:00.000	8	10.777973667780559
2009-09-10 00:00:00.000	8	0.0
2009-09-15 00:00:00.000	8	0.0
2009-09-16 00:00:00.000	8	0.0
2009-09-17 00:00:00.000	8	0.0
2009-09-23 00:00:00.000	8	0.0
2009-09-24 00:00:00.000	8	0.0
2009-09-25 00:00:00.000	8	0.0
2009-09-26 00:00:00.000	8	0.0
2009-09-27 00:00:00.000	8	0.0
2009-08-31 00:00:00.000	9	0.0
2009-09-01 00:00:00.000	9	0.0
2009-09-06 00:00:00.000	9	0.0
2009-09-07 00:00:00.000	9	0.0
2009-09-08 00:00:00.000	9	0.0
2009-09-09 00:00:00.000	9	10.628547588984171
2009-09-11 00:00:00.000	9	0.0
2009-09-14 00:00:00.000	9	0.0
2009-09-16 00:00:00.000	9	0.0
2009-09-17 00:00:00.000	9	0.0
2009-09-22 00:00:00.000	9	0.0
2009-09-24 00:00:00.000	9	0.0
2009-09-25 00:00:00.000	9	0.0
2009-09-26 00:00:00.000	9	0.0
2009-09-27 00:00:00.000	9	0.0
2009-08-31 00:00:00.000	10	0.0
2009-09-01 00:00:00.000	10	0.0
2009-09-06 00:00:00.000	10	0.0
2009-09-07 00:00:00.000	10	0.0
2009-09-08 00:00:00.000	10	0.0
2009-09-09 00:00:00.000	10	3.3302610238393147
2009-09-12 00:00:00.000	10	0.0
2009-09-14 00:00:00.000	10	0.0
2009-09-15 00:00:00.000	10	0.0
2009-09-17 00:00:00.000	10	0.0
2009-09-22 00:00:00.000	10	0.0
2009-09-23 00:00:00.000	10	0.0
2009-09-25 00:00:00.000	10	0.0
2009-09-28 00:00:00.000	10	9.1484183152516678
2009-09-29 00:00:00.000	10	0.0
2009-09-30 00:00:00.000	10	0.0
2009-08-31 00:00:00.000	11	0.0
2009-09-01 00:00:00.000	11	0.0
2009-09-06 00:00:00.000	11	0.0
2009-09-07 00:00:00.000	11	0.0
2009-09-08 00:00:00.000	11	0.0
2009-09-09 00:00:00.000	11	0.0
2009-09-13 00:00:00.000	11	0.0
2009-09-14 00:00:00.000	11	0.0
2009-09-15 00:00:00.000	11	0.0
2009-09-16 00:00:00.000	11	0.0
2009-09-22 00:00:00.000	11	0.0
2009-09-23 00:00:00.000	11	11.916119035085043
2009-09-24 00:00:00.000	11	0.0
2009-09-28 00:00:00.000	11	9.1334264437357593
2009-09-29 00:00:00.000	11	0.0
2009-09-30 00:00:00.000	11	0.0
2009-09-02 00:00:00.000	12	0.0
2009-09-03 00:00:00.000	12	0.0
2009-09-04 00:00:00.000	12	0.0
2009-09-05 00:00:00.000	12	0.0
2009-09-11 00:00:00.000	12	0.0
2009-09-12 00:00:00.000	12	0.0
2009-09-13 00:00:00.000	12	0.0
2009-09-14 00:00:00.000	12	0.0
2009-09-19 00:00:00.000	12	0.0
2009-09-20 00:00:00.000	12	0.0
2009-09-21 00:00:00.000	12	0.0
2009-09-28 00:00:00.000	12	1.6660522143046061
2009-09-29 00:00:00.000	12	0.0
2009-09-30 00:00:00.000	12	0.0
2009-09-02 00:00:00.000	13	0.0
2009-09-03 00:00:00.000	13	0.0
2009-09-04 00:00:00.000	13	0.0
2009-09-05 00:00:00.000	13	0.0
2009-09-10 00:00:00.000	13	0.0
2009-09-12 00:00:00.000	13	0.0
2009-09-13 00:00:00.000	13	0.0
2009-09-15 00:00:00.000	13	0.0
2009-09-18 00:00:00.000	13	0.0
2009-09-20 00:00:00.000	13	0.0
2009-09-21 00:00:00.000	13	0.0
2009-09-28 00:00:00.000	13	0.0
2009-09-29 00:00:00.000	13	0.0
2009-09-30 00:00:00.000	13	0.0
2009-09-02 00:00:00.000	14	0.0
2009-09-03 00:00:00.000	14	0.0
2009-09-04 00:00:00.000	14	0.0
2009-09-05 00:00:00.000	14	0.0
2009-09-10 00:00:00.000	14	0.0
2009-09-11 00:00:00.000	14	0.0
2009-09-13 00:00:00.000	14	0.0
2009-09-16 00:00:00.000	14	0.0
2009-09-18 00:00:00.000	14	0.0
2009-09-19 00:00:00.000	14	0.0
2009-09-21 00:00:00.000	14	0.0
2009-09-26 00:00:00.000	14	0.0
2009-09-27 00:00:00.000	14	0.0
2009-09-02 00:00:00.000	15	0.0
2009-09-03 00:00:00.000	15	0.0
2009-09-04 00:00:00.000	15	0.0
2009-09-05 00:00:00.000	15	0.0
2009-09-10 00:00:00.000	15	0.0
2009-09-11 00:00:00.000	15	0.0
2009-09-12 00:00:00.000	15	0.0
2009-09-17 00:00:00.000	15	0.0
2009-09-18 00:00:00.000	15	0.0
2009-09-19 00:00:00.000	15	0.0
2009-09-20 00:00:00.000	15	0.0
2009-09-26 00:00:00.000	15	0.0
2009-09-27 00:00:00.000	15	0.0
2009-08-31 00:00:00.000	16	0.0
2009-09-06 00:00:00.000	16	0.0
2009-09-07 00:00:00.000	16	0.0
2009-09-08 00:00:00.000	16	12.798027765952934
2009-09-09 00:00:00.000	16	0.0
2009-09-10 00:00:00.000	16	0.0
2009-09-15 00:00:00.000	16	0.0
2009-09-16 00:00:00.000	16	0.0
2009-09-17 00:00:00.000	16	0.0
2009-09-18 00:00:00.000	16	0.0
2009-09-19 00:00:00.000	16	0.0
2009-09-24 00:00:00.000	16	0.0
2009-09-25 00:00:00.000	16	0.0
2009-09-01 00:00:00.000	17	0.0
2009-09-06 00:00:00.000	17	0.0
2009-09-07 00:00:00.000	17	0.0
2009-09-08 00:00:00.000	17	2.7068739255269367
2009-09-09 00:00:00.000	17	0.0
2009-09-11 00:00:00.000	17	0.0
2009-09-14 00:00:00.000	17	0.0
2009-09-16 00:00:00.000	17	0.0
2009-09-17 00:00:00.000	17	0.0
2009-09-18 00:00:00.000	17	0.0
2009-09-19 00:00:00.000	17	0.0
2009-09-24 00:00:00.000	17	0.0
2009-09-25 00:00:00.000	17	0.0
2009-09-30 00:00:00.000	17	0.0
2009-08-31 00:00:00.000	18	0.0
2009-09-01 00:00:00.000	18	0.0
2009-09-06 00:00:00.000	18	0.0
2009-09-07 00:00:00.000	18	0.0
2009-09-08 00:00:00.000	18	0.0
2009-09-09 00:00:00.000	18	0.0
2009-09-12 00:00:00.000	18	0.0
2009-09-14 00:00:00.000	18	0.0
2009-09-15 00:00:00.000	18	0.0
2009-09-17 00:00:00.000	18	0.0
2009-09-20 00:00:00.000	18	0.0
2009-09-21 00:00:00.000	18	0.0
2009-09-22 00:00:00.000	18	0.0
2009-09-23 00:00:00.000	18	0.0
2009-09-30 00:00:00.000	18	0.0
2009-08-31 00:00:00.000	19	0.0
2009-09-01 00:00:00.000	19	0.0
2009-09-06 00:00:00.000	19	0.0
2009-09-07 00:00:00.000	19	0.0
2009-09-08 00:00:00.000	19	0.0
2009-09-09 00:00:00.000	19	0.0
2009-09-13 00:00:00.000	19	0.0
2009-09-14 00:00:00.000	19	0.0
2009-09-15 00:00:00.000	19	0.0
2009-09-16 00:00:00.000	19	0.0
2009-09-20 00:00:00.000	19	0.0
2009-09-21 00:00:00.000	19	0.0
2009-09-22 00:00:00.000	19	0.0
2009-09-23 00:00:00.000	19	0.0
2009-09-30 00:00:00.000	19	0.0
2009-09-01 00:00:00.000	20	0.0
2009-09-02 00:00:00.000	20	0.0
2009-09-03 00:00:00.000	20	0.0
2009-09-04 00:00:00.000	20	0.0
2009-09-05 00:00:00.000	20	0.0
2009-09-11 00:00:00.000	20	0.0
2009-09-12 00:00:00.000	20	0.0
2009-09-13 00:00:00.000	20	0.0
2009-09-14 00:00:00.000	20	0.0
2009-09-20 00:00:00.000	20	0.0
2009-09-21 00:00:00.000	20	0.0
2009-09-22 00:00:00.000	20	0.0
2009-09-23 00:00:00.000	20	0.0
2009-09-27 00:00:00.000	20	0.0
2009-09-28 00:00:00.000	20	0.0
2009-09-29 00:00:00.000	20	0.0
2009-08-31 00:00:00.000	21	0.0
2009-09-02 00:00:00.000	21	0.0
2009-09-03 00:00:00.000	21	0.0
2009-09-04 00:00:00.000	21	0.0
2009-09-05 00:00:00.000	21	0.0
2009-09-10 00:00:00.000	21	0.0
2009-09-12 00:00:00.000	21	0.0
2009-09-13 00:00:00.000	21	0.0
2009-09-15 00:00:00.000	21	0.0
2009-09-20 00:00:00.000	21	0.0
2009-09-21 00:00:00.000	21	0.0
2009-09-22 00:00:00.000	21	0.0
2009-09-23 00:00:00.000	21	0.0
2009-09-26 00:00:00.000	21	0.0
2009-09-28 00:00:00.000	21	0.0
2009-09-29 00:00:00.000	21	0.0
2009-09-02 00:00:00.000	22	0.0
2009-09-03 00:00:00.000	22	0.0
2009-09-04 00:00:00.000	22	0.0
2009-09-05 00:00:00.000	22	0.0
2009-09-10 00:00:00.000	22	0.0
2009-09-11 00:00:00.000	22	0.0
2009-09-13 00:00:00.000	22	0.0
2009-09-16 00:00:00.000	22	0.0
2009-09-18 00:00:00.000	22	0.0
2009-09-19 00:00:00.000	22	0.0
2009-09-24 00:00:00.000	22	0.0
2009-09-25 00:00:00.000	22	0.0
2009-09-26 00:00:00.000	22	0.0
2009-09-27 00:00:00.000	22	0.0
2009-09-29 00:00:00.000	22	0.0
2009-09-02 00:00:00.000	23	0.0
2009-09-03 00:00:00.000	23	0.0
2009-09-04 00:00:00.000	23	0.0
2009-09-05 00:00:00.000	23	0.0
2009-09-10 00:00:00.000	23	0.0
2009-09-11 00:00:00.000	23	0.0
2009-09-12 00:00:00.000	23	0.0
2009-09-17 00:00:00.000	23	0.0
2009-09-18 00:00:00.000	23	0.0
2009-09-19 00:00:00.000	23	0.0
2009-09-24 00:00:00.000	23	0.0
2009-09-25 00:00:00.000	23	0.0
2009-09-26 00:00:00.000	23	0.0
2009-09-27 00:00:00.000	23	0.0
2009-09-28 00:00:00.000	23	0.0
2009-09-27 00:00:00.000	12	0.0
2009-08-31 00:00:00.000	13	0.0
2009-09-01 00:00:00.000	13	0.0
2009-09-06 00:00:00.000	13	0.0
2009-09-07 00:00:00.000	13	0.0
2009-09-08 00:00:00.000	13	0.0
2009-09-09 00:00:00.000	13	0.0
2009-09-11 00:00:00.000	13	0.0
2009-09-14 00:00:00.000	13	0.0
2009-09-16 00:00:00.000	13	0.0
2009-09-17 00:00:00.000	13	0.0
2009-09-19 00:00:00.000	13	0.0
2009-09-22 00:00:00.000	13	0.0
2009-09-23 00:00:00.000	13	4.1081125100453697
2009-09-24 00:00:00.000	13	0.0
2009-09-25 00:00:00.000	13	0.0
2009-09-26 00:00:00.000	13	0.0
2009-09-27 00:00:00.000	13	0.0
2009-08-31 00:00:00.000	14	0.0
2009-09-01 00:00:00.000	14	0.0
2009-09-06 00:00:00.000	14	0.0
2009-09-07 00:00:00.000	14	0.0
2009-09-08 00:00:00.000	14	0.0
2009-09-09 00:00:00.000	14	0.0
2009-09-12 00:00:00.000	14	0.0
2009-09-14 00:00:00.000	14	0.0
2009-09-15 00:00:00.000	14	0.0
2009-09-17 00:00:00.000	14	0.0
2009-09-20 00:00:00.000	14	0.0
2009-09-22 00:00:00.000	14	0.0
2009-09-23 00:00:00.000	14	0.0
2009-09-24 00:00:00.000	14	0.0
2009-09-25 00:00:00.000	14	0.0
2009-09-28 00:00:00.000	14	0.0
2009-09-29 00:00:00.000	14	0.0
2009-09-30 00:00:00.000	14	0.0
2009-08-31 00:00:00.000	15	0.0
2009-09-01 00:00:00.000	15	0.0
2009-09-06 00:00:00.000	15	0.0
2009-09-07 00:00:00.000	15	0.0
2009-09-08 00:00:00.000	15	2.03949457804362
2009-09-09 00:00:00.000	15	0.0
2009-09-13 00:00:00.000	15	0.0
2009-09-14 00:00:00.000	15	0.0
2009-09-15 00:00:00.000	15	0.0
2009-09-16 00:00:00.000	15	0.0
2009-09-21 00:00:00.000	15	0.0
2009-09-22 00:00:00.000	15	0.0
2009-09-23 00:00:00.000	15	0.0
2009-09-24 00:00:00.000	15	0.0
2009-09-25 00:00:00.000	15	0.0
2009-09-28 00:00:00.000	15	0.0
2009-09-29 00:00:00.000	15	0.0
2009-09-30 00:00:00.000	15	0.0
2009-09-01 00:00:00.000	16	0.0
2009-09-02 00:00:00.000	16	0.0
2009-09-03 00:00:00.000	16	0.0
2009-09-04 00:00:00.000	16	0.0
2009-09-05 00:00:00.000	16	0.0
2009-09-11 00:00:00.000	16	0.0
2009-09-12 00:00:00.000	16	0.0
2009-09-13 00:00:00.000	16	0.0
2009-09-14 00:00:00.000	16	0.0
2009-09-20 00:00:00.000	16	0.0
2009-09-21 00:00:00.000	16	0.0
2009-09-22 00:00:00.000	16	0.0
2009-09-23 00:00:00.000	16	0.0
2009-09-26 00:00:00.000	16	0.0
2009-09-27 00:00:00.000	16	0.0
2009-09-28 00:00:00.000	16	0.0
2009-09-29 00:00:00.000	16	0.0
2009-09-30 00:00:00.000	16	0.0
2009-08-31 00:00:00.000	17	0.0
2009-09-02 00:00:00.000	17	0.0
2009-09-03 00:00:00.000	17	0.0
2009-09-04 00:00:00.000	17	0.0
2009-09-05 00:00:00.000	17	0.0
2009-09-10 00:00:00.000	17	0.0
2009-09-12 00:00:00.000	17	0.0
2009-09-13 00:00:00.000	17	0.0
2009-09-15 00:00:00.000	17	0.0
2009-09-20 00:00:00.000	17	0.0
2009-09-21 00:00:00.000	17	0.0
2009-09-22 00:00:00.000	17	0.0
2009-09-23 00:00:00.000	17	0.0
2009-09-26 00:00:00.000	17	0.0
2009-09-27 00:00:00.000	17	0.0
2009-09-28 00:00:00.000	17	0.0
2009-09-29 00:00:00.000	17	0.0
2009-09-02 00:00:00.000	18	0.0
2009-09-03 00:00:00.000	18	0.0
2009-09-04 00:00:00.000	18	0.0
2009-09-05 00:00:00.000	18	0.0
2009-09-10 00:00:00.000	18	0.0
2009-09-11 00:00:00.000	18	0.0
2009-09-13 00:00:00.000	18	0.0
2009-09-16 00:00:00.000	18	0.0
2009-09-18 00:00:00.000	18	0.0
2009-09-19 00:00:00.000	18	0.0
2009-09-24 00:00:00.000	18	0.0
2009-09-25 00:00:00.000	18	0.0
2009-09-26 00:00:00.000	18	0.0
2009-09-27 00:00:00.000	18	0.0
2009-09-28 00:00:00.000	18	0.0
2009-09-29 00:00:00.000	18	0.0
2009-09-02 00:00:00.000	19	0.0
2009-09-03 00:00:00.000	19	0.0
2009-09-04 00:00:00.000	19	0.0
2009-09-05 00:00:00.000	19	0.0
2009-09-10 00:00:00.000	19	0.0
2009-09-11 00:00:00.000	19	0.0
2009-09-12 00:00:00.000	19	0.0
2009-09-17 00:00:00.000	19	0.0
2009-09-18 00:00:00.000	19	0.0
2009-09-19 00:00:00.000	19	0.0
2009-09-24 00:00:00.000	19	0.0
2009-09-25 00:00:00.000	19	0.0
2009-09-26 00:00:00.000	19	0.0
2009-09-27 00:00:00.000	19	0.0
2009-09-28 00:00:00.000	19	0.0
2009-09-29 00:00:00.000	19	0.0
2009-08-31 00:00:00.000	20	0.0
2009-09-06 00:00:00.000	20	0.0
2009-09-07 00:00:00.000	20	0.0
2009-09-08 00:00:00.000	20	0.0
2009-09-09 00:00:00.000	20	0.0
2009-09-10 00:00:00.000	20	0.0
2009-09-15 00:00:00.000	20	0.0
2009-09-16 00:00:00.000	20	0.0
2009-09-17 00:00:00.000	20	0.0
2009-09-18 00:00:00.000	20	0.0
2009-09-19 00:00:00.000	20	0.0
2009-09-24 00:00:00.000	20	0.0
2009-09-25 00:00:00.000	20	0.0
2009-09-26 00:00:00.000	20	0.0
2009-09-30 00:00:00.000	20	0.0
2009-09-01 00:00:00.000	21	0.0
2009-09-06 00:00:00.000	21	0.0
2009-09-07 00:00:00.000	21	0.0
2009-09-08 00:00:00.000	21	0.0
2009-09-09 00:00:00.000	21	0.0
2009-09-11 00:00:00.000	21	0.0
2009-09-14 00:00:00.000	21	0.0
2009-09-16 00:00:00.000	21	0.0
2009-09-17 00:00:00.000	21	0.0
2009-09-18 00:00:00.000	21	0.0
2009-09-19 00:00:00.000	21	0.0
2009-09-24 00:00:00.000	21	0.0
2009-09-25 00:00:00.000	21	0.0
2009-09-27 00:00:00.000	21	0.0
2009-09-30 00:00:00.000	21	0.0
2009-08-31 00:00:00.000	22	0.0
2009-09-01 00:00:00.000	22	0.0
2009-09-06 00:00:00.000	22	0.0
2009-09-07 00:00:00.000	22	0.0
2009-09-08 00:00:00.000	22	0.0
2009-09-09 00:00:00.000	22	0.0
2009-09-12 00:00:00.000	22	0.0
2009-09-14 00:00:00.000	22	0.0
2009-09-15 00:00:00.000	22	0.0
2009-09-17 00:00:00.000	22	0.0
2009-09-20 00:00:00.000	22	0.0
2009-09-21 00:00:00.000	22	0.0
2009-09-22 00:00:00.000	22	0.0
2009-09-23 00:00:00.000	22	0.0
2009-09-28 00:00:00.000	22	0.0
2009-09-30 00:00:00.000	22	0.0
2009-08-31 00:00:00.000	23	0.0
2009-09-01 00:00:00.000	23	0.0
2009-09-06 00:00:00.000	23	0.0
2009-09-07 00:00:00.000	23	0.0
2009-09-08 00:00:00.000	23	0.0
2009-09-09 00:00:00.000	23	0.0
2009-09-13 00:00:00.000	23	0.0
2009-09-14 00:00:00.000	23	0.0
2009-09-15 00:00:00.000	23	0.0
2009-09-16 00:00:00.000	23	0.0
2009-09-20 00:00:00.000	23	0.0
2009-09-21 00:00:00.000	23	0.0
2009-09-22 00:00:00.000	23	0.0
2009-09-23 00:00:00.000	23	0.0
2009-09-29 00:00:00.000	23	0.0
2009-09-30 00:00:00.000	23	0.0

I think there is only data available for august as well so there is not an issue with the between statement...

Thanks
 
Upvote 0 Downvote
I don't know your data :)
Could you post some example data and desired result from it?

Borislav Borissov
VFP9 SP2, SQL Server
 
Upvote 0 Downvote
My appologies, I didnt use the code operators in my first submission.
REAL DATA

Code: 
DateAndTime	Tag	Val
5/4/2010 17:43	24	124.1648865
5/4/2010 17:43	25	199.9999847
5/4/2010 17:43	26	2522500
5/4/2010 17:43	27	0
5/4/2010 17:43	28	1081458
5/4/2010 17:43	29	0
5/4/2010 17:44	0	18.65209198
5/4/2010 17:44	1	3.628157377
5/4/2010 17:44	2	13.42585564
5/4/2010 17:44	3	0
5/4/2010 17:44	4	0
5/4/2010 17:44	5	0
5/4/2010 17:44	6	0
5/4/2010 17:44	7	0
5/4/2010 17:44	8	4
5/4/2010 17:44	9	316
5/4/2010 17:44	10	310
5/4/2010 17:44	11	302
5/4/2010 17:44	12	310
5/4/2010 17:44	13	138
5/4/2010 17:44	14	310
5/4/2010 17:44	15	0
5/4/2010 17:44	16	5.392224312
5/4/2010 17:44	17	0
5/4/2010 17:44	18	0
5/4/2010 17:44	19	0
5/4/2010 17:44	20	0
5/4/2010 17:44	21	-116624.8984
5/4/2010 17:44	22	100.2602234
5/4/2010 17:44	23	232248.4063
5/4/2010 17:44	24	124.1648865
5/4/2010 17:44	25	199.9999847
5/4/2010 17:44	26	2524812
5/4/2010 17:44	27	0
5/4/2010 17:44	28	1083957
5/4/2010 17:44	29	0
5/4/2010 17:45	0	18.65209198

RESULTS I Am looking for
Code: 
per hour (from 9/11/2009 to 9/12/2009)

[u]TimeandDate[/u]       [u] average per hour[/u]
2009-09-11 00:59:27.000 	0.0
2009-09-11 01:59:15.000 	0.0
2009-09-11 02:59:19.000 	0.0
2009-09-11 03:59:28.000 	0.0
2009-09-11 04:59:32.000 	0.0
2009-09-11 05:59:37.000 	0.0
2009-09-11 06:59:41.000 	0.0
2009-09-11 07:59:45.000 	0.0
2009-09-11 08:59:49.000 	0.0
2009-09-11 09:59:54.000 	0.0
2009-09-11 10:59:58.000 	0.0
2009-09-11 11:59:01.000 	0.0
2009-09-11 12:59:05.000 	0.0
2009-09-11 13:59:11.000 	0.0
2009-09-11 14:59:15.000 	0.0
2009-09-11 15:59:19.000 	0.0
2009-09-11 16:59:22.000 	0.0
2009-09-11 17:59:26.000 	2.8
2009-09-11 18:59:29.000 	0.0
2009-09-11 19:59:32.000 	0.0
2009-09-11 20:59:36.000 	0.0
2009-09-11 21:59:38.000 	7.2
2009-09-11 22:59:41.000 	0.0
2009-09-11 23:59:08.000        10.8
2009-09-12 00:59:27.000 	0.0
2009-09-12 01:59:15.000 	0.0
2009-09-12 02:59:19.000 	0.0
2009-09-12 03:59:28.000 	0.0
2009-09-12 04:59:32.000 	0.0
2009-09-12 05:59:37.000 	0.0
2009-09-12 06:59:41.000 	0.0
2009-09-12 07:59:45.000 	0.0
2009-09-12 08:59:49.000 	0.0
2009-09-12 09:59:54.000 	0.0
2009-09-12 10:59:58.000 	0.0
2009-09-12 11:59:01.000 	0.0
2009-09-12 12:59:05.000 	0.0
2009-09-12 13:59:11.000 	0.0
2009-09-12 14:59:15.000 	0.0
2009-09-12 15:59:19.000 	0.0
2009-09-12 16:59:22.000        14.0
2009-09-12 17:59:26.000 	0.0
2009-09-12 18:59:29.000 	0.0
2009-09-12 19:59:32.000 	0.0
2009-09-12 20:59:36.000 	0.0
2009-09-12 21:59:38.000 	0.0
2009-09-12 22:59:41.000 	0.0
2009-09-12 23:59:08.000 	0.0

per day (from 9/11/2009 to 9/15/2009)

Code: 
[u]TimeandDate[/u]       [u] average per day[/u]
2009-09-11                     10.0
2009-09-12               	7.3
2009-09-13              	0.0
2009-09-14               	4.2
2009-09-15               	0.0

thanks
 
Upvote 0 Downvote
Hm,
how can I get results for 2009 from data that you provide :)
All data you post is from 2010 :)

Borislav Borissov
VFP9 SP2, SQL Server
 
Upvote 0 Downvote
Sorry for the confusion :)

The date ranges are going to be variables and change on many different tables and between different date ranges, I was just using this time period as an example to help explain my question. I thought about my mistake as I saved the data to this post, very sorry. I was trying to modify a different query to work and just copied the results. I am sorry for the confusion!

The real data I am using is recorded every minute, so a large amount of data to get an average per hour. here is just 4 hours of data in the date range. Many of the days have values of 0, so this is a date with some actual recorded numbers.

Code: 
DateAndTime	Val
9/9/2009 8:01:29 AM	11.6198472976685
9/9/2009 8:02:29 AM	11.2659435272217
9/9/2009 8:03:29 AM	11.6419658660889
9/9/2009 8:04:29 AM	11.3323011398315
9/9/2009 8:05:29 AM	11.1848411560059
9/9/2009 8:06:29 AM	11.0742454528809
9/9/2009 8:07:29 AM	10.9636507034302
9/9/2009 8:08:30 AM	10.9046649932861
9/9/2009 8:09:30 AM	10.8604278564453
9/9/2009 8:10:30 AM	10.5728816986084
9/9/2009 8:11:30 AM	10.6613569259644
9/9/2009 8:12:30 AM	10.7645797729492
9/9/2009 8:13:30 AM	10.7498321533203
9/9/2009 8:14:30 AM	10.7203416824341
9/9/2009 8:15:30 AM	10.6908502578735
9/9/2009 8:16:30 AM	10.6908502578735
9/9/2009 8:17:30 AM	10.6834774017334
9/9/2009 8:18:30 AM	10.7424592971802
9/9/2009 8:19:30 AM	10.5065240859985
9/9/2009 8:20:30 AM	10.9120397567749
9/9/2009 8:21:30 AM	10.8235626220703
9/9/2009 8:22:30 AM	10.7940721511841
9/9/2009 8:23:30 AM	10.8014450073242
9/9/2009 8:24:30 AM	10.8088178634644
9/9/2009 8:25:30 AM	10.8014450073242
9/9/2009 8:26:30 AM	10.7940721511841
9/9/2009 8:27:31 AM	10.6908502578735
9/9/2009 8:28:31 AM	10.5286436080933
9/9/2009 8:29:31 AM	10.7498321533203
9/9/2009 8:30:31 AM	10.7424592971802
9/9/2009 8:31:31 AM	10.6982221603394
9/9/2009 8:32:31 AM	10.6687297821045
9/9/2009 8:33:31 AM	10.6687297821045
9/9/2009 8:34:31 AM	10.6392393112183
9/9/2009 8:35:31 AM	10.6613569259644
9/9/2009 8:36:31 AM	10.1673669815063
9/9/2009 8:37:31 AM	10.934157371521
9/9/2009 8:38:31 AM	10.8309373855591
9/9/2009 8:39:31 AM	10.7940721511841
9/9/2009 8:40:31 AM	10.7719526290894
9/9/2009 8:41:31 AM	10.7645797729492
9/9/2009 8:42:31 AM	10.7424592971802
9/9/2009 8:43:31 AM	10.4696588516235
9/9/2009 8:44:32 AM	10.5581340789795
9/9/2009 8:45:32 AM	10.6834774017334
9/9/2009 8:46:32 AM	10.6613569259644
9/9/2009 8:47:32 AM	10.6318645477295
9/9/2009 8:48:32 AM	10.6097469329834
9/9/2009 8:49:32 AM	10.6097469329834
9/9/2009 8:50:32 AM	10.6023740768433
9/9/2009 8:51:32 AM	10.4696588516235
9/9/2009 8:52:32 AM	10.5286436080933
9/9/2009 8:53:32 AM	10.6687297821045
9/9/2009 8:54:32 AM	10.7350873947144
9/9/2009 8:55:32 AM	10.7424592971802
9/9/2009 8:56:32 AM	10.6982221603394
9/9/2009 8:57:32 AM	10.6834774017334
9/9/2009 8:58:32 AM	10.6613569259644
9/9/2009 8:59:32 AM	10.6392393112183
9/9/2009 9:00:32 AM	10.3074531555176
9/9/2009 9:01:32 AM	10.3959283828735
9/9/2009 9:02:33 AM	10.6023740768433
9/9/2009 9:03:33 AM	10.6097469329834
9/9/2009 9:04:33 AM	10.5876264572144
9/9/2009 9:05:33 AM	10.5581340789795
9/9/2009 9:06:33 AM	10.5507612228394
9/9/2009 9:07:33 AM	10.5507612228394
9/9/2009 9:08:33 AM	10.5360164642334
9/9/2009 9:09:33 AM	10.2927083969116
9/9/2009 9:10:33 AM	10.7940721511841
9/9/2009 9:11:33 AM	10.7203416824341
9/9/2009 9:12:33 AM	10.7498321533203
9/9/2009 9:13:33 AM	10.7203416824341
9/9/2009 9:14:33 AM	10.6982221603394
9/9/2009 9:15:33 AM	10.6908502578735
9/9/2009 9:16:33 AM	10.7129669189453
9/9/2009 9:17:33 AM	10.6687297821045
9/9/2009 9:18:33 AM	10.3738107681274
9/9/2009 9:19:34 AM	10.5212717056274
9/9/2009 9:20:34 AM	10.6318645477295
9/9/2009 9:21:34 AM	10.6392393112183
9/9/2009 9:22:34 AM	10.6023740768433
9/9/2009 9:23:34 AM	10.6097469329834
9/9/2009 9:24:34 AM	10.5876264572144
9/9/2009 9:25:34 AM	10.6097469329834
9/9/2009 9:26:34 AM	10.4180488586426
9/9/2009 9:27:34 AM	10.8751745223999
9/9/2009 9:28:34 AM	10.8456830978394
9/9/2009 9:29:34 AM	10.8088178634644
9/9/2009 9:30:34 AM	10.7940721511841
9/9/2009 9:31:34 AM	10.7498321533203
9/9/2009 9:32:34 AM	10.7203416824341
9/9/2009 9:33:34 AM	10.7129669189453
9/9/2009 9:34:34 AM	10.6908502578735
9/9/2009 9:35:35 AM	10.4549140930176
9/9/2009 9:36:35 AM	10.4917793273926
9/9/2009 9:37:35 AM	10.6539850234985
9/9/2009 9:38:35 AM	10.6613569259644
9/9/2009 9:39:35 AM	10.6097469329834
9/9/2009 9:40:35 AM	10.6023740768433
9/9/2009 9:41:35 AM	10.6392393112183
9/9/2009 9:42:35 AM	10.4991512298584
9/9/2009 9:43:35 AM	10.7645797729492
9/9/2009 9:44:35 AM	10.8530550003052
9/9/2009 9:45:35 AM	10.8235626220703
9/9/2009 9:46:35 AM	10.7940721511841
9/9/2009 9:47:35 AM	10.7940721511841
9/9/2009 9:48:35 AM	10.7498321533203
9/9/2009 9:49:35 AM	10.6982221603394
9/9/2009 9:50:35 AM	10.2263507843018
9/9/2009 9:51:35 AM	10.4991512298584
9/9/2009 9:52:35 AM	10.6539850234985
9/9/2009 9:53:35 AM	10.6097469329834
9/9/2009 9:54:36 AM	10.5728816986084
9/9/2009 9:55:36 AM	10.5876264572144
9/9/2009 9:56:36 AM	10.6613569259644
9/9/2009 9:57:36 AM	10.6982221603394
9/9/2009 9:58:36 AM	10.7645797729492
9/9/2009 9:59:36 AM	10.4106760025024
9/9/2009 10:00:36 AM	10.477032661438
9/9/2009 10:01:36 AM	10.6982221603394
9/9/2009 10:02:36 AM	10.6539850234985
9/9/2009 10:03:36 AM	10.6539850234985
9/9/2009 10:04:36 AM	10.5876264572144
9/9/2009 10:05:36 AM	10.5286436080933
9/9/2009 10:06:36 AM	10.5286436080933
9/9/2009 10:07:36 AM	10.4991512298584
9/9/2009 10:08:37 AM	10.2779607772827
9/9/2009 10:09:37 AM	10.2779607772827
9/9/2009 10:10:37 AM	10.6613569259644
9/9/2009 10:11:37 AM	10.6687297821045
9/9/2009 10:12:37 AM	10.6318645477295
9/9/2009 10:13:37 AM	10.6023740768433
9/9/2009 10:14:37 AM	10.5802545547485
9/9/2009 10:15:37 AM	10.5360164642334
9/9/2009 10:16:37 AM	10.5286436080933
9/9/2009 10:17:37 AM	10.5212717056274
9/9/2009 10:18:37 AM	9.90193843841553
9/9/2009 10:19:37 AM	0
9/9/2009 10:20:37 AM	0
9/9/2009 10:21:37 AM	0
9/9/2009 10:22:37 AM	0
9/9/2009 10:23:37 AM	0
9/9/2009 10:24:37 AM	0
9/9/2009 10:25:37 AM	0
9/9/2009 10:26:38 AM	0
9/9/2009 10:27:38 AM	0
9/9/2009 10:28:38 AM	0
9/9/2009 10:29:38 AM	0
9/9/2009 10:30:38 AM	0
9/9/2009 10:31:38 AM	0
9/9/2009 10:32:38 AM	0
9/9/2009 10:33:38 AM	0
9/9/2009 10:34:38 AM	0
9/9/2009 10:35:38 AM	0
9/9/2009 10:36:38 AM	0
9/9/2009 10:37:38 AM	0
9/9/2009 10:38:38 AM	0
9/9/2009 10:39:39 AM	0
9/9/2009 10:40:39 AM	0
9/9/2009 10:41:39 AM	0
9/9/2009 10:42:39 AM	0
9/9/2009 10:43:39 AM	0
9/9/2009 10:44:39 AM	0
9/9/2009 10:45:39 AM	0
9/9/2009 10:46:39 AM	0
9/9/2009 10:47:39 AM	0
9/9/2009 10:48:39 AM	0
9/9/2009 10:49:39 AM	0
9/9/2009 10:50:39 AM	0
9/9/2009 10:51:39 AM	0
9/9/2009 10:52:39 AM	0
9/9/2009 10:53:40 AM	0
9/9/2009 10:54:40 AM	0
9/9/2009 10:55:40 AM	0
9/9/2009 10:56:40 AM	0
9/9/2009 10:57:40 AM	0
9/9/2009 10:58:40 AM	0
9/9/2009 10:59:40 AM	0
9/9/2009 11:00:40 AM	0
9/9/2009 11:01:40 AM	0
9/9/2009 11:02:40 AM	0
9/9/2009 11:03:40 AM	0
9/9/2009 11:04:40 AM	0
9/9/2009 11:05:40 AM	0
9/9/2009 11:06:40 AM	0
9/9/2009 11:07:40 AM	0
9/9/2009 11:08:41 AM	0
9/9/2009 11:09:41 AM	0
9/9/2009 11:10:41 AM	0
9/9/2009 11:11:41 AM	0
9/9/2009 11:12:41 AM	0
9/9/2009 11:13:41 AM	0
9/9/2009 11:14:41 AM	0
9/9/2009 11:15:41 AM	0
9/9/2009 11:16:41 AM	0
9/9/2009 11:17:41 AM	0
9/9/2009 11:18:41 AM	0
9/9/2009 11:19:41 AM	0
9/9/2009 11:20:41 AM	0
9/9/2009 11:21:41 AM	0
9/9/2009 11:22:41 AM	0
9/9/2009 11:23:42 AM	0
9/9/2009 11:24:42 AM	0
9/9/2009 11:25:42 AM	0
9/9/2009 11:26:42 AM	0
9/9/2009 11:27:42 AM	0
9/9/2009 11:28:42 AM	0
9/9/2009 11:29:42 AM	0
9/9/2009 11:30:42 AM	0
9/9/2009 11:31:42 AM	0
9/9/2009 11:32:42 AM	0
9/9/2009 11:33:42 AM	0
9/9/2009 11:34:42 AM	0
9/9/2009 11:35:42 AM	0
9/9/2009 11:36:43 AM	0
9/9/2009 11:37:43 AM	0
9/9/2009 11:38:43 AM	0
9/9/2009 11:39:43 AM	0
9/9/2009 11:40:43 AM	0
9/9/2009 11:41:43 AM	0
9/9/2009 11:42:43 AM	0
9/9/2009 11:43:43 AM	0
9/9/2009 11:44:43 AM	0
9/9/2009 11:45:43 AM	0
9/9/2009 11:46:43 AM	0
9/9/2009 11:47:43 AM	0
9/9/2009 11:48:43 AM	0
9/9/2009 11:49:43 AM	0
9/9/2009 11:50:43 AM	0
9/9/2009 11:51:43 AM	0
9/9/2009 11:52:43 AM	0
9/9/2009 11:53:43 AM	0
9/9/2009 11:54:43 AM	0
9/9/2009 11:55:43 AM	0
9/9/2009 11:56:43 AM	0
9/9/2009 11:57:43 AM	0
9/9/2009 11:58:43 AM	0
9/9/2009 11:59:44 AM	0

I appreciate all the help!! I have been reading my old SQL 2000 server book on TSQL and going through DateAdd functions and joins and am getting more confused. I havent had to write SQL queries in many years, the last time I posted in the forum I was even more confused!

 
Upvote 0 Downvote
Can you try these queries?

[coode]
Select DateAdd(Hour, DateDiff(Hour, 0, DateAndTime), 0), Avg(Val)
From ftb_analogs
Group By DateAdd(Hour, DateDiff(Hour, 0, DateAndTime), 0)
[/code]

Code: 
Select	DateAdd(Day, DateDiff(Day, 0, DateAndTime), 0), Avg(Val)
From	ftb_analogs
Group By DateAdd(Day, DateDiff(Day, 0, DateAndTime), 0)

I'm curious... how many rows are in this table? My concern is that the performance of these queries could be really bad. If that's the case, I may have some ideas that will speed things up a bit.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Thanks George! Those worked!
I was referencing some old posts you had made from 2008 to my questions looking for the correct way to average hourly data!
Thanks again!

I have 48,314,220 records in this table total,

I tested this query out

Code: 
Select DateAdd(Hour, DateDiff(Hour, 0, DateAndTime), 0), Avg(Val)
From	ftbl_analogs
WHERE      (TagIndex = '3') and (val > 0)  AND (DateAndTime >= CONVERT(DATETIME, '2009-9-8 00:00:00', 102)) AND 
                                                   (DateAndTime < CONVERT(DATETIME, '2009-9-9 00:00:00', 102)) 
Group By DateAdd(Hour, DateDiff(Hour, 0, DateAndTime), 0)

This was fast.

the daily average took a minute to do for 246 records for

Code: 
Select	DateAdd(Day, DateDiff(Day, 0, DateAndTime), 0), avg(val)
From	ftbl_analogs
WHERE      (TagIndex = '3') and (val > 0 )AND (DateAndTime >= CONVERT(DATETIME, '2009-9-8 00:00:00', 102)) AND 
                                                   (DateAndTime < CONVERT(DATETIME, '2009-9-12 00:00:00', 102)) 
Group By DateAdd(Day, DateDiff(Day, 0, DateAndTime), 0)

Thanks for the help!
 
Upvote 0 Downvote
Are you satisfied? Do you want to make this faster? Do you have authorization to make table changes?

Option 1
I'm thinking that we could add a couple of persisted computed columns to your table, and then we could include those persisted columns in an index. This should make the select queries faster, but will also make the inserts a tiny bit slower.

Option 2
You could add 2 new additional tables to the database. One table would have the daily averages pre-calculated and the other would have the hourly averages pre-calculated. With this option, you would probably want to create a SQL job that runs every day that adds new rows to the table.

There are a couple of problems with this approach. If old data changes, then you would essentially need to recreate ALL the data in the extra tables on a regular basis. If the data does not change, but you need to query this data for the active day, then you would effectively need to run 2 queries and Union the data together.

Both methods have their pros and cons. If you are interested in doing this, let me know and I will step you through the process in such a way that you would completely understand it.

Messing around with a table the has 50 million rows can be a bit tricky.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Thanks Again!

Yes I have complete access to the tables. I would like to make the queries faster!

I don't think option 1 is a good idea, another application for process control creates these database tables automatically.

I have been wanting to format the information to a more usable structure for some time. This is a production database with data being written often to many tables. I would like to learn more about Option 2 for sure!

I have lots of historical data that I would like to do in chunks at a time. I have been trying to learn to use jobs as a way to move old data to another database to store and use for reporting and keep the production database to a smaller size on faster drives. I have about 40 other tables in this database with over 1,200,000,000 records.
 
Upvote 0 Downvote
Your sample data shows data from 2009, 3 years ago. Is there ever a situation where old data is updated?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
That's good, because it means we won't need to ever "re-process" the old data. All we really need to do is to make sure we continually add new data to the summary tables.

I would create 2 summary tables, like this....

Code: 
Create 
Table   ftbl_analogs_[!]hourly[/!](
        DateAndTime DateTime,
        AverageValue float
        Primary Key (DateAndTime))

Create 
Table   ftbl_analogs_[!]Daily[/!](
        DateAndTime DateTime,
        AverageValue float
        Primary Key (DateAndTime))

Next, you'll want to populate this with all of the data. Please understand that when you run the following queries, it will take a long time. If you can run these queries when nobody is connected to the database, that would be best.

Code: 
Insert 
Into	ftbl_analogs_hourly(DateAndTime, AverageValue)
Select  DateAdd(Hour, DateDiff(Hour, 0, DateAndTime), 0), Avg(Val)
From	ftbl_analogs
WHERE   TagIndex = '3'
        and val > 0 
        And DateAndTime < DateAdd(Day, DateDiff(Day, 0, GetDate()), 0) 
Group By DateAdd(Hour, DateDiff(Hour, 0, DateAndTime), 0)

Code: 
Insert 
Into	ftbl_analogs_daily(DateAndTime, AverageValue)
Select  DateAdd(Day, DateDiff(Day, 0, DateAndTime), 0), Avg(Val)
From	ftbl_analogs
WHERE   TagIndex = '3'
        and val > 0 
        And DateAndTime < DateAdd(Day, DateDiff(Day, 0, GetDate()), 0) 
Group By DateAdd(Day, DateDiff(Day, 0, DateAndTime), 0)

Those queries above will do all of the historical data. It will not process anything for today.

Next, we'll want to create a stored procedure that inserts the new data.

Code: 
Create Procedure Insert_History_Analogs
AS
SET NOCOUNT ON

Delete
From	ftbl_analogs_hourly
Where	DateAndTime >= DateAdd(Day, -1 + DateDiff(Day, 0, GetDate()), 0) 
		And DateAndTime < DateAdd(Day, DateDiff(Day, 0, GetDate()), 0) 

Delete
From	ftbl_analogs_daily
Where	DateAndTime >= DateAdd(Day, -1 + DateDiff(Day, 0, GetDate()), 0) 
		And DateAndTime < DateAdd(Day, DateDiff(Day, 0, GetDate()), 0) 

Insert 
Into	ftbl_analogs_hourly(DateAndTime, AverageValue)
Select  DateAdd(Hour, DateDiff(Hour, 0, DateAndTime), 0), Avg(Val)
From	ftbl_analogs
WHERE   TagIndex = '3'
        and val > 0 
		And DateAndTime >= DateAdd(Day, -1 + DateDiff(Day, 0, GetDate()), 0) 
		And DateAndTime < DateAdd(Day, DateDiff(Day, 0, GetDate()), 0) 
Group By DateAdd(Hour, DateDiff(Hour, 0, DateAndTime), 0) 


Insert 
Into	ftbl_analogs_daily(DateAndTime, AverageValue)
Select  DateAdd(Day, DateDiff(Day, 0, DateAndTime), 0), Avg(Val)
From	ftbl_analogs
WHERE   TagIndex = '3'
        and val > 0 
		And DateAndTime >= DateAdd(Day, -1 + DateDiff(Day, 0, GetDate()), 0) 
		And DateAndTime < DateAdd(Day, DateDiff(Day, 0, GetDate()), 0) 
Group By DateAdd(Day, DateDiff(Day, 0, DateAndTime), 0)

The code in the stored procedure will always process "Yesterday" and add it to the tables. I would suggest that you execute this stored procedure just after midnight every night. Please note that the stored procedure will always delete the data prior to inserting it. Under normal circumstances, there will be nothing to delete. However, if this procedure is accidentally run multiple times, you would get a primary key violation for those rows that already exist. So... we simply delete them prior to adding them to make sure everything is good.

Finally, you'll want to schedule this job. To do this, following the instructions here:




-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

gbaughma
  • Locked
  • Question
SQL Performance
Replies
11
Views
359
pjw001
pjw001
V
  • Locked
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
376
Volvere
V
dgillz
  • Locked
  • Question
How to convert varchar to time? 2
Replies
1
Views
458
Chris Miller
Chris Miller
coorsman
  • Locked
  • Question
Column Data Type. 2
Replies
9
Views
329
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
fill the gap of hierarchy data
Replies
11
Views
398
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top