View in sql - 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 2005 Forums

Transact-SQL (2005)

View in sql

Author

Topic

smitha
Posting Yak Master

100 Posts

Posted - 2009-12-02 : 06:30:22

Hi,
I have got 3 columns, date, time and number column(E1_ELE1) in a table. I am generating a view to get the average of that number column based on the time (ex. time from 1 Pm to 2 Pm)
I am using the following code in view

SELECT CONVERT(char(10), LOGDATE, 103) AS DATE1, LOGTIME, AVG(E1_ELE1) AS E1
FROM dbo.ELECTROLYSER1_MIN
GROUP BY LOGDATE, LOGTIME
HAVING (LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 13:00:00', 102) AND CONVERT(DATETIME, '1900-01-01 13:59:59', 102))

but I am getting the result as follows:
09/11/2009 1/1/1900 1:38:15 PM 85.000000
09/11/2009 1/1/1900 1:38:16 PM 85.000000
09/11/2009 1/1/1900 1:38:43 PM 85.000000
09/11/2009 1/1/1900 1:38:44 PM 85.000000

if you see in this the E1_ELE1 column is not averaged.

please help me in this

kbhere
Yak Posting Veteran

58 Posts

Posted - 2009-12-02 : 06:41:41

Here, you are grouping up the logtime..
So only, you are not getting the average..

For each and every log the timestamp differs in logtime..

Balaji.K

kbhere
Yak Posting Veteran

58 Posts

Posted - 2009-12-02 : 06:50:07

If your requirement is to get the average of that number column based on the time range means just re write the query as,

SELECT CONVERT(char(10), LOGDATE, 103) AS DATE1, AVG(E1_ELE1) AS E1
FROM dbo.ELECTROLYSER1_MIN
GROUP BY LOGDATE
HAVING (LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 13:00:00', 102) AND CONVERT(DATETIME, '1900-01-01 13:59:59', 102))

Balaji.K

smitha
Posting Yak Master

100 Posts

Posted - 2009-12-02 : 07:47:26

When I am goruping the timings and writing the code like this

SELECT CONVERT(char(10), LOGDATE, 103) AS DATE1, AVG(E1_ELE1) AS E1
FROM dbo.ELECTROLYSER1_MIN
WHERE (LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 06:00:00', 102) AND CONVERT(DATETIME, '1900-01-01 06:59:59', 102)) OR
(LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 07:00:00', 102) AND CONVERT(DATETIME, '1900-01-01 07:59:59', 102)) OR
(LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 08:00:00', 102) AND CONVERT(DATETIME, '1900-01-01 08:59:59', 102)) OR
(LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 09:00:00', 102) AND CONVERT(DATETIME, '1900-01-01 09:59:59', 102)) OR
(LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 10:00:00', 102) AND CONVERT(DATETIME, '1900-01-01 10:59:59', 102)) OR
(LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 11:00:00', 102) AND CONVERT(DATETIME, '1900-01-01 11:59:59', 102)) OR
(LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 12:00:00', 102) AND CONVERT(DATETIME, '1900-01-01 12:59:59', 102)) OR
(LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 13:00:00', 102) AND CONVERT(DATETIME, '1900-01-01 13:59:59', 102))
GROUP BY LOGDATE

I am getting the result as follows, but instead of grouping to time it is showing the average by grouping the date.
09/11/2009 85.000000
10/11/2009 88.333333

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-02 : 07:58:57

Try this

SELECT CONVERT(char(10), LOGDATE, 103) AS DATE1, dateadd(hour,datediff(hour,0,LOGTIME),0), AVG(E1_ELE1) AS E1
FROM dbo.ELECTROLYSER1_MIN
WHERE datepart(hour,LOGTIME) in 1 and 2
GROUP BY LOGDATE, dateadd(hour,datediff(hour,0,LOGTIME),0)

Madhivanan

Failing to plan is Planning to fail

kbhere
Yak Posting Veteran

58 Posts

Posted - 2009-12-02 : 08:05:19

What is your exact requirement??

If you want average of values within a specified time range for each day means..
Try this,

SELECT CONVERT(char(10), LOGDATE, 103) AS DATE1, AVG(E1_ELE1) AS E1
FROM dbo.ELECTROLYSER1_MIN
GROUP BY LOGDATE
HAVING (LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 13:00:00', 102) AND CONVERT(DATETIME, '1900-01-01 13:59:59', 102))

(OR)

If you want average of values within a specified time range for all the days..
Try this..

SELECT CONVERT(char(10), LOGDATE, 103) AS DATE1, AVG(E1_ELE1) AS E1
FROM dbo.ELECTROLYSER1_MIN
WHERE LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 13:00:00', 102) AND CONVERT(DATETIME, '1900-01-01 13:59:59', 102)

Balaji.K

smitha
Posting Yak Master

100 Posts

Posted - 2009-12-03 : 01:10:42

quote:
Originally posted by kbhere

What is your exact requirement??

If you want average of values within a specified time range for each day means..
Try this,

SELECT CONVERT(char(10), LOGDATE, 103) AS DATE1, AVG(E1_ELE1) AS E1
FROM dbo.ELECTROLYSER1_MIN
GROUP BY LOGDATE
HAVING (LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 13:00:00', 102) AND CONVERT(DATETIME, '1900-01-01 13:59:59', 102))

(OR)

If you want average of values within a specified time range for all the days..
Try this..

SELECT CONVERT(char(10), LOGDATE, 103) AS DATE1, AVG(E1_ELE1) AS E1
FROM dbo.ELECTROLYSER1_MIN
WHERE LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 13:00:00', 102) AND CONVERT(DATETIME, '1900-01-01 13:59:59', 102)

Balaji.K

I tried with both your results.
In the 1st case it is giving error stating that groupby LOGTIME is required.
In the 2nd case it is giving error stating that some expression has to be given. so when I gave group by expression for LOGDATE, I am getting the answer which is not correct.

smitha
Posting Yak Master

100 Posts

Posted - 2009-12-03 : 01:13:10

quote:
Originally posted by madhivanan

Try this

SELECT CONVERT(char(10), LOGDATE, 103) AS DATE1, dateadd(hour,datediff(hour,0,LOGTIME),0), AVG(E1_ELE1) AS E1
FROM dbo.ELECTROLYSER1_MIN
WHERE datepart(hour,LOGTIME) in 1 and 2
GROUP BY LOGDATE, dateadd(hour,datediff(hour,0,LOGTIME),0)

Madhivanan

Failing to plan is Planning to fail

I couldn't understand why you are using dateadd and datepart

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-03 : 02:15:52

quote:
Originally posted by smitha

quote:
Originally posted by madhivanan

Try this

SELECT CONVERT(char(10), LOGDATE, 103) AS DATE1, dateadd(hour,datediff(hour,0,LOGTIME),0), AVG(E1_ELE1) AS E1
FROM dbo.ELECTROLYSER1_MIN
WHERE datepart(hour,LOGTIME) in 1 and 2
GROUP BY LOGDATE, dateadd(hour,datediff(hour,0,LOGTIME),0)

Madhivanan

Failing to plan is Planning to fail

I couldn't understand why you are using dateadd and datepart

Did you get required result?

Madhivanan

Failing to plan is Planning to fail

smitha
Posting Yak Master

100 Posts

Posted - 2009-12-03 : 05:18:55

quote:
Originally posted by madhivanan

quote:
Originally posted by smitha

quote:
Originally posted by madhivanan

Try this

SELECT CONVERT(char(10), LOGDATE, 103) AS DATE1, dateadd(hour,datediff(hour,0,LOGTIME),0), AVG(E1_ELE1) AS E1
FROM dbo.ELECTROLYSER1_MIN
WHERE datepart(hour,LOGTIME) in 1 and 2
GROUP BY LOGDATE, dateadd(hour,datediff(hour,0,LOGTIME),0)

Madhivanan

Failing to plan is Planning to fail

I couldn't understand why you are using dateadd and datepart

Did you get required result?

Madhivanan

Failing to plan is Planning to fail

It is giving error as Incorrect syntax near '1'

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-03 : 05:35:16

Hi

Try like this...

SELECT CONVERT(CHAR(10), LOGDATE, 103) AS DATE1, DATEADD(HOUR,DATEDIFF(HOUR,0,LOGTIME),0), AVG(E1_ELE1) AS E1
FROM DBO.ELECTROLYSER1_MIN
WHERE DATEPART(HOUR,LOGTIME) IN (1,2)
GROUP BY LOGDATE, DATEADD(HOUR,DATEDIFF(HOUR,0,LOGTIME),0)

-------------------------
R...

smitha
Posting Yak Master

100 Posts

Posted - 2009-12-03 : 07:46:10

quote:
Originally posted by rajdaksha

Hi

Try like this...

SELECT CONVERT(CHAR(10), LOGDATE, 103) AS DATE1, DATEADD(HOUR,DATEDIFF(HOUR,0,LOGTIME),0), AVG(E1_ELE1) AS E1
FROM DBO.ELECTROLYSER1_MIN
WHERE DATEPART(HOUR,LOGTIME) IN (1,2)
GROUP BY LOGDATE, DATEADD(HOUR,DATEDIFF(HOUR,0,LOGTIME),0)

-------------------------
R...

Thanks I got the answer

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-03 : 07:58:56

quote:
Originally posted by smitha

quote:
Originally posted by rajdaksha

Hi

Try like this...

SELECT CONVERT(CHAR(10), LOGDATE, 103) AS DATE1, DATEADD(HOUR,DATEDIFF(HOUR,0,LOGTIME),0), AVG(E1_ELE1) AS E1
FROM DBO.ELECTROLYSER1_MIN
WHERE DATEPART(HOUR,LOGTIME) IN (1,2)
GROUP BY LOGDATE, DATEADD(HOUR,DATEDIFF(HOUR,0,LOGTIME),0)

-------------------------
R...

Thanks I got the answer

See, you must have found that by seeing the syntax
Now see why I used dateadd and datepart

Madhivanan

Failing to plan is Planning to fail

smitha
Posting Yak Master

100 Posts

Posted - 2009-12-04 : 00:19:50

quote:
Originally posted by madhivanan

quote:
Originally posted by smitha

quote:
Originally posted by rajdaksha

Hi

Try like this...

SELECT CONVERT(CHAR(10), LOGDATE, 103) AS DATE1, DATEADD(HOUR,DATEDIFF(HOUR,0,LOGTIME),0), AVG(E1_ELE1) AS E1
FROM DBO.ELECTROLYSER1_MIN
WHERE DATEPART(HOUR,LOGTIME) IN (1,2)
GROUP BY LOGDATE, DATEADD(HOUR,DATEDIFF(HOUR,0,LOGTIME),0)

-------------------------
R...

Thanks I got the answer

See, you must have found that by seeing the syntax
Now see why I used dateadd and datepart

Madhivanan

Failing to plan is Planning to fail

Thanks for the support

Smitha

smitha
Posting Yak Master

100 Posts

Posted - 2010-01-08 : 02:44:01

I am getting the results. The only problem with that is I am getting the time as 6:00:00 am instead of 7:00:00 am, for the average values between 6:00:00 am to 7:00:00 am.

Smitha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-08 : 02:47:59

quote:
Originally posted by smitha

I am getting the results. The only problem with that is I am getting the time as 6:00:00 am instead of 7:00:00 am, for the average values between 6:00:00 am to 7:00:00 am.

Smitha

what about this?


SELECT CONVERT(CHAR(10), LOGDATE, 103) AS DATE1, DATEADD(HOUR,DATEDIFF(HOUR,0,LOGTIME),0)+1, AVG(E1_ELE1) AS E1
FROM DBO.ELECTROLYSER1_MIN
WHERE DATEPART(HOUR,LOGTIME) IN (1,2)
GROUP BY LOGDATE, DATEADD(HOUR,DATEDIFF(HOUR,0,LOGTIME)+1,0)

smitha
Posting Yak Master

100 Posts

Posted - 2010-01-08 : 02:58:02

GROUP BY LOGDATE, DATEADD(HOUR,DATEDIFF(HOUR,0,LOGTIME)+1,0)
it is not taking 4 arguments. It is giving error as dateadd requires 3 arguments

quote:
Originally posted by visakh16

quote:
Originally posted by smitha

I am getting the results. The only problem with that is I am getting the time as 6:00:00 am instead of 7:00:00 am, for the average values between 6:00:00 am to 7:00:00 am.

Smitha

what about this?

SELECT CONVERT(CHAR(10), LOGDATE, 103) AS DATE1, DATEADD(HOUR,DATEDIFF(HOUR,0,LOGTIME),0)+1, AVG(E1_ELE1) AS E1
FROM DBO.ELECTROLYSER1_MIN
WHERE DATEPART(HOUR,LOGTIME) IN (1,2)
GROUP BY LOGDATE, DATEADD(HOUR,DATEDIFF(HOUR,0,LOGTIME)+1,0)

Smitha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-08 : 03:09:51

slight typo. it should have been


SELECT CONVERT(CHAR(10), LOGDATE, 103) AS DATE1, DATEADD(HOUR,DATEDIFF(HOUR,0,LOGTIME)+1,0), AVG(E1_ELE1) AS E1
FROM DBO.ELECTROLYSER1_MIN
WHERE DATEPART(HOUR,LOGTIME) IN (1,2)
GROUP BY LOGDATE, DATEADD(HOUR,DATEDIFF(HOUR,0,LOGTIME)+1,0)

smitha
Posting Yak Master

100 Posts

Posted - 2010-01-08 : 03:28:46

Thanks for the support

quote:

Originally posted by visakh16

slight typo. it should have been


SELECT CONVERT(CHAR(10), LOGDATE, 103) AS DATE1, DATEADD(HOUR,DATEDIFF(HOUR,0,LOGTIME)+1,0), AVG(E1_ELE1) AS E1
FROM DBO.ELECTROLYSER1_MIN
WHERE DATEPART(HOUR,LOGTIME) IN (1,2)
GROUP BY LOGDATE, DATEADD(HOUR,DATEDIFF(HOUR,0,LOGTIME)+1,0)

Smitha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-08 : 03:33:49

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources