CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Penguen
Penguen

Reputation: 17298

Change table cells in sql but how?

i need Sql query Convert Table1 to Table2 

select Count(Page) as VisitingCount,CONVERT(VARCHAR(5),Date, 108) as [Time] from scr_SecuristLog     
where Date between '2009-04-30' and '2009-05-02'    
and [user] in(select USERNAME               
    from scr_CustomerAuthorities where customerID=Convert(varchar,4) and ID=Convert(varchar,43) )    
group by CONVERT(VARCHAR(5),Date, 108) order by CONVERT(VARCHAR(5),Date, 108) asc


Table1

VisitingCount Date
1-------------------15:09
3-------------------15:10
7-------------------15:15
1-------------------15:39
2-------------------15:40
3-------------------15:47


How can i change this table below table


Table2

VisitingCount Date
11-------------------15:00-15:30
6-------------------15:30-16:00

Upvotes: 0

Views: 183

Answers (2)

ahains
ahains

Reputation: 1912

See my post in this other thread - If statement SQL

You can use a table that defines the bounds that you want to bucket each count by. E.g. you would have a bound definition like ('15:01', '15:30', '15:00 - 15:30'). Then you just join your data table against this boundaries table and include the time bucket in your GROUP BY (as shown in the other thread).

Upvotes: 0

John M Gant
John M Gant

Reputation: 19308

Use a case statement to create a category, and then count by that category.

For (oversimplified) example.

select case when Date < '15:30' then '15:00 - 15:30'
            when Date < '16:00' then '15:30 - 16:00'
            else 'After 16:00' end as category
into #temp1
from Table1

select count(*) as VistingCount, category as Date
from #temp1
group by category

Upvotes: 1

Related Questions