How to access the previous or the next row without using any cursor in MS SQL Server
- The standard way of doing row operations is using cursors.
- SQL is not designed to deal with rows, but is optimized to deal with sets.
- Depending on the cursor operations, sets operation are from thousand times fast to million of times faster than cursor operations.
Standard way of accessing the previous row
- Declare a few variables to keep track of the info needed.
- Declare a cursor.
- Open the cursor.
- Get a set of data.
- Loop trough the data, copying the data to the variables declared in [1].
- Perform whatever operation they need.
- Close the cursor.
- Almost all cursor operations a completely unnecessary.
- Cursor operations impose an undue burden on the SQL Server.
Data used
These are the sales for each employee with the date and the quantities:
USE sql911;
go
SELECT emp_short, sales_date, sales_qty
FROM sales
ORDER BY emp_short, sales_date;
go
SELECT emp_short, sales_date, sales_qty
FROM sales
ORDER BY emp_short, sales_date;
emp_short sales_date sales_qty ---------- ----------------------- --------------------------------------- 000010 2005-04-06 15:01:01.000 4.00 000010 2005-11-06 10:01:01.000 9.00 000010 2005-12-06 11:01:01.000 8.00 000010 2006-04-06 15:01:01.000 4.00 000010 2006-05-06 16:01:01.000 3.00 000010 2006-07-06 19:01:01.000 1.00 000010 2007-04-06 00:00:00.000 4.00 000010 2007-06-06 17:01:01.000 2.00 000030 2005-10-06 10:01:01.000 10.00 000030 2006-01-06 12:01:01.000 7.00 000030 2006-04-06 15:01:01.000 4.00 000030 2006-06-06 17:01:01.000 2.00 000030 2006-10-06 10:01:01.000 10.00 000030 2007-05-01 00:00:00.000 5.00 000030 2007-11-06 10:01:01.000 9.00 000110 2004-03-06 14:01:01.000 5.00 000110 2005-11-06 10:01:01.000 9.00 000110 2006-02-06 13:01:01.000 6.00 000110 2006-03-06 14:01:01.000 5.00 000110 2006-06-06 17:01:01.000 2.00 000110 2006-10-06 10:01:01.000 10.00 000110 2006-10-06 10:01:01.000 3.00 000190 2005-03-06 14:01:01.000 5.00 000190 2005-05-06 16:01:01.000 3.00 000190 2005-12-06 11:01:01.000 8.00 000190 2006-02-06 13:01:01.000 6.00 000190 2006-06-06 17:01:01.000 2.00 000190 2006-07-06 00:00:00.000 1.00 000190 2006-07-06 19:01:01.000 1.00 000190 2006-10-06 10:01:01.000 10.00 000190 2006-10-06 10:01:01.000 10.00 000190 2007-07-06 00:00:00.000 1.00 000310 2004-10-06 10:01:01.000 10.00 000310 2005-10-06 10:01:01.000 10.00 000310 2006-01-06 12:01:01.000 7.00 000310 2006-03-06 14:01:01.000 5.00 000310 2006-07-06 19:01:01.000 1.00 000310 2006-10-06 10:01:01.000 10.00 000310 2006-10-06 10:01:01.000 10.00 (39 row(s) affected)
Accessing the next row with "straight" SQL, without using any cursor
- It's actually very simple, bu just doing and subquery, and eliminating and the redundant rows.
SELECT emp_short, sales_date,
DATEDIFF(DAY,sales_date,
(SELECT MIN(sales_date) FROM sales AS B
WHERE B.sales_date > A.sales_date and A.emp_short = B. emp_short
)) AS 'Days to next sale'
FROM sales AS A
ORDER BY emp_short, sales_date;
go
DATEDIFF(DAY,sales_date,
(SELECT MIN(sales_date) FROM sales AS B
WHERE B.sales_date > A.sales_date and A.emp_short = B. emp_short
)) AS 'Days to next sale'
FROM sales AS A
ORDER BY emp_short, sales_date;
go
emp_short sales_date Days to next sale ---------- ----------------------- ----------------- 000010 2005-04-06 15:01:01.000 214 000010 2005-11-06 10:01:01.000 30 000010 2005-12-06 11:01:01.000 121 000010 2006-04-06 15:01:01.000 30 000010 2006-05-06 16:01:01.000 61 000010 2006-07-06 19:01:01.000 274 000010 2007-04-06 00:00:00.000 61 000010 2007-06-06 17:01:01.000 NULL 000030 2005-10-06 10:01:01.000 92 000030 2006-01-06 12:01:01.000 90 000030 2006-04-06 15:01:01.000 61 000030 2006-06-06 17:01:01.000 122 000030 2006-10-06 10:01:01.000 207 000030 2007-05-01 00:00:00.000 189 000030 2007-11-06 10:01:01.000 NULL 000110 2004-03-06 14:01:01.000 610 000110 2005-11-06 10:01:01.000 92 000110 2006-02-06 13:01:01.000 28 000110 2006-03-06 14:01:01.000 92 000110 2006-06-06 17:01:01.000 122 000110 2006-10-06 10:01:01.000 NULL 000110 2006-10-06 10:01:01.000 NULL 000190 2005-03-06 14:01:01.000 61 000190 2005-05-06 16:01:01.000 214 000190 2005-12-06 11:01:01.000 62 000190 2006-02-06 13:01:01.000 120 000190 2006-06-06 17:01:01.000 30 000190 2006-07-06 00:00:00.000 0 000190 2006-07-06 19:01:01.000 92 000190 2006-10-06 10:01:01.000 273 000190 2006-10-06 10:01:01.000 273 000190 2007-07-06 00:00:00.000 NULL 000310 2004-10-06 10:01:01.000 365 000310 2005-10-06 10:01:01.000 92 000310 2006-01-06 12:01:01.000 59 000310 2006-03-06 14:01:01.000 122 000310 2006-07-06 19:01:01.000 92 000310 2006-10-06 10:01:01.000 NULL 000310 2006-10-06 10:01:01.000 NULL (39 row(s) affected)
- We link twice to the sales table:
- First as A: the regular select ... from
- Second as B: Find all the dates that are greater than the one we want [THE >] AND get the first one [THE MIN].
- We get a NULL for employee 000310, when the employee is the same and the date is the same. The subquery cannot find a next row, it is the next one is the same date.
- It's because it not really the next row but a row defined as the next date.
- Notice that the last 2 rows of 000310 have the exact same date and time. Check your data.
Accessing the previous row with "straight" SQL, without using any cursor
It's almost identical to accessing the next row, but the condition is the opposite.
SELECT emp_short, sales_date,
DATEDIFF(DAY,
(SELECT MAX(sales_date) FROM sales AS B
WHERE B.sales_date < A.sales_date and A.emp_short = B. emp_short
),sales_date) AS 'Days from prev sale'
FROM sales AS A
ORDER BY emp_short, sales_date;
DATEDIFF(DAY,
(SELECT MAX(sales_date) FROM sales AS B
WHERE B.sales_date < A.sales_date and A.emp_short = B. emp_short
),sales_date) AS 'Days from prev sale'
FROM sales AS A
ORDER BY emp_short, sales_date;
emp_short sales_date Days from prev sale ---------- ----------------------- ------------------- 000010 2005-04-06 15:01:01.000 NULL 000010 2005-11-06 10:01:01.000 214 000010 2005-12-06 11:01:01.000 30 000010 2006-04-06 15:01:01.000 121 000010 2006-05-06 16:01:01.000 30 000010 2006-07-06 19:01:01.000 61 000010 2007-04-06 00:00:00.000 274 000010 2007-06-06 17:01:01.000 61 000030 2005-10-06 10:01:01.000 NULL 000030 2006-01-06 12:01:01.000 92 000030 2006-04-06 15:01:01.000 90 000030 2006-06-06 17:01:01.000 61 000030 2006-10-06 10:01:01.000 122 000030 2007-05-01 00:00:00.000 207 000030 2007-11-06 10:01:01.000 189 000110 2004-03-06 14:01:01.000 NULL 000110 2005-11-06 10:01:01.000 610 000110 2006-02-06 13:01:01.000 92 000110 2006-03-06 14:01:01.000 28 000110 2006-06-06 17:01:01.000 92 000110 2006-10-06 10:01:01.000 122 000110 2006-10-06 10:01:01.000 122 000190 2005-03-06 14:01:01.000 NULL 000190 2005-05-06 16:01:01.000 61 000190 2005-12-06 11:01:01.000 214 000190 2006-02-06 13:01:01.000 62 000190 2006-06-06 17:01:01.000 120 000190 2006-07-06 00:00:00.000 30 000190 2006-07-06 19:01:01.000 0 000190 2006-10-06 10:01:01.000 92 000190 2006-10-06 10:01:01.000 92 000190 2007-07-06 00:00:00.000 273 000310 2004-10-06 10:01:01.000 NULL 000310 2005-10-06 10:01:01.000 365 000310 2006-01-06 12:01:01.000 92 000310 2006-03-06 14:01:01.000 59 000310 2006-07-06 19:01:01.000 122 000310 2006-10-06 10:01:01.000 92 000310 2006-10-06 10:01:01.000 92 (39 row(s) affected)
- We link twice to the sales table:
- First as A: the regular select ... from
- Second as B: Find all the dates that are less than the one we want [THE <] AND get the last one [THE MAX].
- We get the same number of days for the last 2 rows of the data for employee 000310.
- It's because it not really the previous row, but a row defined as the previous date.
- Notice that the last 2 rows of 000310 have the exact same date and time. Check your data.
Accessing the previous row and the next row with "straight" SQL, without using any cursor
- We can combine both at the same time to get both the previous and the next row.
SELECT emp_short, sales_date,
DATEDIFF(DAY,sales_date,
(SELECT MIN(sales_date) FROM sales AS B
WHERE B.sales_date > A.sales_date and A.emp_short = B. emp_short
)) AS 'Days to next sale',
DATEDIFF(DAY,
(SELECT MAX(sales_date) FROM sales AS B
WHERE B.sales_date < A.sales_date and A.emp_short = B. emp_short
),sales_date) AS 'Days from prev sale'
FROM sales AS A
ORDER BY emp_short, sales_date;
DATEDIFF(DAY,sales_date,
(SELECT MIN(sales_date) FROM sales AS B
WHERE B.sales_date > A.sales_date and A.emp_short = B. emp_short
)) AS 'Days to next sale',
DATEDIFF(DAY,
(SELECT MAX(sales_date) FROM sales AS B
WHERE B.sales_date < A.sales_date and A.emp_short = B. emp_short
),sales_date) AS 'Days from prev sale'
FROM sales AS A
ORDER BY emp_short, sales_date;
emp_short sales_date Days to next sale Days from prev sale ---------- ----------------------- ----------------- ------------------- 000010 2005-04-06 15:01:01.000 214 NULL 000010 2005-11-06 10:01:01.000 30 214 000010 2005-12-06 11:01:01.000 121 30 000010 2006-04-06 15:01:01.000 30 121 000010 2006-05-06 16:01:01.000 61 30 000010 2006-07-06 19:01:01.000 274 61 000010 2007-04-06 00:00:00.000 61 274 000010 2007-06-06 17:01:01.000 NULL 61 000030 2005-10-06 10:01:01.000 92 NULL 000030 2006-01-06 12:01:01.000 90 92 000030 2006-04-06 15:01:01.000 61 90 000030 2006-06-06 17:01:01.000 122 61 000030 2006-10-06 10:01:01.000 207 122 000030 2007-05-01 00:00:00.000 189 207 000030 2007-11-06 10:01:01.000 NULL 189 000110 2004-03-06 14:01:01.000 610 NULL 000110 2005-11-06 10:01:01.000 92 610 000110 2006-02-06 13:01:01.000 28 92 000110 2006-03-06 14:01:01.000 92 28 000110 2006-06-06 17:01:01.000 122 92 000110 2006-10-06 10:01:01.000 NULL 122 000110 2006-10-06 10:01:01.000 NULL 122 000190 2005-03-06 14:01:01.000 61 NULL 000190 2005-05-06 16:01:01.000 214 61 000190 2005-12-06 11:01:01.000 62 214 000190 2006-02-06 13:01:01.000 120 62 000190 2006-06-06 17:01:01.000 30 120 000190 2006-07-06 00:00:00.000 0 30 000190 2006-07-06 19:01:01.000 92 0 000190 2006-10-06 10:01:01.000 273 92 000190 2006-10-06 10:01:01.000 273 92 000190 2007-07-06 00:00:00.000 NULL 273 000310 2004-10-06 10:01:01.000 365 NULL 000310 2005-10-06 10:01:01.000 92 365 000310 2006-01-06 12:01:01.000 59 92 000310 2006-03-06 14:01:01.000 122 59 000310 2006-07-06 19:01:01.000 92 122 000310 2006-10-06 10:01:01.000 NULL 92 000310 2006-10-06 10:01:01.000 NULL 92 (39 row(s) affected)
- The MIN function is used with > to retrieve the next row.
- The MAX function in used with < to achieve the previous row