June 1, 2005 at 12:55 pm
My query is like this..
Select convert(varchar(11),date_Time,101),datepart(hh,date_time) + (DATEPART(minute,date_time) /05 ) *.05 as hours ,avg(convert(float,column_name))
from table_name where date_time between '05/22/2005' and '05/24/2005'
and datepart(hh,date_time) between 07 and 16
group by convert(varchar(11),date_Time,101),datepart(hh,date_time) + (DATEPART(minute, date_time) /05 ) * .05
I will get results like this
Datec Hours Col1
05/22/2005 7.00 5.4781198325968514
05/22/2005 7.05 5.1944400112529738
05/22/2005 7.10 7.6891518804731573
05/22/2005 7.15 6.0407670954529467
-------
------------------------
-----------------
----------------
---------------
05/23/2005 16.40 19.58330250098663
05/23/2005 16.45 31.230852407728158
05/23/2005 16.50 17.385456165402708
05/23/2005 16.55 14.258523822451002
Now i want my data for 05/23 to appear in a different column like this...
05/22/2005 7.00 5.4781198325968514 19.58330250098663
05/22/2005 7.05 5.1944400112529738 31.230852407728158
05/22/2005 7.10 7.6891518804731573 17.385456165402708
05/22/2005 7.15 6.0407670954529467 14.258523822451002
I am not much worried about the datecolumn and the hours columns.If i could
get the data like displayed above that would be greatly appreciated.
Is this possible..If so can anybody help me with this.
Thanks In Advance
June 1, 2005 at 1:53 pm
Do this at the client.
I can't think of a solution for this right now, since you can't GROUP BY a common criteria here. However, your SELECT statement might be simplified to
SELECT
DATEADD(minute, -DATEPART(minute, date_column) % 5, date_column)
, AVG(float_column)
FROM
table
WHERE ...
GROUP BY
DATEADD(minute, -DATEPART(minute, date_column) % 5, date_column),
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply