Find the closest appointment time according to the specified time

Source: Internet
Author: User

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
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. 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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.