Difference between the number of the latest date and the earliest date selected by date

The table structure is as follows:

Number date
8 2009/1/11
7 2009/1/11
6 2009/1/11 12: 00
5 2009/1/11
4 2009/1/12 4:00
3 2009/1/12 10: 00
2 2009/1/12 12:00
1 2009/1/11

The result is as follows:

Difference
2
3
Copy codeThe Code is as follows:
Create table # date
(
Number int identity (1, 1) primary key,
Date datetime
)
Insert into # date select '2014/1/11'
Insert into # date select '2014/1/12'
Insert into # date select '2014/1/12'
Insert into # date select '2014/1/12'
Insert into # date select '2014/1/11'
Insert into # date select '2014/1/11'
Insert into # date select '2014/1/11'
Insert into # date select '2014/1/11'

Select (d2.number-d1.number) number
From
(
Select number, date from # date where date in
(Select max (date) from # date group by convert (varchar (10), date, 120 ))
) D1
,
(
Select number, date from # date where date in
(Select min (date) from # date group by convert (varchar (10), date, 120 ))
) D2
Where converter (varchar (10), d1.date, 120) = convert (varchar (10), d2.date, 120)

Number
-----------
2
3