Tutorial :Change table cells in sql but how?



Question:

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


Solution:1

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  


Solution:2

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).


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »