Table structure data is as follows: (estimated time of sdate and actual time of 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
The statistical results are as follows: (compare the actual time and estimated time by two days)
Quasi-delivery quantity
...
6 days in advance, 1 day in advance
4 days in advance
2 days in advance
Day 1
2 days delayed
....
--> Test data: [TB]
If object_id ('[TB]') is not null drop table [TB]
Create Table [TB] ([ID] int, [sdate] datetime, [edate] datetime)
Insert [TB]
Select 1, '2014-01-01 ', '2014-01-03' Union all
Select 2, '2014-01-02 ', '2014-01-04' Union all
Select 3, '2014-01-04 ', '2014-01-02' Union all
Select 4, '2014-01-07 ', '2014-01-03' Union all
Select 5, '2017-01-08 ', '2017-01-08' Union all
Select 6, '2014-01-15 ', '2014-01-09' Union all
Select 7, '2017-01-23 ', '2017-01-20'
Select * from [TB]
Select ID, sdate, edate
From TB
/*
Quasi-delivery quantity
...
6 days in advance, 1 day in advance
4 days in advance
2 days in advance
Day 1
Delay 2 days 2 */
Select
[Quasi-delivery date] =
Case when datediff (DD, edate, sdate) <0 then 'latencies '+ convert (varchar (2), datediff (DD, edate, sdate) + 'day'
When datediff (DD, edate, sdate)> 0 then 'advance '+ convert (varchar (2), datediff (DD, edate, sdate) + 'day'
Else 'day' end,
[Quantity] = count (*)
From TB
Where datediff (DD, edate, sdate) % 2 = 0
Group by datediff (DD, edate, sdate)
/*
Quasi-delivery quantity
-------------------
Latency-2 days 2
Day 1
1 day in advance
4 days in advance
6 days in advance, 1 day in advance
(5 rows affected )*/