StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Give this a try:</p> <pre><code>WITH BEFORE AS ( SELECT eid, max(datetime) date FROM t WHERE datetime <= '2013-11-18 15:50:00.000' GROUP BY eid ), AFTER AS ( SELECT eid, min(datetime) date FROM t WHERE datetime >= '2013-11-18 15:50:00.000' GROUP BY eid ) SELECT t.eid, t.name, max(b.date) beforeDate, min(a.date) afterDate FROM t LEFT JOIN BEFORE b ON t.eid = b.eid LEFT JOIN AFTER a ON t.eid = a.eid GROUP BY t.eid, t.name ORDER BY t.eid </code></pre> <p>Or the non-CTE version:</p> <pre><code>SELECT t.eid, t.name, max(b.date) beforeDate, min(a.date) afterDate FROM t LEFT JOIN ( SELECT eid, max(datetime) date FROM t WHERE datetime <= '2013-11-18 15:50:00.000' GROUP BY eid ) b ON t.eid = b.eid LEFT JOIN ( SELECT eid, min(datetime) date FROM t WHERE datetime >= '2013-11-18 15:50:00.000' GROUP BY eid ) a ON t.eid = a.eid GROUP BY t.eid, t.name ORDER BY t.eid </code></pre> <p>I've added repeated dates to test it works with them too.</p> <p>Output:</p> <pre><code>| EID | NAME | BEFOREDATE | AFTERDATE | |-----|---------|----------------------------|----------------------------| | 1 | john | November, 18 2013 15:30:00 | November, 18 2013 16:00:00 | | 2 | Richard | November, 18 2013 13:40:00 | November, 18 2013 16:20:00 | | 3 | Mandy | (null) | November, 18 2013 20:20:00 | | 4 | Michael | November, 18 2013 13:00:00 | (null) | | 5 | Mosty | November, 18 2013 15:00:00 | November, 18 2013 16:00:00 | </code></pre> <p>Fiddle <a href="http://sqlfiddle.com/#!6/86b01/1" rel="nofollow">here</a>.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload