query?

• samsun125

  SSC Enthusiast

  Points: 129

  More actions

  June 10, 2009 at 1:23 am

  #95659

  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.

• arun.sas

  SSChampion

  Points: 11831

  More actions

  June 10, 2009 at 3:26 am

  #1007245

  Hi,

  Don't edit am working with the old statement,

  however try this

  -- create table #temp1

  -- (

  -- datetime1 datetime,

  -- View1 int

  -- )

  -- insert into #temp1

  -- select '2009-06-01 00:00:00.000 ',1

  -- union all

  -- select '2009-06-01 02:00:00.000 ',1

  -- union all

  -- select '2009-06-01 22:00:00.000 ',1

  -- union all

  -- select '2009-06-02 03:00:00.000 ',1

  -- union all

  -- select '2009-06-02 06:00:00.000 ',1

  -- union all

  -- select '2009-06-02 09:00:00.000 ',1

  -- union all

  -- select '2009-06-03 04:00:00.000 ',1

  -- union all

  -- select '2009-06-03 08:00:00.000 ',1

  -- union all

  -- select '2009-06-03 12:00:00.000 ',1

  -- union all

  -- select '2009-06-04 05:00:00.000 ',1

  -- union all

  -- select '2009-06-04 10:00:00.000 ',1

  -- union all

  -- select '2009-06-04 15:00:00.000 ',1

  -- union all

  -- select '2009-06-05 06:00:00.000 ',1

  -- union all

  -- select '2009-06-05 13:00:00.000 ',1

  -- union all

  -- select '2009-06-05 21:00:00.000 ',1

  -- union all

  -- select '2009-06-06 07:00:00.000 ',1

  -- union all

  -- select '2009-06-06 14:00:00.000 ',1

  -- union all

  -- select '2009-06-06 23:00:00.000 ',1

  -- union all

  -- select '2009-06-07 00:00:00.000 ',1

  -- union all

  -- select '2009-06-08 00:00:00.000 ',1

  -- union all

  -- select '2009-06-09 00:00:00.000 ',1

  -- union all

  -- select '2009-06-10 00:00:00.000 ',1

  select GroupClass, sum(View1)total from

  (

  select (case when (DATEDIFF(hh,convert(varchar(12),datetime1,101), datetime1) ) between 0 and 6 then '[0-6]'

  when (DATEDIFF(hh,convert(varchar(12),datetime1,101), datetime1) ) between 7 and 12 then '[7-12]'

  when (DATEDIFF(hh,convert(varchar(12),datetime1,101), datetime1) ) between 13 and 18 then '[13-18]'

  when (DATEDIFF(hh,convert(varchar(12),datetime1,101), datetime1) ) between 19 and 23 then '[19-23]' end )GroupClass,

  View1 from #temp1 where datetime1 >(cast(cast(getdate() as int)as datetime)-7)

  ) as X

  group by GroupClass

  --convert(varchar(12),datetime1,101)ROUNDDATE

  --(DATEDIFF(hh,convert(varchar(12),datetime1,101), datetime1)) HOUR_SEPERATE

  --(cast(cast(getdate() as int)as datetime)-7) a week before

  ARUN SAS