StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Try mine. It <em>works</em>:</p> <pre><code>declare @TestTable table (ID int, eid int, Name varchar(10), TestDate datetime) declare @InputDate datetime = '2013-11-18 15:50:00.000' insert into @TestTable (ID,eid,Name,TestDate) values (1,1,'john', '2013-11-18 15:30:00.000') ,(2,1,'john', '2013-11-18 14:10:00.000') ,(3,1,'john', '2013-11-18 13:30:00.000') ,(4,1,'john', '2013-11-18 16:00:00.000') ,(5,1,'john', '2013-11-18 17:00:00.000') ,(6,2,'richard', '2013-11-18 13:40:00.000') ,(7,2,'richard', '2013-11-18 16:20:00.000') ,(8,3,'mandy', '2013-11-18 20:22:00.000') ,(9,3,'mandy', '2013-11-18 20:20:00.000') ,(10,4,'michael', '2013-11-18 13:00:00.000'); with cte as ( select id, eid, name, TestDate, (datediff(s, TestDate, @InputDate)) as DateDiffSeconds from @TestTable ) select cte.eid, cte.name, x.testdate as maxunder, y.testdate as minover, @InputDate as InputDateForComparison from cte left join ( select eid, testdate from cte join ( select eid as eidmin, min(DateDiffSeconds) as datematchunder from cte where DateDiffSeconds >= 0 group by eid ) as datematchunder on datematchunder.datematchunder = cte.DateDiffSeconds ) x on x.eid = cte.eid left join ( select eid, testdate from cte join ( select eid as eidmin, max(DateDiffSeconds) as datematchover from cte where DateDiffSeconds <= 0 group by eid ) as datematchover on datematchover.datematchover = cte.DateDiffSeconds ) y on y.eid = cte.eid group by cte.eid, cte.name, x.testdate, y.testdate; </code></pre> <p>BAM!</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