求日期段算法！急！

[downmoon]

求日期段算法！急！
数据如下：
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均可！·

[happycoolsky]

点数的值是怎么得到的？

[singlepine]

select convert(varchar(16),strBgn,121)+'至'+convert(varchar(16),strEnd,121)+' 点数:'+convert(varchar(10),lngPoint) from tbl

[singlepine]

好像不对

[M_AREA]

用正则表达式

[astra1]

楼主的意思好象并不是拼字符串,而应该是计算吧

我用一个存储过程,假设你的表名为test

create proc getTestPoint
@startTime datetime,
@endTime datetime
as

select (case when sum(lngPoint) is not null then sum(lngPoint) else 0 end)
from test
where not @endTime<=strbgn
and not @startTime>=strEnd

go

exec 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

[astra1]

你只要sql语句的话可以自己改

[astra1]

我不知道你的数据是否有问题(可能有字段是null值),下面把sql贴完整(包括建表和数据),你可以测试一下


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[test]
GO

CREATE 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]
GO

insert 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]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE proc getTestPoint
@startTime datetime,
@endTime datetime
as

select (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
GO



exec 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'

[ksnoopy]

public static string CountPoint(string strBgn,string strEnd)
{
DateTime dt1 = Convert.ToDateTime(strBgn);
DateTime dt2 = Convert.ToDateTime(strEnd);
TimeSpan tsPoint = new TimeSpan();
tsPoint = dt2 - dt1;
return tsPoint.Hours.ToString();
}

[李洪喜]

使用SQL统计比较方便。
select datadiff(hh,getdate(),dt),count(*) as sl from table
goroup by datadiff(hh,getdate(),dt)

[szch]

点数是指什么?

或者是要对源数据2006-03-08 09:002006-03-08 14:305
进行分解?

[downmoon]

回复人：happycoolsky(学习专用帐号) (

回复人：szch(灏然)

点数就是源表中的lngPoint 某个区间段的累加

[downmoon]

回复人：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

[shoutor]

例子:

SELECT DATEDIFF(hh, '2006-3-8 08:12:12', GETDATE()) AS hours

[downmoon]

回复人：astra1(Hyperion) (

我的结果如下：
0
0
0
0
11
11
0
0

[downmoon]

解决了！　谢谢astra1(Hyperion)及各位朋友！