D. Caan D. Caan - 2 months ago 6
SQL Question

SQL: Query status based on Timestamp and Code

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


I'd like to group the entries by Start and Stop times, based on the information that
Code != ''
means
RUNNING
and everything else means
STOPPED
.
The result would look like this (not repeating
RUNNING
entries until
STOPPED
is found)

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


I tried selecting both types in a single query but I don't think that's quite right as the column names would be the same on both subqueries and it doesn't group the RUNNING entries. What would be the best approach to achieve this result? I believe the current approach is not going to be very helpful to me so any ideas are welcome.

Standard SQL syntax would be best. SQL Server Express also an option.

Current Attempt:

SELECT Timestamp, Status
FROM (SELECT Timestamp, 'RUNNING' as Status FROM MyTable WHERE Code != ''),
(SELECT Timestamp, 'STOPPED' as Status FROM MyTable WHERE Code = '')

Answer
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

AND

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.