根據兩日期字段生成兩日期間隔天數字段

表結構資料如下：(sdate預計時間，edate實際時間)

id  sdate        edate

1   2010-01-01   2010-01-03

2   2010-01-02   2010-01-04

3   2010-01-04   2010-01-02

4   2010-01-07   2010-01-03

5   2010-01-08   2010-01-08

6   2010-01-15   2010-01-09

7   2010-01-23   2010-01-20

統計結果如下：（按2天的來進行統計,實際時間和預計時間來進行比較）

準交期       數量

...

提前6天       1  

提前4天       1

提前2天       2

當天          1

延遲2天       2

....

--> 測試資料：[TB]

if object_id('[TB]') is not null drop table [TB]

create table [TB]([id] int,[sdate] datetime,[edate] datetime)

insert [TB]

select 1,'2010-01-01','2010-01-03' union all

select 2,'2010-01-02','2010-01-04' union all

select 3,'2010-01-04','2010-01-02' union all

select 4,'2010-01-07','2010-01-03' union all

select 5,'2010-01-08','2010-01-08' union all

select 6,'2010-01-15','2010-01-09' union all

select 7,'2010-01-23','2010-01-20'

select * from [TB]

select id,sdate,edate

from tb

select

[準交期]=

case when datediff(dd,edate,sdate) < 0 then '延遲' +convert(varchar(2),datediff(dd,edate,sdate)) +'天'

     when datediff(dd,edate,sdate) > 0 then '提前' +convert(varchar(2),datediff(dd,edate,sdate)) +'天'

     else '當天' end ,

[數量] = count(*)

from tb

where datediff(dd,edate,sdate)%2 = 0

group by datediff(dd,edate,sdate)