Menu
I'm working with SQL server 2016 and I can't figure out how to build that query.
Let's say I have a table like this:
ID EntryTime ResultTime
1 2016-05-02 13:30:00 2016-05-02 21:50:00
2 2016-05-02 14:45:00 2016-05-02 22:00:00
3 2016-05-02 16:30:00 2016-05-02 22:21:00
4 2016-05-03 01:00:00 2016-05-03 03:33:00
5 2016-05-03 10:30:00 2016-05-04 07:47:00
6 2016-05-03 12:30:00 2016-05-03 22:45:00
7 2016-05-04 11:30:00 2016-05-05 21:30:00
8 2016-05-04 12:30:00 2016-05-04 22:58:00
9 2016-05-04 13:30:00 2016-05-04 23:04:00
10 2016-05-04 13:45:00 2016-05-04 22:59:00
11 2016-05-04 14:00:00 2016-05-04 22:59:00
12 2016-05-04 14:15:00 2016-05-04 23:04:00
13 2016-05-04 17:45:00 2016-05-04 21:47:00
14 2016-05-05 23:30:00 2016-05-06 03:25:00
15 2016-05-05 23:45:00 2016-05-06 03:30:00
16 2016-05-06 00:00:00 2016-05-06 03:32:00
17 2016-05-06 00:15:00 2016-05-06 03:31:00
18 2016-05-06 00:30:00 2016-05-06 03:25:00
19 2016-05-06 00:45:00 2016-05-06 02:50:00
20 2016-05-06 01:00:00 2016-05-06 03:25:00
I want to select just the rows that the entry datetime is after the last chosen result datetime.
For example: the result time for line 1 is "2016-05-02 21:50:00" so the next row will be line 4 because this is the first line that the entry time is after the result time that was last selected, the next line suppose to be after the result time of line 4 (After "2016-05-03 03:33:00") so the next line will be line 5.
The requested result is:
ID EntryTime ResultTime
1 2016-05-02 13:30:00 2016-05-02 21:50:00
4 2016-05-03 01:00:00 2016-05-03 03:33:00
5 2016-05-03 10:30:00 2016-05-04 07:47:00
7 2016-05-04 11:30:00 2016-05-05 21:30:00
14 2016-05-05 23:30:00 2016-05-06 03:25:00
810
This can be easily done using equals to(=), less than(<), and greater than(>) operators. In SQL, the date value has DATE datatype which accepts date in 'yyyy-mm-dd' format. To compare two dates, we will declare two dates and compare them using the IF-ELSE statement.
To compare dates without the time part, don't use the DATEDIFF() or any other function on both sides of the comparison in a WHERE clause. Instead, put CAST() on the parameter and compare using >= and < operators. Let's use StackOverflow database to find all user profiles created on a particular date.
In the blue text, you can see the calculation of the SQL delta between two rows. To calculate a difference, you need a pair of records; those two records are “the current record” and “the previous year's record”. You obtain this record using the LAG() window function.
Here's the SQL query to compare each row with previous row. In the above query, we join sales table with itself using an INNER JOIN condition g2.id=g1.id + 1 that allows you to compare each row with its previous row. Please note, this condition depends on the fact that our id column has consecutive numbers.
One way you could do this is by using a recursive CTE to get the next row. For example,
with cte as (
select *
from myTable
where id = 1
union all
select t.*
from myTable t
cross join cte
where t.id = (
select id
from (
select id, row_number() over (order by id) rn
from myTable
where entrytime > cte.resulttime) z
where rn = 1)
)
select * from cte;
EDIT: For multiple "symbols", here's a method that would work (with example data).
DECLARE @myTable TABLE (Symbol CHAR(3), EntryTime DATETIME, ResultTime DATETIME)
INSERT @myTable VALUES ('AAA','2016-05-02 13:30:00','2016-05-02 21:50:00')
,('AAA','2016-05-02 14:45:00','2016-05-02 22:00:00')
,('AAA','2016-05-02 16:30:00','2016-05-02 22:21:00')
,('AAA','2016-05-03 01:00:00','2016-05-03 03:33:00')
,('AAA','2016-05-03 10:30:00','2016-05-04 07:47:00')
,('AAA','2016-05-03 12:30:00','2016-05-03 22:45:00')
,('AAA','2016-05-04 11:30:00','2016-05-05 21:30:00')
,('AAA','2016-05-04 12:30:00','2016-05-04 22:58:00')
,('AAA','2016-05-04 13:30:00','2016-05-04 23:04:00')
,('AAA','2016-05-04 13:45:00','2016-05-04 22:59:00')
,('AAA','2016-05-04 14:00:00','2016-05-04 22:59:00')
,('AAA','2016-05-04 14:15:00','2016-05-04 23:04:00')
,('AAA','2016-05-04 17:45:00','2016-05-04 21:47:00')
,('AAA','2016-05-05 23:30:00','2016-05-06 03:25:00')
,('AAA','2016-05-05 23:45:00','2016-05-06 03:30:00')
,('AAA','2016-05-06 00:00:00','2016-05-06 03:32:00')
,('AAA','2016-05-06 00:15:00','2016-05-06 03:31:00')
,('AAA','2016-05-06 00:30:00','2016-05-06 03:25:00')
,('AAA','2016-05-06 00:45:00','2016-05-06 02:50:00')
,('AAA','2016-05-06 01:00:00','2016-05-06 03:25:00')
,('BBB','2016-05-02 01:00:00','2016-05-02 03:01:00')
,('BBB','2016-05-02 02:00:00','2016-05-02 03:05:00')
,('BBB','2016-05-02 03:00:00','2016-05-02 03:40:00')
,('BBB','2016-05-02 04:00:00','2016-05-02 04:01:00')
,('BBB','2016-05-02 05:00:00','2016-05-03 07:00:00')
,('BBB','2016-05-02 06:00:00','2016-05-02 07:00:00')
,('BBB','2016-05-03 06:00:00','2016-05-03 07:05:00')
,('BBB','2016-05-04 06:01:00','2016-05-04 07:08:00')
,('BBB','2016-05-04 06:07:00','2016-05-04 07:52:00')
,('BBB','2016-05-05 06:00:00','2016-05-05 07:49:00')
,('CCC','2016-05-05 06:00:00','2016-05-05 07:04:00')
,('CCC','2016-05-05 06:05:00','2016-05-05 06:55:00')
,('CCC','2016-05-05 07:00:00','2016-05-05 07:10:00')
,('CCC','2016-05-05 07:06:00','2016-05-05 08:05:00')
,('CCC','2016-05-05 08:00:00','2016-05-05 08:15:00')
,('CCC','2016-05-05 08:09:00','2016-05-05 09:00:00');
WITH myTable AS (
SELECT Symbol, EntryTime, ResultTime, ROW_NUMBER() OVER (PARTITION BY Symbol ORDER BY EntryTime) RN
FROM @myTable)
, CTE AS (
SELECT *
FROM myTable
WHERE RN = 1
UNION ALL
SELECT T.*
FROM CTE
CROSS APPLY (
SELECT Symbol, EntryTime, ResultTime, RN
FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY EntryTime) RN2
FROM myTable
WHERE Symbol = CTE.Symbol
AND EntryTime > CTE.ResultTime) Z
WHERE RN2 = 1) T
)
SELECT Symbol, EntryTime, ResultTime--, RN [ID?]
FROM CTE
ORDER BY Symbol;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
