'creating BookingProcesses using SQL

i have the follwoing table called Requests in my database:

index userID pickup_time accepted
1 1 02.03.2022 09:10:00 0
2 1 02.03.2022 09:50:00 0
3 2 02.03.2022 09:30:00 0
4 2 02.03.2022 10:20:00 1
5 1 02.03.2022 10:05:00 1
6 1 02.03.2022 10:09:00 1
7 1 02.03.2022 10:14:00 0
8 2 02.03.2022 10:29:00 1
9 1 02.03.2022 14:40:00 0
10 1 02.03.2022 15:20:00 0
11 1 02.03.2022 16:00:00 1
12 2 02.03.2022 20:00:00 1
13 2 02.03.2022 20:50:00 1
14 2 02.03.2022 20:59:00 0

From the above table i want to create another table called BookingProcesses:

index userID first_pickup_time last_pickup_time successful
1 1 02.03.2022 09:10:00 02.03.2022 10:05:00 1
2 1 02.03.2022 10:09:00 02.03.2022 10:09:00 1
3 1 02.03.2022 10:14:00 02.03.2022 10:14:00 0
4 2 02.03.2022 09:30:00 02.03.2022 10:20:00 1
5 2 02.03.2022 10:29:00 02.03.2022 10:29:00 1
6 1 02.03.2022 14:40:00 02.03.2022 15:20:00 0
7 1 02.03.2022 16:00:00 02.03.2022 16:00:00 1
8 2 02.03.2022 20:00:00 02.03.2022 20:00:00 1
9 2 02.03.2022 20:50:00 02.03.2022 20:50:00 1
10 2 02.03.2022 20:59:00 02.03.2022 20:59:00 0

This is basciacally , how it should work:

A user can bascially request a ride (lets say via Uber) and he tries many times before he accepts one or he accepts the first offer he gets. A booking process lasts one hour, therefore if the user does not accept the propsed offers within one hour , the booking process finished with the status unsuccessful,otherwise if he accepts any of the proposed offers within one hour, the booking process is successful. However, if he accepts any of the proposed offers before the one hour expires, a new booking process should be created , if he tries to book again.

It can be seen in the BookingProcsses table that first_pickup_time denotes the start time of the booking process (the first time , when he tries to book within the new booking process) and last_pickup_time represents either the time , when the user accepted an offer within the booking process, or the time of the last offer that the user did not accept within the booking process (where the booking process is unsuccessful)

1,2 and 5 rows belong to the first booking process , where the first_pickup_time (1-th row) denotes the time the user started the booking process and last_pickup_time denotes the time , when he accepted a proposed offer (5-th row).

In the second booking process the user accepted the propsed offer from the first time , therefore first_pickup_time and last_pickup_time are the same..... and so on.

I can achieve this task using two cursors , where at first i iterate over all users and for each user i iterate over all his records and create the records for the BookingProcsses table. However i would like to know if there is another way i can go without using cursors, because achieving this using two cursors takes a lot of time.

i used two cursors to solve the tasks , however , it is not time-efficient , as it lasts nearly 5 hours to process 300000 rows in the request table.

sqlsql-server


Solution 1:[1]

my idea, just open a cursor, just traverse it once
  1. Open a cursor and sort by userID, pickup_time
  2. Traverse each record, when the previous record is compared with the current record,
  • If userID is different;
  • pickup_time exceeds 3600 seconds;
  • accepted is equal to 1

  1. As long as one condition holds?indicates that the current record is not the same process as the previous record, insert the last record into the bookingprocess table,

  2. Finally, insert the remaining one into the bookingprocess table

I have generated 300,000 records in the requests table on the desktop machine, and the execution time only takes 90 seconds

Below is my code:

set nocount on
declare @userId int, 
    @pickup_time smalldatetime, 
    @accepted int,
    @lastUserId int, 
    @first_pickup_time smalldatetime, 
    @last_pickup_time smalldatetime, 
    @successful int,
    @status int = 0;

declare request_cursor cursor for select userID, pickup_time, accepted 
    from testdb.dbo.requests order by userID, pickup_time;

open request_cursor

fetch next from request_cursor into @userId, @pickup_time, @accepted
while @@fetch_status = 0  
begin
    if @status = 1 and (
        @userId != @lastUserId or 
        datediff(second, @first_pickup_time, @pickup_time) > 3600 or 
        @successful = 1)
    begin
        insert into testdb.dbo.bookingprocesses 
            (userID,first_pickup_time,last_pickup_time,successful)
            values (@lastUserId, @first_pickup_time, @last_pickup_time, @successful);
        set @status = 0
    end

    if @status = 1
    begin
        set @last_pickup_time = @pickup_time
        set @successful = @accepted
    end
    else
    begin
        set @lastUserId = @userId
        set @first_pickup_time = @pickup_time
        set @last_pickup_time = @pickup_time
        set @successful = @accepted
        set @status = 1
    end

    fetch next from request_cursor into @userId, @pickup_time, @accepted
end

if @status = 1
begin
    insert into testdb.dbo.bookingprocesses 
        (userID,first_pickup_time,last_pickup_time,successful)
        values (@lastUserId, @first_pickup_time, @last_pickup_time, @successful);
end

close request_cursor
deallocate request_cursor

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 dulngix

Related Questions