Question[SQL]：如何计算重叠的天数(未完成...)

未完成...
http://www.cnblogs.com/springwind/archive/2008/06/28/1231527.html
http://topic.csdn.net/t/20061123/14/5179712.html

     with  arg1  as
    (
         select  a. * ,
             -- A的开始时间在B区间内的数量
            ( select   COUNT ( 1 )  from  Orders  as  b  where  a.Positioncode = b.Positioncode  and
                  a.Startdate >= b.Startdate  and  a.Startdate <= b.Enddate)  as  Counts1,     
             -- A的结束时间在B区间内的数量
            ( select   COUNT ( 1 )  from  Orders  as  b  where  a.Positioncode = b.Positioncode  and  
                a.Enddate >= b.Startdate  and  a.Enddate <= b.Enddate)  as  Counts2 
             from  Orders  as  a
    )
     -- select * from arg1
     select  arg1.OrderID, arg1.Positioncode,
        ( case   when  arg1.Counts1 > arg1.Counts2  then  arg1.Counts1  else  arg1.Counts2  end )  as  Counts -- 哪个大以哪个
         from  arg1
             inner   join  Product  as  p
                 on  arg1.Positioncode  =  p.Positioncode  and   -- 取重叠数大于显示数的项
                    p.Showcount < ( case   when  arg1.Counts1 > arg1.Counts2  then  arg1.Counts1  else  arg1.Counts2  end )
         order   by  Positioncode

 

 

 

 

转载于:https://www.cnblogs.com/chenjunbiao/archive/2008/10/14/1760181.html