I've got this table:
Timestamp[DateTime] | Code [Text]
01-jan-2010 00:00:00 | ABC
01-jan-2010 02:00:00 | AAA
01-jan-2010 02:20:00 |
01-jan-2010 03:00:00 | BBB
Code != ''
Timestamp[DateTime] | Status [Text]
01-jan-2010 00:00:00 | RUNNING
01-jan-2010 02:20:00 | STOPPED
01-jan-2010 03:00:00 | RUNNING
SELECT Timestamp, Status
FROM (SELECT Timestamp, 'RUNNING' as Status FROM MyTable WHERE Code != ''),
(SELECT Timestamp, 'STOPPED' as Status FROM MyTable WHERE Code = '')
SELECT r.Timestamp ,CASE WHEN r.T1Code != '' THEN 'RUNNING' ELSE 'STOPPED' END as Status FROM ( SELECT t1.Timestamp ,CAST(COALESCE(t1.Code,'') AS VARCHAR(MAX)) as T1Code ,(SELECT TOP 1 CAST(COALESCE(t2.Code,'') AS VARCHAR(MAX)) FROM myTable t2 WHERE t2.Timestamp < t1.Timestamp ORDER BY t2.Timestamp DESC) as T2Code FROM myTable t1 ) r WHERE NOT(r.T1Code != '' AND r.T2Code != '') OR r.T2Code IS NULL
Updated to Allow For Testing Null or Empty string and to take into consideration issue of comparison of text data type.
http://rextester.com/OBWS90094 showing it works
If you really want the reverse meaning of stopped/running when Code is not null or empty then change the following lines:
CASE WHEN r.T1Code = '' THEN 'RUNNING' ELSE 'STOPPED' END as Status
WHERE NOT(r.T1Code = '' AND r.T2Code = '') OR r.T2Code IS NULL
This shows it works: http://rextester.com/KUGW22706
You actually can do it all in one query but it is a little easier to show the logic in a nested query so that I can use a column reference. If your DBMS supported things like a lateral join/apply and/or LAG/LEAD window functions it would be a bit simpler but you can essentially do the same thing using a correlated sub query in your Column definition. That sub query gets the code of the previous Timestamp Row. If both current row code and previous row code != '' THEN it is still running so don't show the record. But you still need to include the first record so test if t2.Code is null.