Select and sub-select -1 Row

Transact-SQL
1

How would I be able to return the previous row in a sub-query? What I have is a table with a Date column and a Status column, I want to be able to do a select which returns the Date, Status and the previous-row's Status. For example: -

2015-12-01, Open
2015-11-28, Closed
2015-11-04, Open
....

my select should return: -

2015-12-01, Open, Closed
2015-11-28, Closed, Open
2015-11-04, Open, ......

So something like...

Select aDate, aStatus, (Select aStatus -1 Row) as aPreviousStatus from [aTable] Order by aDate Desc

Thanks

Phil

2

If you are on SQL 2012 or later, you can use the windowing function LAG like this:

select
	DateColumn,
	StatusColumn,
	lag(StatusColumn) over (order by DateColumn) as PreviousStatus
from
	YourTable;
3

Sorry James, should have mentioned this needs to work on SQL 2008

Phil

4

For 2005 or later,

SELECT
	a.DateColumn,
	a.StatusColumn,
	b.StatusColumn as PreviousStatus
FROM
	YourTable a
	OUTER APPLY
	(
		SELECT TOP (1) b.StatusColumn
		FROM YourTable b
		WHERE b.DateColumn < a.DateColumn
		ORDER BY b.DateColumn DESC
	) AS b
1 Like
5

Excellent thanks James.

6

I wish you had posted actual DDL instead of making us do your typing for you. Did you read the Netiquette at the at the start of the forum?

The point you missed is the time as a continuum. And that his status is a state of being. This means that an entity has a particular status during a time. The time period is shown as (start_time, end_time) pairs.

If the status you are dealing with, has a lot of values, or frequently changes, then use a reference to another table. If the status you are dealing with, has a few static values, then put it in a check constraint. I would guess yours is the second case.

CREATE TABLE Doors
(door number integer nOT NULL.
door_status char (5) not null
check (door status in ('open', 'close')),
Open_date date not null,
primary key (door number, open_date),
close_date date,
check (open_date <= close_date)

What you have is a design error called "attribute splitting" and it is very common. What you have done is mimic in SQL. What would have been on the clipboard with a sign out sheet.

What other people are suggesting is the kludge that you always get stuck with in a split design – – reassembling the fact that you split into pieces. Do not do that, this is like a mop. This is like mopping the floor but not fixing the leak in the roof.