Change table cells in sql, but how?
I need a Sql query Convert table 1 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
Table 1
VisitingCount Date1 ------------------- 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 Date11 ------------------- 15: 00-15: 30
6 ------------------- 15 : 30-16: 00
0
source to share
2 answers
Use a case statement to create a category and then count that category.
For an example (simplified).
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
+1
source to share
See my post on this other topic - If the SQL statement
You can use a table that defines the boundaries you want to use for each count. For instance. you have an associated type definition ('15: 01 ', '15: 30', '15: 00 - 15:30 '). Then you just join the data table with that bounds table and include the time bucket in the GROUP BY (as shown in another thread).
0
source to share