December 8, 2009 at 4:35 am
Hi
I have Table "XXX" ,This table as two columns in it..
1.MM_DD_YYYY_HH -its varchar(50)
2. Data --its varchar(20)
|
MM_DD_YYYY_HH | Data
11/25/2009 00 | 1874
11/25/2009 01 | 2360
11/25/2009 02 | 2783
11/25/2009 03 | 2778
11/25/2009 04 | 2689
11/25/2009 05 | 2681
11/25/2009 06 | 2562
11/25/2009 07 | 2237
11/25/2009 08 | 2099
11/25/2009 09 | 1970
This table gets the data on hourly bases ,as you can see,For every hour on that particular day ,data is in inserted in to the table .Now From this table ,I need to write a query ,which gets me the "AVG" of the second column -"Data" for every 4 hours ...
thanks In advanced
jaya
December 8, 2009 at 4:42 am
Isnt it the same as,
http://www.sqlservercentral.com/Forums/Topic829346-149-1.aspx
I thought arun has answered it and u dint give any feedback on that thing there?
---------------------------------------------------------------------------------
December 15, 2009 at 12:06 am
DECLARE @tblHrTABLE
(
Date_Time VARCHAR(50),
DATA VARCHAR(100)
)
INSERT INTO @tblHr
SELECT
'11/25/2009 00' , '1874'
UNION
SELECT
'11/25/2009 01' , '2360'UNION
SELECT
'11/25/2009 02' , '2783'UNION
SELECT
'11/25/2009 03' , '2778'UNION
SELECT
'11/25/2009 04' , '2689'UNION
SELECT
'11/25/2009 05' , '2681'UNION
SELECT
'11/25/2009 06' , '2562'UNION
SELECT
'11/25/2009 07' , '2237'UNION
SELECT
'11/25/2009 08' , '2099'UNION
SELECT
'11/25/2009 09' , '1970'
SELECT
LEFT(DATE_TIME,10),
CAST(RIGHT(DATE_TIME,2) AS INT)/4,
SUM(CAST(DATA AS INT))
FROM
@tblHr
GROUP BY
LEFT(DATE_TIME,10),
CAST(RIGHT(DATE_TIME,2) AS INT)/4
Regards,
Mitesh OSwal
+918698619998
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply