Find the closest appointment time according to the specified time

Database environment: SQL SERVER 2008R2

There are user tables and appointment schedules as follows:

User table

Appointment Schedule

The following results are required

The following restrictions apply:

1. Have an appointment time greater than or equal to today, take the earliest date

2. No more than today's appointment time, take the past appointment time the most recent

3. No appointment time, the appointment time is displayed as null

First look at the topic, feel very simple, with the analysis of the function of the whole is OK. But when it comes to realizing it, it's not as simple as you think.

Using the analysis function to complete a moment, there is no good idea, the appointment schedule is temporarily divided into 2 parts, part of the appointment time is greater than or equal to today,

The other part is the appointment time is less than today. After sorting, use union ALL to relate.

1. Build the table and prepare the test data

CREATE TABLEUsers (IDINT, usernameVARCHAR(Ten))INSERT  intoUsers (Id,username)VALUES(1,'Zhang San')INSERT  intoUsers (Id,username)VALUES(2,'John Doe')INSERT  intoUsers (Id,username)VALUES(3,'Harry')CREATE TABLEAppointment (IDINTUseridINT, Ordertime DATE)INSERT  intoAppointment (Id,userid,ordertime)VALUES(1,1,'2015-07-30')INSERT  intoAppointment (Id,userid,ordertime)VALUES(2,1,'2015-07-23')INSERT  intoAppointment (Id,userid,ordertime)VALUES(3,2,'2015-07-26')INSERT  intoAppointment (Id,userid,ordertime)VALUES(4,1,'2015-07-31')INSERT  intoAppointment (Id,userid,ordertime)VALUES(5,2,'2015-07-21')

View Code

2.union All implementation

 withx0 as(/*greater than or equal to today's appointment time group to take the minimum value*/ SELECTUserID,MIN(ordertime) ordertime fromdbo.appointmentWHEREOrdertime>= GETDATE()                                     GROUP  byuseridUNION  All               /*smaller than today's appointment time group to take maximum value*/               SELECTUserID,MAX(ordertime) ordertime fromdbo.appointmentWHEREOrdertime< GETDATE()               GROUP  byuserid),/*after merging, there may be a customer with 2 appointment periods to group the result set to the maximum value*/X1 as(SELECTUserID,MAX(ordertime) ordertime fromx0GROUP  byuserid)SELECTU.username, P.ordertime fromUsers U Left JOINX1 P onP.userid=U.id

View Code

Then came back to examine the problem, found himself into a dead end inside. Actually, when comparing appointment time and today's size by case,

Then the group by group is used for the line.

3.case When+group by implementation

 withx0 as(SELECTUserID,MAX( Case  whenOrdertime< GETDATE() ThenOrdertimeEND) asMax_ordertime,--as early as today, the maximum number of time to take the appointment                        MIN( Case  whenOrdertime>= GETDATE() ThenOrdertimeEND) asMin_ordertime--appointment time is greater than or equal to today, take the minimum of appointment time                fromAppointmentGROUP  byuserid)SELECTA.username,ISNULL(B.min_ordertime, B.max_ordertime) asOrdertime fromusers a Left JOINX0 b onB.userid=a.ID

View Code

Go around, it's so simple!

Find the closest appointment time according to the specified time