在问怎么写一段sql？（子陌红尘，潇洒老乌龟两位大哥你们看错意思了）

在问如何写一段sql？（子陌红尘，潇洒老乌龟两位大哥你们看错意思了）
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天的数据取出就可以了

------解决方案--------------------

SQL code

declare @t table(rego varchar(10),costcentername varchar(20),startdate varchar(10),enddate varchar(10))
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
*/

在问怎么写一段sql？（子陌红尘，潇洒老乌龟两位大哥你们看错意思了）

相关推荐