求日期段算法!急!
数据如下:
strBgn strEnd lngPoint
2006-03-08 09:00 2006-03-08 14:30 5
2006-03-08 14:00 2006-03-08 16:30 4
2006-03-09 09:00 2006-03-09 12:30 5
2006-03-09 09:00 2006-03-10 09:30 10
2006-03-15 09:00 2006-03-16 09:30 6希望得到如下结果:
2006-03-08 09:00至2006-03-08 14:00 点数:5
2006-03-08 14:00至2006-03-08 14:30 点数:9
2006-03-08 14:30至2006-03-08 16:30 点数:4
2006-03-08 16:30至2006-03-09 09:00 点数:0
2006-03-09 09:00至2006-03-09 12:30 点数:15
2006-03-09 12:30至2006-03-10 09:30 点数:10
2006-03-10 09:30至2006-03-15 09:00 点数:0
2006-03-15 09:00至2006-03-16 09:30 点数:6已得到区间段,希望得到点数的计算方法! 谢谢·!用SQL或asp.net均可!·
数据如下:
strBgn strEnd lngPoint
2006-03-08 09:00 2006-03-08 14:30 5
2006-03-08 14:00 2006-03-08 16:30 4
2006-03-09 09:00 2006-03-09 12:30 5
2006-03-09 09:00 2006-03-10 09:30 10
2006-03-15 09:00 2006-03-16 09:30 6希望得到如下结果:
2006-03-08 09:00至2006-03-08 14:00 点数:5
2006-03-08 14:00至2006-03-08 14:30 点数:9
2006-03-08 14:30至2006-03-08 16:30 点数:4
2006-03-08 16:30至2006-03-09 09:00 点数:0
2006-03-09 09:00至2006-03-09 12:30 点数:15
2006-03-09 12:30至2006-03-10 09:30 点数:10
2006-03-10 09:30至2006-03-15 09:00 点数:0
2006-03-15 09:00至2006-03-16 09:30 点数:6已得到区间段,希望得到点数的计算方法! 谢谢·!用SQL或asp.net均可!·
@startTime datetime,
@endTime datetime
asselect (case when sum(lngPoint) is not null then sum(lngPoint) else 0 end)
from test
where not @endTime<=strbgn
and not @startTime>=strEndgoexec getTestPoint '2006-03-08 09:00','2006-03-08 14:00'
exec getTestPoint '2006-03-08 14:00','2006-03-08 14:30'
exec getTestPoint '2006-03-08 14:30','2006-03-08 16:30'
exec getTestPoint '2006-03-08 16:30','2006-03-09 09:00'
exec getTestPoint '2006-03-09 09:00','2006-03-09 12:30'
exec getTestPoint '2006-03-09 12:30','2006-03-10 09:30'
exec getTestPoint '2006-03-10 09:30','2006-03-15 09:00'
exec getTestPoint '2006-03-15 09:00','2006-03-16 09:30'结果输出:5
9
4
0
15
10
0
6
{
DateTime dt1 = Convert.ToDateTime(strBgn);
DateTime dt2 = Convert.ToDateTime(strEnd);
TimeSpan tsPoint = new TimeSpan();
tsPoint = dt2 - dt1;
return tsPoint.Hours.ToString();
}
select datadiff(hh,getdate(),dt),count(*) as sl from table
goroup by datadiff(hh,getdate(),dt)
回复人:happycoolsky(学习专用帐号) (回复人:szch(灏然)数据如下:
strBgn strEnd lngPoint
2006-03-08 09:00 2006-03-08 14:30 5
2006-03-08 14:00 2006-03-08 16:30 4
2006-03-09 09:00 2006-03-09 12:30 5
2006-03-09 09:00 2006-03-10 09:30 10
2006-03-15 09:00 2006-03-16 09:30 6
0
0
0
0
11
11
0
0
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[test]
GOCREATE TABLE [dbo].[test] (
[strBgn] [datetime], --(50) COLLATE Chinese_PRC_CI_AS NULL ,
[strEnd] [datetime], --(50) COLLATE Chinese_PRC_CI_AS NULL ,
[lngPoint] [int] NULL
) ON [PRIMARY]
GOinsert into [test] select
'2006-03-08 09:00','2006-03-08 14:30',5
insert into [test] select
'2006-03-08 14:00','2006-03-08 16:30',4
insert into [test] select
'2006-03-09 09:00','2006-03-09 12:30',5
insert into [test] select
'2006-03-09 09:00','2006-03-10 09:30',10
insert into [test] select
'2006-03-15 09:00','2006-03-16 09:30',6
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getTestPoint]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[getTestPoint]
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOCREATE proc getTestPoint
@startTime datetime,
@endTime datetime
asselect (case when sum(lngPoint) is not null then sum(lngPoint) else 0 end)
from test
where not @endTime<=strbgn
and not @startTime>=strEnd
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOexec getTestPoint '2006-03-08 09:00','2006-03-08 14:00'
exec getTestPoint '2006-03-08 14:00','2006-03-08 14:30'
exec getTestPoint '2006-03-08 14:30','2006-03-08 16:30'
exec getTestPoint '2006-03-08 16:30','2006-03-09 09:00'
exec getTestPoint '2006-03-09 09:00','2006-03-09 12:30'
exec getTestPoint '2006-03-09 12:30','2006-03-10 09:30'
exec getTestPoint '2006-03-10 09:30','2006-03-15 09:00'
exec getTestPoint '2006-03-15 09:00','2006-03-16 09:30'