How to use FIRST_VALUE and LAST_VALUE Analytical Functions?

Problem: How to use FIRST_VALUE and LAST_VALUE Analytical Functions?

Solution: Analytical Functions are introduced in SQL Server 2012, Where the table rows can be compared without using self joins and cte’s.

The following two functions are used to get the first value of a partition and last value of a subset of data (partitions based on the order by clause.)

FIRST_VALUE (Transact-SQL) – used to get the first value of a subset of rows.

Syntax:

FIRST_VALUE ( [scalar_expression ] ) 

    OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )

LAST_VALUE (Transact-SQL) – used for getting the last value of a subset rows.

Syntax:

LAST_VALUE ( [scalar_expression ] )

    OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )

This Function comes handy when there are certain queries where we need to find the bottom to top differences and moving dates calculations.

Example:

In this example, I would like to see a schedule of appointments for each day what is his first appointment and last appointment on that day. So, with this information we can see if he his free in the intervals or plan accordingly.

--SQL SERVER 2012 version

IF EXISTS ( SELECT *

      FROM sys.tables

      WHERE name = 'Doctorappointment'

                  and SCHEMA_NAME(schema_id)='dbo')

   DROP TABLE dbo.Doctorappointment;

  GO

CREATE TABLE dbo.Doctorappointment

   (

   AppointmentID int IDENTITY(1,1) PRIMARY KEY,

                PatientID INT,

    AppointmentDate  DATE ,

    AppointmentTime TIME,

                TypeofAppointment VARCHAR(256)

   );

 GO

INSERT INTO dbo.Doctorappointment

   (PatientID, AppointmentDate, AppointmentTime,TypeofAppointment )

VALUES

   ( 4464, '2017-01-07', '08:00','Phyiscal Checkup'),

   ( 79879, '2017-01-07', '08:10', 'consulting'),

   (1231 , '2017-01-07', '11:30','Phyiscal Checkup'),

   (1124 , '2017-02-07', '11:35','dental Checkup'),

   (16546 , '2017-02-07', '12:45','Follow up Visit'),

   ( 4646, '2017-02-07', '16:45','General Checkup'),

   (1124 , '2017-03-07', '17:30','Follow up Visit'),

   (79879 , '2017-03-07', '1:00','General Checkup'),

   ( 7987, '2017-04-07', '08:33','General Checkup'),

   (789 , '2017-04-07', '17:33','General Checkup'),

   (3366 , '2017-04-07', '17:35','General Checkup');

GO

-- This will show a patient's first and last appointment for the that date side by side.

   SELECT Patientid ,

       TypeofAppointment ,

       CAST(AppointmentDate AS DATETIME) + CAST(AppointmentTime AS DATETIME) AS AppointmentDateTime ,

       FIRST_VALUE(CAST(AppointmentDate AS DATETIME) + CAST(AppointmentTime AS DATETIME)) OVER (PARTITION BY AppointmentDate  ORDER BY AppointmentDate  ) AS [First Appointment of the Day ],

       LAST_VALUE(CAST(AppointmentDate AS DATETIME) + CAST(AppointmentTime AS DATETIME)) OVER (PARTITION BY AppointmentDate  ORDER BY AppointmentDate  ) AS [Last Appointment of the Day]

FROM dbo.Doctorappointment;

GO

Patientid	TypeofAppointment	EventDateTime	First Appointment of the Day	Last Appointment of the Day
4464	Phyiscal Checkup	2017-01-07 08:00:00.000	2017-01-07 08:00:00.000	2017-01-07 11:30:00.000
79879	consulting	2017-01-07 08:10:00.000	2017-01-07 08:00:00.000	2017-01-07 11:30:00.000
1231	Phyiscal Checkup	2017-01-07 11:30:00.000	2017-01-07 08:00:00.000	2017-01-07 11:30:00.000
1124	dental Checkup	2017-02-07 11:35:00.000	2017-02-07 11:35:00.000	2017-02-07 16:45:00.000
16546	Follow up Visit	2017-02-07 12:45:00.000	2017-02-07 11:35:00.000	2017-02-07 16:45:00.000
4646	General Checkup	2017-02-07 16:45:00.000	2017-02-07 11:35:00.000	2017-02-07 16:45:00.000
1124	Follow up Visit	2017-03-07 17:30:00.000	2017-03-07 17:30:00.000	2017-03-07 01:00:00.000
79879	General Checkup	2017-03-07 01:00:00.000	2017-03-07 17:30:00.000	2017-03-07 01:00:00.000
7987	General Checkup	2017-04-07 08:33:00.000	2017-04-07 08:33:00.000	2017-04-07 17:35:00.000
789	General Checkup	2017-04-07 17:33:00.000	2017-04-07 08:33:00.000	2017-04-07 17:35:00.000
3366	General Checkup	2017-04-07 17:35:00.000	2017-04-07 08:33:00.000	2017-04-07 17:35:00.000