rego costcentername startdate enddate
AE70PV 51417 64702 2007-01-01 2007-09-17
AE70PV 51417 34504 2007-09-18 2100-01-01
AK11ZS 51417 34501 2007-01-01 2007-09-16
AK11ZS 51417 XX198 2007-09-17 2100-01-01
AK83HB 51417 66303 2007-02-27 2007-09-06
AK83HB 51417 34504 2007-09-07 2100-01-01
AM77KA 51417 34501 2007-01-01 2007-09-16
AM77KA 51417 XX198 2007-09-17 2100-01-01
AP32XJ 51417 27510 2007-06-28 2007-09-12
AP32XJ 51417 XX232 2007-09-13 2007-09-25
AP32XJ 51417 27510 2007-09-26 2100-01-01
AP98XI 51417 64302 2007-06-29 2007-09-17
AP98XI 51417 64702 2007-09-18 2100-01-01
AQ23AY 51417 XX254 2007-07-14 2007-09-11
AQ23AY 51417 27510 2007-09-12 2007-09-25
AQ23AY 51417 27503 2007-09-26 2100-01-01 这样一张表中如何通过sql语句把相同rego在指定时间段中所占天数多的数据提取出来
如:
(时间段为:2007.9.1~2007.9.30)
rego为:AE70PV, 有两条记录,一条在指定时间段占了16天,一条占了14天,只要把占16天的数据取出就可以了
AE70PV 51417 64702 2007-01-01 2007-09-17
AE70PV 51417 34504 2007-09-18 2100-01-01
AK11ZS 51417 34501 2007-01-01 2007-09-16
AK11ZS 51417 XX198 2007-09-17 2100-01-01
AK83HB 51417 66303 2007-02-27 2007-09-06
AK83HB 51417 34504 2007-09-07 2100-01-01
AM77KA 51417 34501 2007-01-01 2007-09-16
AM77KA 51417 XX198 2007-09-17 2100-01-01
AP32XJ 51417 27510 2007-06-28 2007-09-12
AP32XJ 51417 XX232 2007-09-13 2007-09-25
AP32XJ 51417 27510 2007-09-26 2100-01-01
AP98XI 51417 64302 2007-06-29 2007-09-17
AP98XI 51417 64702 2007-09-18 2100-01-01
AQ23AY 51417 XX254 2007-07-14 2007-09-11
AQ23AY 51417 27510 2007-09-12 2007-09-25
AQ23AY 51417 27503 2007-09-26 2100-01-01 这样一张表中如何通过sql语句把相同rego在指定时间段中所占天数多的数据提取出来
如:
(时间段为:2007.9.1~2007.9.30)
rego为:AE70PV, 有两条记录,一条在指定时间段占了16天,一条占了14天,只要把占16天的数据取出就可以了
解决方案 »
- 存储过程与触发器
- 求UPDATE语句.在线等,谢谢
- 写了一个支持SQL Server 2005的文档生成器
- 调整编码的隶属关系2,在线等答案
- [重开一贴]能否直接从数据库中输出增值税票上的零符号(圈中间一个叉)?
- 求一个简单的行列转换SQL语句,望哪位大哥大姐伸出援助之手
- 查找重复数据
- 求助:MSSQL 2000 占用资源大的解决办法??
- 问一个SQL server在windows XP下的安装问题!请大家来帮帮忙! 谢谢!
- 帮我该一下这句 select '1000'+id from table,目的是让'1000'和id=1000id,详细请进!
- windows的服务都有一个启动账户,这个启动账户有什么意义?sql server的启动账户又有什么意义?
- SQL2005中如何查看语句的执行时间?
这个帖子里面两位大哥找的是startdate和enddate的时间间隔,不是我指定的时间间隔。
我要的时间间隔是2007.9.1~2007.9.30
也就是
AE70PV 51417 64702 2007-01-01 2007-09-17
这条记录在指定的时间里占用了2007-09-01~2007-09-17, 为17天
AE70PV 51417 34504 2007-09-18 2100-01-01
这条记录在指定的时间里占用了2007-09-18~2007-09-30, 为13天
要取的是占用17天的那条记录
insert into @t values('AE70PV','51417 64702','2007-01-01','2007-09-17')
insert into @t values('AE70PV','51417 34504','2007-09-18','2100-01-01')
insert into @t values('AK11ZS','51417 34501','2007-01-01','2007-09-16')
insert into @t values('AK11ZS','51417 XX198','2007-09-17','2100-01-01')
insert into @t values('AK83HB','51417 66303','2007-02-27','2007-09-06')
insert into @t values('AK83HB','51417 34504','2007-09-07','2100-01-01')
insert into @t values('AM77KA','51417 34501','2007-01-01','2007-09-16')
insert into @t values('AM77KA','51417 XX198','2007-09-17','2100-01-01')
insert into @t values('AP32XJ','51417 27510','2007-06-28','2007-09-12')
insert into @t values('AP32XJ','51417 XX232','2007-09-13','2007-09-25')
insert into @t values('AP32XJ','51417 27510','2007-09-26','2100-01-01')
insert into @t values('AP98XI','51417 64302','2007-06-29','2007-09-17')
insert into @t values('AP98XI','51417 64702','2007-09-18','2100-01-01')
insert into @t values('AQ23AY','51417 XX254','2007-07-14','2007-09-11')
insert into @t values('AQ23AY','51417 27510','2007-09-12','2007-09-25')
insert into @t values('AQ23AY','51417 27503','2007-09-26','2100-01-01') declare @startdate datetime,@enddate datetime
set @startdate='2007-09-01'
set @enddate ='2007-09-30'
select
t.*
from
@t t
where
((t.startdate between @startdate and @enddate)
or
(t.enddate between @startdate and @enddate))
and
not exists(select
1
from
@t
where
rego=t.rego
and
((startdate between @startdate and @enddate)
or
(enddate between @startdate and @enddate))
and
datediff(dd,case when startdate>@startdate then startdate else @startdate end,
case when enddate <@enddate then enddate else @enddate end)
>
datediff(dd,case when t.startdate>@startdate then t.startdate else @startdate end,
case when t.enddate <@enddate then t.enddate else @enddate end))/*
rego costcentername startdate enddate
---------- -------------------- ---------- ----------
AE70PV 51417 64702 2007-01-01 2007-09-17
AK11ZS 51417 34501 2007-01-01 2007-09-16
AK83HB 51417 34504 2007-09-07 2100-01-01
AM77KA 51417 34501 2007-01-01 2007-09-16
AP32XJ 51417 XX232 2007-09-13 2007-09-25
AP98XI 51417 64302 2007-06-29 2007-09-17
AQ23AY 51417 27510 2007-09-12 2007-09-25
*/