SQL: Find next record based on time column, after "code" column. 2

[Aurillius]

Good Morning,
I want to query a table that contains multiple appointments with time stamped status code changes for each appointment. Specifically, I need to pull the immediate next record in time, after the last "IN" status. Below, you see that in this case, "MT" is the next code after the last "IN".

Appt Status Time_stamp
1234 YD 11/21/2018 8:00
1234 YD 11/21/2018 8:05
1234 YD 11/21/2018 8:20
1234 YD 11/21/2018 8:23
1234 YD 11/21/2018 8:30
1234 IN 11/21/2018 9:30
1234 IN 11/21/2018 9:45
1234 IN 11/21/2018 9:50
1234 MT 11/21/2018 11:05
1234 MT 11/21/2018 11:10
1234 MT 11/21/2018 11:13
1234 SHP 11/21/2018 12:55
1234 SHP 11/21/2018 12:56
1234 SHP 11/21/2018 12:58

 

[Andrzejek]

[pre]
Appt Status Time_stamp
1234 YD 11/21/2018 8:00
1234 YD 11/21/2018 8:05
1234 YD 11/21/2018 8:20
1234 YD 11/21/2018 8:23
1234 YD 11/21/2018 8:30
1234 IN 11/21/2018 9:30
1234 IN 11/21/2018 9:45
1234 IN [blue]11/21/2018 9:50[/blue][green]
1234 MT 11/21/2018 11:05
1234 MT 11/21/2018 11:10
1234 MT 11/21/2018 11:13
1234 SHP 11/21/2018 12:55
1234 SHP 11/21/2018 12:56
1234 SHP 11/21/2018 12:58[/green]
[/pre]
Time_stamp of "the last "IN" status"
[tt][blue]
Select MAX(Time_stamp)
From MyTable
Where Appt = 1234
And Status = 'IN'[/blue][/tt]

"the immediate next record in time"
[tt][green]
Select *
From MyTable
Where Time_stamp >
([/green][blue]Select MAX(Time_stamp)
From MyTable
Where Appt = 1234
And Status = 'IN'[/blue][green])
Order By Time_stamp[/green]
[/tt]
and just take very first record from this outcome.

Or you could just take the [tt]MIN(Time_stamp)[/tt] of the last [tt]Select[/tt] statement and look for the record with that one Time_stamp
[tt]
Select *
From MyTable
Where Time_stamp =
(Select MIN(Time_stamp)
From MyTable
Where Time_stamp >
(Select MAX(Time_stamp)
From MyTable
Where Appt = 1234
And Status = 'IN'))
[/tt]

---- Andy

There is a great need for a sarcasm font.

 

[bborissov]

DECLARE @MyTable TABLE (Appt int, Status varchar(5), Time_stamp datetime)
INSERT INTO @MyTable VALUES
(1234,'YD','11/21/2018 8:00'),
(1234,'YD','11/21/2018 8:05'),
(1234,'YD','11/21/2018 8:20'),
(1234,'YD','11/21/2018 8:23'),
(1234,'YD','11/21/2018 8:30'),
(1234,'IN','11/21/2018 9:30'),
(1234,'IN','11/21/2018 9:45'),
(1234,'IN','11/21/2018 9:50'),
(1234,'MT','11/21/2018 11:05'),	
(1234,'MT','11/21/2018 11:10'),
(1234,'MT','11/21/2018 11:13'),
(1234,'SHP','11/21/2018 12:55'),
(1234,'SHP','11/21/2018 12:56'),
(1234,'SHP','11/21/2018 12:58'),
 
 (234,'YD','11/21/2018 8:00'),
(234,'YD','11/21/2018 8:05'),
(234,'YD','11/21/2018 8:20'),
(234,'YD','11/21/2018 8:23'),
(234,'YD','11/21/2018 8:30'),
(234,'IN','11/21/2018 9:30'),
(234,'IN','11/21/2018 9:45'),
(234,'IN','11/21/2018 9:50'),
(234,'MT','11/21/2018 11:05'),	
(234,'MT','11/21/2018 11:10'),
(234,'MT','11/21/2018 11:13'),
(234,'SHP','11/21/2018 12:55'),
(234,'SHP','11/21/2018 12:56'),
(234,'SHP','11/21/2018 12:58')
 
Select MyTable.*
From (SELECT *, ROW_NUMBER() OVER (PARTITION BY Appt ORDER BY Time_Stamp) AS R_C
             FROM @MyTable) MyTable

INNER JOIN (SELECT *
            FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY Appt ORDER BY Time_Stamp) AS R_C
                        FROM @MyTable) MyTable
            WHERE Status = 'IN') MTbl ON  MTbl.Appt = MyTable.Appt
                                       AND MTbl.R_C = MyTable.R_C-1
                                       AND MyTable.Status <> 'IN'

Borislav Borissov
VFP9 SP2, SQL Server

 

[Aurillius]

Thanks guys. I'll give these a try.

Mark