declare @t table (tran_datetime datetime, viewss int)
insert into @t select '2009-05-28 00:00:00.000', 1
insert into @t select '2009-05-28 04:00:08.000', 2
insert into @t select '2009-06-29 06:12:08.000', 10 
insert into @t select '2009-06-28 11:00:08.000', 34
insert into @t select '2009-06-30 16:00:08.000', 20


select convert(varchar(32),cast(t.date as datetime),108) +'-'+ convert(varchar(32),cast(t.date1 as datetime),108)  as date
 ,sum( viewss ) as sumviews from
(select CAST(convert(varchar(32),tran_datetime,108) AS DATETIME) as date,viewss from @t)s 
inner join 
(select '1/1/1900 0:00' as date,'1/1/1900 4:00' as date1 UNION ALL
select '1/1/1900 4:00','1/1/1900 8:00' UNION ALL
select '1/1/1900 8:00','1/1/1900 12:00' UNION ALL
select '1/1/1900 12:00','1/1/1900 16:00' UNION ALL
select '1/1/1900 16:00','1/1/1900 20:00') t on s.date between t.date and t.date1
group by t.date,t.date1

quote:

---

Originally posted by samsun125

Hi all,
i have one query,

My table name is tbl_test,columns are tran_datetime,views


jun 1st from 12.00am to 4.00am views=20
jun 2nd from 12.00am to 4.00am views=20
upto jun 7th 12.00am to 4.00am views=20

result is:
time sumof(views)
12.00am to 4.00am 140(7*20)

how to write query in sql server

If anybody knows please let me know.

Thanks & Regards
Rama.

---