CODEWITHSUNDEEP
sqlsql-server
Bosko Stupar
Bosko Stupar

Reputation: 153

MsSQL iterate through result to check and fetch more data

I'm doing query on our events table 

SELECT id, user_id, date, time, event_type_id FROM events
WHERE date >= '2018-05-01'
AND event_type_id = 3

This returns something like this

+------------+-------------+-------------------------+-------------------------+----------------+
| id         | user_id     |          date           |          time           | event_type_id  |
+------------+-------------+-------------------------+-------------------------+----------------+
|    5550579 |        1887 | 2018-05-03 00:00:00.000 | 2000-01-01 08:10:46.000 |              3 |
|    5550581 |        1887 | 2018-05-03 00:00:00.000 | 2000-01-01 08:41:38.000 |              3 |
|    5550629 |         993 | 2018-05-03 00:00:00.000 | 2000-01-01 08:01:59.000 |              3 |
|    5550662 |         546 | 2018-05-03 00:00:00.000 | 2000-01-01 09:13:04.000 |              3 |
|    5550711 |        1869 | 2018-05-03 00:00:00.000 | 2000-01-01 09:05:58.000 |              3 |
|    5550730 |        1148 | 2018-05-03 00:00:00.000 | 2000-01-01 09:15:20.000 |              3 |
|    5550732 |         611 | 2018-05-03 00:00:00.000 | 2000-01-01 09:26:26.000 |              3 |
|    5550733 |         627 | 2018-05-03 00:00:00.000 | 2000-01-01 09:26:32.000 |              3 |
|    5550734 |         530 | 2018-05-03 00:00:00.000 | 2000-01-01 09:27:32.000 |              3 |
|    5550767 |         566 | 2018-05-03 00:00:00.000 | 2000-01-01 09:50:15.000 |              3 |
|    5550810 |         415 | 2018-05-03 00:00:00.000 | 2000-01-01 10:10:01.000 |              3 |
|    5550812 |         556 | 2018-05-03 00:00:00.000 | 2000-01-01 10:14:08.000 |              3 |
|    5550878 |         777 | 2018-05-03 00:00:00.000 | 2000-01-01 10:39:19.000 |              3 |
|    5550896 |         926 | 2018-05-03 00:00:00.000 | 2000-01-01 09:45:22.000 |              3 |
|    5550900 |         926 | 2018-05-03 00:00:00.000 | 2000-01-01 10:06:25.000 |              3 |
|    5550901 |         780 | 2018-05-03 00:00:00.000 | 2000-01-01 10:06:29.000 |              3 |
|    5550902 |        1887 | 2018-05-03 00:00:00.000 | 2000-01-01 10:13:48.000 |              3 |
|    5550903 |        1938 | 2018-05-03 00:00:00.000 | 2000-01-01 10:16:35.000 |              3 |
|    5550915 |        2086 | 2018-05-03 00:00:00.000 | 2000-01-01 10:54:14.000 |              3 |
|    5550916 |        1796 | 2018-05-03 00:00:00.000 | 2000-01-01 10:54:15.000 |              3 |
+------------+-------------+-------------------------+-------------------------+----------------+

Now I would need to iterate/query over each row from the results to retrieve time of next event for specific user and within the same date like this: 

SELECT TOP 1 time FROM events
WHERE id > 5550579
AND user_id = 1887
AND date = '2018-05-03'

Which returns:

+-------------------------+
|          time           |
+-------------------------+
| 2000-01-01 08:41:38.000 |
+-------------------------+

With the given result I should compare times from first query and from second query. 2000-01-01 08:10:46.000 and 2000-01-01 08:41:38.000

If the difference between these two times is greater than 60 min, I should keep result from first query, if not, row should be removed. At the end I should have table list as from first query but only with results where times between two events are larger than 60 min for that user.

I don't know if this can be achieved in pure SQL or TSQL. I know how I would do this in some programming language but not in plain SQL.

Some pseudocode as a developer I'd do:

endresult = array()
query = select id, user_id, date, time, event_type_id from events where date >= '2018-05-01' and event_type_id = 3
foreach (query as row) {
    subquery = select top 1 time from events where id > row[id] and user_id = row[user_id] and date = row[date]
    if ((subquery[time] - row[time]) > 60) {
        endresult[] = row
    }
}

I updated this question with some realistic data. Also I can't explain it well as I'm a developer and not DBA expert.

Added with sample data, corresponding columns etc for testing purposes

SQLFiddle link - http://sqlfiddle.com/#!18/f83be

Upvotes: 0

Views: 85

Answers (2)

boskicthebrain
boskicthebrain

Reputation: 545

I like the George's answer, but he hardcoded too much. This should do the trick:

SELECT e1.id, e1.user_id, e1.date, e1.time, e1.event_type_id FROM events e1
WHERE e1.date >= '2018-05-01'
AND e1.event_type_id = 3
and dateadd(minute,60,e1.time)>
(
    SELECT TOP 1 e2.time FROM events e2
    WHERE e2.id > e1.id
    AND e2.user_id = e1.user_id
    AND e2.date = '2018-05-03'
    order by time desc
)

Upvotes: 1

George Menoutis
George Menoutis

Reputation: 7250

Is this what you want?

SELECT id, user_id, date, time, event_type_id FROM events
WHERE date >= '2018-05-01'
AND event_type_id = 3
and dateadd(minute,60,time)>
(
    SELECT TOP 1 time FROM events
    WHERE id > 5550579
    AND user_id = 1887
    AND date = '2018-05-03'
    order by time desc
)

From your pseudocode I deduce you only want rows(query 1) such that the time from query 2 is 60 minutes larger (not smaller), correct?

Also, as Sean Lange correctly commented, what is your order-by in the second query? It means to be something meaningful if your need you combined query to be meaningful, too.

Upvotes: 0

Related Questions