Daily Average Query - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Daily Average Query

Author

Topic

robnjay
Starting Member

14 Posts

Posted - 2004-08-25 : 14:13:08

Gurus:

I hate to show my ignorance, but sometimes you just gotta do what you hafta do....

If I have a table like this:
TimeStamp Value
24-Aug-2004 08:00:12 21.2
24-Aug-2004 08:11:12 19.2
24-Aug-2004 09:14:11 21.6
24-Aug-2004 09:51:19 20.9
24-Aug-2004 11:39:15 22.0
24-Aug-2004 14:00:14 21.4
24-Aug-2004 15:05:18 24.7
24-Aug-2004 18:23:16 21.3
24-Aug-2004 18:40:35 20.2
25-Aug-2004 08:00:12 21.8
25-Aug-2004 08:23:24 23.1
25-Aug-2004 11:00:10 19.5
25-Aug-2004 13:12:04 20.2
25-Aug-2004 14:20:05 22.3
25-Aug-2004 17:32:18 20.4
25-Aug-2004 17:51:32 21.6
25-Aug-2004 23:08:52 23.8
25-Aug-2004 23:41:12 21.2

I want to write sql which will return a TimeStamp and Daily Average of Value.

How about a TimeStamp and an hourly average of value?

The "returned" timestamp would have to be adjusted:

Daily TimeStamp would be "24-Aug-2004 00:00:00" (for day beginning at 12:00 AM)

Hourly TimeStamp would be "24-Aug-2004 08:00:00" (for hour beginning at 0800 on the 24th)

TIA
Rob

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-25 : 14:50:57

Nobody?


USE Northwind
GO

CREATE TABLE myTAble99([TimeStamp] datetime,  Value decimal(3,1))
GO

INSERT INTO myTable99([TimeStamp], Value)
SELECT '24-Aug-2004 08:00:12', 21.2 UNION ALL
SELECT '24-Aug-2004 08:11:12', 19.2 UNION ALL
SELECT '24-Aug-2004 09:14:11', 21.6 UNION ALL
SELECT '24-Aug-2004 09:51:19', 20.9 UNION ALL
SELECT '24-Aug-2004 11:39:15', 22.0 UNION ALL
SELECT '24-Aug-2004 14:00:14', 21.4 UNION ALL
SELECT '24-Aug-2004 15:05:18', 24.7 UNION ALL
SELECT '24-Aug-2004 18:23:16', 21.3 UNION ALL
SELECT '24-Aug-2004 18:40:35', 20.2 UNION ALL
SELECT '25-Aug-2004 08:00:12', 21.8 UNION ALL
SELECT '25-Aug-2004 08:23:24', 23.1 UNION ALL
SELECT '25-Aug-2004 11:00:10', 19.5 UNION ALL
SELECT '25-Aug-2004 13:12:04', 20.2 UNION ALL
SELECT '25-Aug-2004 14:20:05', 22.3 UNION ALL
SELECT '25-Aug-2004 17:32:18', 20.4 UNION ALL
SELECT '25-Aug-2004 17:51:32', 21.6 UNION ALL
SELECT '25-Aug-2004 23:08:52', 23.8 UNION ALL
SELECT '25-Aug-2004 23:41:12', 21.2
GO

  SELECT    CONVERT(varchar(10),[Timestamp],101) AS Timestamp_DAY
	  , SUM(Value) AS SUM_Value
    FROM    myTable99
GROUP BY    CONVERT(varchar(10),[Timestamp],101)
GO

DROP TABLE myTable99
GO

You'll get answers faster if you supply DDL, sample data(in DML form) and expected results....

Brett

8-)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-25 : 15:00:53

for the by hour portion using Brett's DDL & DML


  SELECT    
	dateadd(hh,datepart(hh,[Timestamp]),CONVERT(varchar(10),[Timestamp],101)) AS Timestamp_Hour
	, SUM(Value) AS SUM_Value
    FROM    myTable99
GROUP BY    dateadd(hh,datepart(hh,[Timestamp]),CONVERT(varchar(10),[Timestamp],101))

Corey

robnjay
Starting Member

14 Posts

Posted - 2004-08-26 : 17:31:24

OK Guys:
Here's a better representation of the Raw data:


EH_DField1  EH_Timestamp
----------  --------------------
      1.23  25-MAY-04 14:52:12.0
      1.27  25-MAY-04 06:52:06.0
      1.30  24-MAY-04 22:52:09.0
      1.39  24-MAY-04 14:52:06.0
      1.28  24-MAY-04 06:52:06.0
      1.28  23-MAY-04 22:52:09.0
      1.54  23-MAY-04 14:52:09.0
      1.27  23-MAY-04 06:52:06.0
      1.55  22-MAY-04 22:52:09.0
      1.68  22-MAY-04 14:52:06.0
      1.40  22-MAY-04 06:52:09.0
      1.73  21-MAY-04 22:52:09.0
      1.41  21-MAY-04 14:52:09.0
      1.50  21-MAY-04 06:52:09.0
      1.52  20-MAY-04 22:52:09.0
      1.14  20-MAY-04 14:52:09.0
      1.57  20-MAY-04 06:52:06.0
      1.54  19-MAY-04 22:52:12.0
      1.44  19-MAY-04 14:52:12.0
      1.25  19-MAY-04 06:52:09.0
      1.58  18-MAY-04 22:52:06.0
      1.39  18-MAY-04 14:52:12.0
      1.22  18-MAY-04 06:52:06.0
      1.35  17-MAY-04 22:52:06.0
      1.10  17-MAY-04 14:52:09.0
      1.21  17-MAY-04 06:52:09.0
      1.26  16-MAY-04 22:52:09.0
      1.11  16-MAY-04 14:52:06.0
      1.34  16-MAY-04 06:52:09.0
      1.16  15-MAY-04 22:52:12.0
      1.18  15-MAY-04 14:52:09.0
      1.18  15-MAY-04 06:52:06.0
      0.73  14-MAY-04 22:52:09.0
      0.99  14-MAY-04 14:52:09.0
      1.45  14-MAY-04 06:52:06.0
      1.38  13-MAY-04 22:52:06.0
      1.42  13-MAY-04 14:52:06.0
      1.67  13-MAY-04 06:52:09.0
      2.89  12-MAY-04 22:52:09.0
      0.01  12-MAY-04 14:52:09.0
      1.03  12-MAY-04 06:52:06.0
      1.08  11-MAY-04 22:52:06.0
      0.96  11-MAY-04 14:52:09.0
      1.39  11-MAY-04 06:52:09.0
      1.56  10-MAY-04 22:52:09.0
      1.35  10-MAY-04 14:52:06.0
      1.54  10-MAY-04 06:52:09.0
      1.61  09-MAY-04 22:52:09.0
      1.70  09-MAY-04 14:52:09.0
      1.71  09-MAY-04 06:52:06.0

This is the Query I came up with to do Daily Averages:


--This is the Daily Average Query for 1-Value (EH_DField1) 
select 
   avg(Value) as TheData, 
   CAST(Cast (Timestamp AS CHAR FORMAT 'DD-MON-YY') as timestamp) as TheDate 
from 
   "1A1FLCFLehf" 
GROUP BY TheDate
order by TheDate DESC;

This Produces:


       TheData  TheDate
--------------  --------------------
       1.24837  25-MAY-04 00:00:00.0
        1.3201  24-MAY-04 00:00:00.0
         1.363  23-MAY-04 00:00:00.0
       1.54155  22-MAY-04 00:00:00.0
       1.54651  21-MAY-04 00:00:00.0
       1.41277  20-MAY-04 00:00:00.0
       1.41149  19-MAY-04 00:00:00.0
       1.39861  18-MAY-04 00:00:00.0
       1.22141  17-MAY-04 00:00:00.0
       1.23558  16-MAY-04 00:00:00.0
       1.17357  15-MAY-04 00:00:00.0
       1.05264  14-MAY-04 00:00:00.0
       1.48918  13-MAY-04 00:00:00.0
       1.30908  12-MAY-04 00:00:00.0
       1.14622  11-MAY-04 00:00:00.0
       1.48188  10-MAY-04 00:00:00.0
       1.67218  09-MAY-04 00:00:00.0

This produces Almost Exactly What I want (I want to return a daily average with a timestamp for the beginning of the day I am returning the average for)

BUT What if my "Start of the Day" is defined as 06:00 (AM - Which it is ...)

I Tried this:


select 
   Avg(EH_DField1) as TheData,
   CAST(Cast (EH_Timestamp AS CHAR FORMAT 'DD-MON-YY 06:00:00.0') as timestamp) as TheDate 
from 
   "1A1FLCFLehf"
Where
   EH_Timestamp BETWEEN (cast(cast(EH_Timestamp as char format 'DD-MON-YY 06:00:00') as timestamp)) and
                        (cast(cast((EH_Timestamp) as char format 'DD-MON-YY 06:00:00') as timestamp) + 24:00:00.0) 
GROUP BY
   TheDate
ORDER BY
   TheDate DESC;

The above works fine on the original table, however if there is data in the "1A1FLCFLehf" table with a timestamp between 00:00 and 06:00, it is not used in the average.

As you might have noticed the SQL I am using is not MSSQL....

Can anyone offer any further ehlp?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources