If you want to get the difference between the earliest time of the day and the number of the latest time, you can refer.
If you want to get the difference between the earliest time of the day and the number of the latest time, you can refer.
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
The 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
