我如何评估这张表? [英] How can i evaluate this table?
本文介绍了我如何评估这张表?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要第二张桌子.
CREATE TABLE #Temp (VisitingCount int, [Time] int, [Date] nvarchar(50) )
DECLARE @DateNow DATETIME,@i int,@Time int, @Date nvarchar(50)
set @DateNow='00:00'
set @i=1;
while(@i<48)
begin
set @DateNow = DATEADD(minute, 30, @DateNow)
set @Time = (datepart(hour,@DateNow)*60+datepart(minute,@DateNow))/30
set @Date = CONVERT(VARCHAR(5),@DateNow, 108)
insert into #Temp(VisitingCount,[Time],[Date]) values(0,@Time,@Date )
set @i=@i+1
end
select * from #Temp
---------------------------------------------------------------------------
select Sum(VisitingCount) as VisitingCount, Date
from (
select Sum(VisitingCount) as VisitingCount, [Time],Date
from #Temp group by [Time],Date
Union All
select count(page) as VisitingCount,
(datepart(hour,Date)*60+datepart(minute,Date))/30 as [Time], CONVERT(VARCHAR(5),Date, 108) as Date
from scr_SecuristLog
where Date between '2009-05-12' and '2009-05-13'
GROUP BY (datepart(hour,Date)*60+datepart(minute,Date))/30,CONVERT(VARCHAR(5),Date, 108)
) X
group by Date
order by 2 asc
<代码>VCount 日期
0 10:00
0 10:30
0 11:00
0 11:30
1 11:36
1 11:53
0 12:00
1 12:04
1 12:07
1 12:11
1 12:16
0 12:30
0 13:00
0 13:30
0 14:00
1 14:13
1 14:17
2 14:23
2 14:24
1 14:25
<小时>
我需要这张桌子,请看下面:第二张桌子
VCount Date
0 10:00
0 10:30
0 11:00
0 11:30
1 11:36
1 11:53
0 12:00
1 12:04
1 12:07
1 12:11
1 12:16
0 12:30
0 13:00
0 13:30
0 14:00
1 14:13
1 14:17
2 14:23
2 14:24
1 14:25
i need this table look below please: Second Table
推荐答案
这里是一些示例代码,解释了如何使用 CASE 语句,您应该能够弄清楚如何在代码中进行更改
here is some sample code that explains how you can use a CASE statement, you should be able to figure out how to make the changes in your code
--sample data
create table #temp (SomeDate datetime)
insert #temp values ( '2009-05-12 11:13:19.667')
insert #temp values ( '2009-05-12 11:12:19.667')
insert #temp values ( '2009-05-12 11:33:19.667')
insert #temp values ( '2009-05-12 11:43:19.667')
insert #temp values ( '2009-05-12 11:03:19.667')
insert #temp values ( '2009-05-12 11:53:19.667')
insert #temp values ( '2009-05-12 11:53:19.667')
insert #temp values ( '2009-05-12 11:23:19.667')
insert #temp values ( '2009-05-12 12:13:19.667')
insert #temp values ( '2009-05-12 12:12:19.667')
insert #temp values ( '2009-05-12 13:33:19.667')
insert #temp values ( '2009-05-12 13:43:19.667')
insert #temp values ( '2009-05-12 14:03:19.667')
insert #temp values ( '2009-05-12 14:53:19.667')
insert #temp values ( '2009-05-12 15:53:19.667')
insert #temp values ( '2009-05-12 15:23:19.667')
--this is the grouping/count query
select count(*),case when datepart(mi,Somedate) < 30
then dateadd(hh, datediff(hh, 0, Somedate)+0, 0)
else dateadd(mi,30,dateadd(hh, datediff(hh, 0, Somedate)+0, 0)) end
from #temp
group by case when datepart(mi,Somedate) < 30
then dateadd(hh, datediff(hh, 0, Somedate)+0, 0)
else dateadd(mi,30,dateadd(hh, datediff(hh, 0, Somedate)+0, 0)) end
查看数据实际是什么样子
to see what the data actually looks like
select Somedate,case when datepart(mi,Somedate) < 30
then dateadd(hh, datediff(hh, 0, Somedate)+0, 0)
else dateadd(mi,30,dateadd(hh, datediff(hh, 0, Somedate)+0, 0)) end
from #temp
输出
vCount time
4 2009-05-12 11:00:00.000
4 2009-05-12 11:30:00.000
2 2009-05-12 12:00:00.000
2 2009-05-12 13:30:00.000
1 2009-05-12 14:00:00.000
1 2009-05-12 14:30:00.000
1 2009-05-12 15:00:00.000
1 2009-05-12 15:30:00.000
这篇关于我如何评估这张表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文