Average value after 3 time intervals.

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

Average value after 3 time intervals.

Author

Topic

sorin3
Starting Member

5 Posts

Posted - 2009-07-14 : 11:22:18

I have a simple table with 3 fields: ID, Tag1, Data

ID Tag1 Data
________________________________
1 2 6/1/2009 22:00
2 1 6/1/2009 23:00
3 2 6/2/2009 6:00
4 3 6/2/2009 7:00
5 2 6/2/2009 8:00
6 2 6/2/2009 9:00
7 2 6/2/2009 10:00
8 1 6/2/2009 11:00
9 2 6/2/2009 11:30
10 1 6/2/2009 13:00
11 2 6/2/2009 14:00
12 2 6/2/2009 15:00
13 2 6/2/2009 16:00
14 1 6/2/2009 17:00
15 2 6/2/2009 18:00
16 1 6/2/2009 19:00
17 1 6/2/2009 20:00
18 2 6/2/2009 21:00
19 2 6/2/2009 22:00
20 3 6/2/2009 23:00
21 1 6/2/2009 23:59
22 2 6/3/2009 1:00
23 3 6/3/2009 2:00
24 2 6/3/2009 3:00
25 3 6/3/2009 4:00
26 2 6/3/2009 5:00
27 3 6/3/2009 6:00
28 2 6/3/2009 7:00
29 3 6/3/2009 8:00
30 2 6/3/2009 9:00
31 3 6/3/2009 10:00
32 2 6/3/2009 11:00
33 1 6/3/2009 11:55
34 2 6/3/2009 22:00
35 2 6/3/2009 23:00

Must I do a average AVG(Tag1) after 3 hourly intervals over a period of time.
Hourly intervals are:

10.30 PM - 6.30 AM, (represents schimbul1)
06.30 AM - 14.30 PM, (represents schimbul2)
14.30 PM - 22.30 PM, (represents schimbul3)

I tried this:

SELECT
AVG(CASE WHEN DATEPART(hour, DataTime) BETWEEN DATEPART(hour,'10:30:00 PM') AND DATEPART(hour,'06:30:00 AM') THEN Tag1 ELSE NULL END) AS Schimbul_1,
AVG(CASE WHEN DATEPART(hour, DataTime) BETWEEN DATEPART(hour,'6:30:00 AM') AND DATEPART(hour,'2:30:00 PM') THEN Tag1 ELSE NULL END) AS Schimbul_2,
AVG(CASE WHEN DATEPART(hour, DataTime) BETWEEN DATEPART(hour,'2:30:00 PM') AND DATEPART(hour,'10:30:00 PM') THEN Tag1 ELSE NULL END) AS Schimbul_3
FROM dbo.TableTest
WHERE (DataTime BETWEEN '6/1/2009 10:00:00 PM' AND '6/3/2009 11:00:00 PM')
GROUP BY DATEPART(DAY,DataTime)

The result looks like:

Schimbul_1 Schimbul_2 Schimbul_3
__________________________________________________________
NULL NULL 1.0
NULL 2.2222222222222223 1.7777777777777777
NULL 1.7142857142857142 2.0

Note:
For schimbul1 and schimbul2, belonging on the same day, is well calculated. The problem is schimbul1 - time which belongs to two days.
Can help me someone in this problem?
Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-14 : 19:25:15

[code]
Schimbul_1 = AVG(CASE WHEN DBO.F_TIME_FROM_DATETIME(DataTime) >= '22:30'
OR DBO.F_TIME_FROM_DATETIME(DataTime) < '06:30'
THEN Tag1 ELSE NULL END),
Schimbul_2 = AVG(CASE WHEN DBO.F_TIME_FROM_DATETIME(DataTime) >= '06:30'
OR DBO.F_TIME_FROM_DATETIME(DataTime) < '14:30'
THEN Tag1 ELSE NULL END),
Schimbul_3 = AVG(CASE WHEN DBO.F_TIME_FROM_DATETIME(DataTime) >= '14:30'
OR DBO.F_TIME_FROM_DATETIME(DataTime) < '22:30'
THEN Tag1 ELSE NULL END)

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65358&SearchTerms=F_TIME_FROM_DATETIME
[/code]

KH
[spoiler]Time is always against us[/spoiler]

sorin3
Starting Member

5 Posts

Posted - 2009-07-15 : 02:46:52

Thanks for answer.
I have tested your code.Unfortunately it is not calculated correctly.
The code...

SELECT
Schimbul_1 = AVG(CASE WHEN DBO.F_TIME_FROM_DATETIME(DataTime) >= '22:00'
OR DBO.F_TIME_FROM_DATETIME(DataTime) < '06:00'
THEN Tag1 ELSE NULL END)
FROM dbo.TableTest
WHERE (DataTime BETWEEN '6/1/2009 10:00:00 PM' AND '6/3/2009 11:00:00 PM')

return this:

Schimbul_1
-----------------
2.0833333333333335

...which is not the average value for Tag1 (time interval '22:00'-'06:00').

The code...

SELECT Tag1, DataTime
FROM dbo.TableTest
WHERE DBO.F_TIME_FROM_DATETIME(DataTime) >= '22:00' OR DBO.F_TIME_FROM_DATETIME(DataTime) < '06:00'

return this:
Tag1 DataTime
2.0 2009-06-01 22:00:00.000
1.0 2009-06-01 23:00:00.000
2.0 2009-06-02 22:00:00.000
3.0 2009-06-02 23:00:00.000
1.0 2009-06-02 23:59:00.000
2.0 2009-06-03 01:00:00.000
3.0 2009-06-03 02:00:00.000
2.0 2009-06-03 03:00:00.000
3.0 2009-06-03 04:00:00.000
2.0 2009-06-03 05:00:00.000
2.0 2009-06-03 22:00:00.000
2.0 2009-06-03 23:00:00.000

where AVG(Tag1) is not the average value for Tag1 (time interval '22:00'-'06:00').
I wrong somewhere?
Thanks again.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-15 : 03:17:55

[code]select (2.0 + 1.0 + 2.0 + 3.0 + 1.0 + 2.0 + 3.0 + 2.0 + 3.0 + 2.0 + 2.0 + 2.0) / 12[/code]

gives 2.083333

What's the average value for the above are you expecting ?

KH
[spoiler]Time is always against us[/spoiler]

sorin3
Starting Member

5 Posts

Posted - 2009-07-15 : 03:35:48

For time interval '22:00'-'06:00' must I have
select(2.0 + 1.0 + 2.0)/3 where AVG(Tag1)=1.666

for the above table (TableTest)...

SELECT AVG(Tag1) AS Tag1
FROM dbo.TableTest
WHERE (DataTime BETWEEN '6/1/2009 10:00:00 PM' AND '6/2/2009 6:00:00 AM')

Tag1
----

1.666

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-15 : 03:53:16

quote:
10.30 PM - 6.30 AM, (represents schimbul1)
06.30 AM - 14.30 PM, (represents schimbul2)
14.30 PM - 22.30 PM, (represents schimbul3)

for time 6:30 AM you want to include in schimbul1 or schimbul2 ?
or BOTH ?

Note in my query, i specify for schimbul1


   >= '22:30' 
OR <  '06:30'

which exclude it from schimbul1 and put 06:30 into schimbul2

KH
[spoiler]Time is always against us[/spoiler]

sorin3
Starting Member

5 Posts

Posted - 2009-07-15 : 04:48:39

The 3 time intervals are a working day.
So,
10.30 PM - 6.30 AM, represents tour 1 (alias schimbul1)
06.30 AM - 14.30 PM, represents tour 2 (alias schimbul2)
14.30 PM - 22.30 PM, represents tour 3 (alias schimbul3)

'10.30 PM - 6.30 AM'=8 hours
'06.30 AM - 14.30 PM'=8 hours
'14.30 PM - 22.30 PM'=8 hours
-----------------------------
Total = 24 hours - a working day.

Note: - tour 1 (alias schimbul1) - time which belongs to two days neighboring '10.30 PM - 6.30 AM'.
- yes, tour 1 must be >=22:30 and <=06:30
tour 2 must be >=06:30 and <=14:30
tour 3 must be >=14:30 and <=22:30
ORDER BY Data (for "TableTest" above)

My "SELECT" must to calculate the average for the three time intervals (tour 1, tour 2 and tour 3) where Data is the time interval selected by user. By example Data BETWEEN '6/1/2009 10:00:00 PM' AND '6/2/2009 6:00:00 AM'

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-15 : 05:05:38

quote:
Originally posted by sorin3

The 3 time intervals are a working day.
So,
10.30 PM - 6.30 AM, represents tour 1 (alias schimbul1)
06.30 AM - 14.30 PM, represents tour 2 (alias schimbul2)
14.30 PM - 22.30 PM, represents tour 3 (alias schimbul3)

'10.30 PM - 6.30 AM'=8 hours
'06.30 AM - 14.30 PM'=8 hours
'14.30 PM - 22.30 PM'=8 hours
-----------------------------
Total = 24 hours - a working day.

Note: - tour 1 (alias schimbul1) - time which belongs to two days neighboring '10.30 PM - 6.30 AM'.
- yes, tour 1 must be >=22:30 and <=06:30
tour 2 must be >=06:30 and <=14:30
tour 3 must be >=14:30 and <=22:30
ORDER BY Data (for "TableTest" above)

My "SELECT" must to calculate the average for the three time intervals (tour 1, tour 2 and tour 3) where Data is the time interval selected by user. By example Data BETWEEN '6/1/2009 10:00:00 PM' AND '6/2/2009 6:00:00 AM'

Thanks.

If you have a record with time 06:30 you want to consider it as tour1 or tour2 ? It does not make sense to consider under both tour1 and tour2. You will be double counting.

KH
[spoiler]Time is always against us[/spoiler]

sorin3
Starting Member

5 Posts

Posted - 2009-07-15 : 06:31:14

Ok, is not very important this aspect.
Must only to calculate corect.
Yes, we can consider:

tour 1 must be >= 22:30 and < 06:30
tour 2 must be >= 06:30 and < 14:30
tour 3 must be >= 14:30 and < 22:30

Thank you for your time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources