T-SQL Query Column based on filtered condition

I could do this rather easily in Python (or any other language), but I’m trying to see if this is possible with pure T-sql

I have two tables:

Table A has a bunch of general data and timestamps with each row

+------+------+------+-----------+
| Col1 | Col2 | Col3 | Timestamp |
+------+------+------+-----------+
| A    | B    | C    | 17:00     |
| D    | E    | F    | 18:00     |
| G    | H    | I    | 23:00     |
+------+------+------+-----------+

​x
 
+------+------+------+-----------+| Col1 | Col2 | Col3 | Timestamp |+------+------+------+-----------+| A    | B    | C    | 17:00     || D    | E    | F    | 18:00     || G    | H    | I    | 23:00     |+------+------+------+-----------+​

Table B is considered metadata

+-------+-----------+
| RunNo | Timestamp |
+-------+-----------+
|     1 | 16:50     |
|     2 | 17:30     |
|     3 | 18:00     |
|     4 | 19:00     |
+-------+-----------+

 
+-------+-----------+| RunNo | Timestamp |+-------+-----------+|     1 | 16:50     ||     2 | 17:30     ||     3 | 18:00     ||     4 | 19:00     |+-------+-----------+​

So the general data is referenced to a “RunNo”. The timestamp in table B is just when that “Run” was created in the DB. You can match the General data to its proper run number by comparing the timestamps. For example the timestamp for the first row in Table A is 17:00 which is greater than 16:50 and less than 17:30, so obviously this row belongs to RunNo 1. How can I perform this query so the resulting table is

+------+------+------+-----------+-------+
| Col1 | Col2 | Col3 | Timestamp | RunNo |
+------+------+------+-----------+-------+
| A    | B    | C    | 17:00     |     1 |
| D    | E    | F    | 18:00     |     2 |
| G    | H    | I    | 23:00     |     4 |
+------+------+------+-----------+-------+

 
+------+------+------+-----------+-------+| Col1 | Col2 | Col3 | Timestamp | RunNo |+------+------+------+-----------+-------+| A    | B    | C    | 17:00     |     1 || D    | E    | F    | 18:00     |     2 || G    | H    | I    | 23:00     |     4 |+------+------+------+-----------+-------+​

I though maybe using CASE would be helpful here, but I couldn’t figure how to put it togther

SELECT a.*,
       CASE WHEN a.TIMESTAMP < b.TIMESAMP AND a.TIMESTAMP > b.TIMSTAMP then b.RunNo END AS RunNo
FROM A as a, B as b

 
SELECT a.*,       CASE WHEN a.TIMESTAMP < b.TIMESAMP AND a.TIMESTAMP > b.TIMSTAMP then b.RunNo END AS RunNoFROM A as a, B as b​

Any help would be greatly appreciated.

Answer

CASE allows you to return different values (i.e. columns or expressions) based on a condition. This is not what you what here. You want to join tables and filter matching rows based on a condition.

I have replaced the name Timestamp with ts, as even escaped, I had difficulties with it on SQL Fiddle. It is a reserved keyword.

SELECT A.Col1, A.Col2, A.Col3, A.ts, MAX(B.RunNo) AS RunNo
FROM
    A
    INNER JOIN B
        ON A.ts > B.ts
GROUP BY A.Col1, A.Col2, A.Col3, A.ts

 
SELECT A.Col1, A.Col2, A.Col3, A.ts, MAX(B.RunNo) AS RunNoFROM    A    INNER JOIN B        ON A.ts > B.tsGROUP BY A.Col1, A.Col2, A.Col3, A.ts       ​

With A.ts > B.ts this returns RunNo 2 for the second entry. With A.ts >= B.ts this returns RunNo 3 for the second entry.

See http://sqlfiddle.com/#!18/9dd143/6/0

Advertisement

Answer