SQL Server: Update multiple different rows with the same value from the same table

Question

I have a table that needs to be updated with the values from the same table. Basically, I want to change the connection setup in the rows where the worker and client are same and that the changed row Connection setup started in 5mins after the other connection (with the same worker and client) ended. The code below does this. But there is one more problem. when there are multiple connections that happen in short period of time , between the same client and worker I need them all to have the same Connection setup. I really am not sure how to achieve this.

the current query:

UPDATE t
SET t.[Connection setup] = t2.[Connection setup]
FROM Table1 t
INNER JOIN Table1 t2 ON  t.worker = t2.worker
                     AND t.client = t2.client
                     AND t.SessionNo <> t2.SessionNo
                     AND t.[Connection setup] <= DATEADD(mi, 5, t2.[Connection end])
                     AND t.[Connection setup] >= t2.[Connection end]

As you can see in this example there are several connections between same client and worker and the column "real_time" shows what time they should have.

     SessionNo  worker  Tag       Start     Ende    Dauer   Connection setup    Connection end      client      right_time
5   116590  mma 09.08.2020 00:00:00 12:44   13:01   00:17   09.08.2020 12:44:00 09.08.2020 13:01:00 OBENAT1D0137    12:44
6   106991  mma 09.08.2020 00:00:00 13:03   13:07   00:04   09.08.2020 13:03:00 09.08.2020 13:07:00 OBENAT1D0137    12:44
7   102306  mma 09.08.2020 00:00:00 13:07   13:56   00:49   09.08.2020 13:07:00 09.08.2020 13:56:00 OBENAT1D0137    12:44
8   430386  mma 09.08.2020 00:00:00 13:56   14:06   00:10   09.08.2020 13:56:00 09.08.2020 14:06:00 OBENAT1D0137    12:44
9   117264  mma 09.08.2020 00:00:00 14:06   14:17   00:10   09.08.2020 14:06:00 09.08.2020 14:17:00 OBENAT1D0137    12:44
10  434302  mma 09.08.2020 00:00:00 14:17   14:41   00:23   09.08.2020 14:17:00 09.08.2020 14:41:00 OBENAT1D0137    12:44
11  333234  mma 09.08.2020 00:00:00 14:41   14:55   00:13   09.08.2020 14:41:00 09.08.2020 14:55:00 OBENAT1D0137    12:44
12  271379  mg  09.03.2020 00:00:00 10:24   10:25   00:00   09.03.2020 10:24:00 09.03.2020 10:25:00 OBENAT1D0117    10:24
13  269650  mg  09.03.2020 00:00:00 10:25   10:47   00:21   09.03.2020 10:25:00 09.03.2020 10:47:00 OBENAT1D0117    10:24
14  290765  mg  09.03.2020 00:00:00 12:19   12:19   00:00   09.03.2020 12:19:00 09.03.2020 12:19:00 OBENAT1D0117    12:19
15  280892  mg  09.03.2020 00:00:00 12:19   12:22   00:03   09.03.2020 12:19:00 09.03.2020 12:22:00 OBENAT1D0117    12:19

with my current query they just take the time from the row before. any help would be great

EDIT : I added 4 rows to the table to explain the problem more in detail. For example in the last 4 rows the client and worker are the same, but the connections should be grouped in 2 different groups. not in one. as it can be seen by the column "right_time".

So do you want them set to the minimum [Connection Setup] value from the group of matching records? If not, then what is the logic that needs to be used? Thanks — NickW
– NickW, Commented Dec 3, 2020 at 18:35
@NickW yes I want them set to the minimum value. but the problem is if there are more than one group of the same worker and client on the same day — miwa_p
– miwa_p, Commented Dec 10, 2020 at 13:52
Please can you explain, in English rather than code, what the logic should be, given a specific record, for selecting the group of records from which the minimum [Connection Setup] value should be taken. Obviously they need to have the same worker and client and day but within that day what includes/exclude records from the group (I assume it is a time-based comparison)? — NickW
– NickW, Commented Dec 10, 2020 at 16:46
@NickW sorry if I didn't explained it well... yes you are correct it is time-based. For example same worker works with the same client multiple times in one day, we need to group them by time, where in one group go all the connections that in between them have less than 5 min break. there is an extra column that shows what the [right_time] should for [Connection setup] be. — miwa_p
– miwa_p, Commented Dec 11, 2020 at 9:51
Hi - please can you define exactly what you mean by "less than 5 min break.". Is this any record that has a start date <= 5 mins before the start date of the record being considered? Thanks — NickW
– NickW, Commented Dec 11, 2020 at 10:51

Sander · Accepted Answer · 2020-12-10 22:00:57Z

One option to group the rows would be to use recursion. However, recursion can be slow on large data sets...

Sample data

I omitted some columns from your sample data and only used the columns I need and added an id column for the initial values. If the id column is not part of your data set, then you will have to compare both conn_start and conn_end to sort of find the next row for each combination of worker and client (because there can be rows with an equal conn_start value like rows 14 and 15).

create table data
(
  id int,
  worker nvarchar(3),
  client nvarchar(15),
  conn_start datetime2(0),
  conn_end datetime2(0)
);

insert into data (id, worker, client, conn_start, conn_end) values
(5 , 'mma', 'OBENAT1D0137', '09.08.2020 12:44:00', '09.08.2020 13:01:00'),
(6 , 'mma', 'OBENAT1D0137', '09.08.2020 13:03:00', '09.08.2020 13:07:00'),
(7 , 'mma', 'OBENAT1D0137', '09.08.2020 13:07:00', '09.08.2020 13:56:00'),
(8 , 'mma', 'OBENAT1D0137', '09.08.2020 13:56:00', '09.08.2020 14:06:00'),
(9 , 'mma', 'OBENAT1D0137', '09.08.2020 14:06:00', '09.08.2020 14:17:00'),
(10, 'mma', 'OBENAT1D0137', '09.08.2020 14:17:00', '09.08.2020 14:41:00'),
(11, 'mma', 'OBENAT1D0137', '09.08.2020 14:41:00', '09.08.2020 14:55:00'),
(12, 'mg',  'OBENAT1D0117', '09.03.2020 10:24:00', '09.03.2020 10:25:00'),
(13, 'mg',  'OBENAT1D0117', '09.03.2020 10:25:00', '09.03.2020 10:47:00'),
(14, 'mg',  'OBENAT1D0117', '09.03.2020 12:19:00', '09.03.2020 12:19:00'),
(15, 'mg',  'OBENAT1D0117', '09.03.2020 12:19:00', '09.03.2020 12:22:00');

Solution

If this looks daunting, then make sure to check out this fiddle for a step-by-step construction.

with cte as
(
  select d.id,
         d.worker,
         d.client,
         d.conn_start,
         d.conn_end,
         datediff(minute,
                  coalesce(lag(d.conn_end) over(partition by d.worker, d.client order by d.id), d.conn_start),
                  d.conn_start) as diff_minutes
  from data d
),
rcte as
(
  select c.id,
         c.worker,
         c.client,
         c.conn_start,
         c.conn_end,
         c.diff_minutes,
         c.conn_start as conn_start_group
  from cte c
  where not exists ( select 'x'
                     from data d2
                     where d2.worker = c.worker
                       and d2.client = c.client
                       and d2.id < c.id )
    union all
  -- select next row for each (worker, client), keep conn_start_group if difference < 5 min
  select c.id,
         c.worker,
         c.client,
         c.conn_start,
         c.conn_end,
         c.diff_minutes,
         case
           when c.diff_minutes <= 5
           then r.conn_start_group
           else c.conn_start
         end
  from rcte r
  join cte c
    on  c.worker = r.worker
    and c.client = r.client
    and c.id > r.id
    and not exists ( select 'x'
                     from cte c2
                     where c2.worker = c.worker
                       and c2.client = c.client
                       and c2.id > r.id
                       and c2.id < c.id )
)
select rc.id,
       rc.worker,
       rc.client,
       rc.conn_start,
       rc.conn_end,
       rc.conn_start_group
from rcte rc
order by rc.id;

Result

id worker client       conn_start          conn_end            conn_start_group
-- ------ ------------ ------------------- ------------------- -------------------
5  mma    OBENAT1D0137 2020-09-08 12:44:00 2020-09-08 13:01:00 2020-09-08 12:44:00
6  mma    OBENAT1D0137 2020-09-08 13:03:00 2020-09-08 13:07:00 2020-09-08 12:44:00
7  mma    OBENAT1D0137 2020-09-08 13:07:00 2020-09-08 13:56:00 2020-09-08 12:44:00
8  mma    OBENAT1D0137 2020-09-08 13:56:00 2020-09-08 14:06:00 2020-09-08 12:44:00
9  mma    OBENAT1D0137 2020-09-08 14:06:00 2020-09-08 14:17:00 2020-09-08 12:44:00
10 mma    OBENAT1D0137 2020-09-08 14:17:00 2020-09-08 14:41:00 2020-09-08 12:44:00
11 mma    OBENAT1D0137 2020-09-08 14:41:00 2020-09-08 14:55:00 2020-09-08 12:44:00
12 mg     OBENAT1D0117 2020-09-03 10:24:00 2020-09-03 10:25:00 2020-09-03 10:24:00
13 mg     OBENAT1D0117 2020-09-03 10:25:00 2020-09-03 10:47:00 2020-09-03 10:24:00
14 mg     OBENAT1D0117 2020-09-03 12:19:00 2020-09-03 12:19:00 2020-09-03 12:19:00
15 mg     OBENAT1D0117 2020-09-03 12:19:00 2020-09-03 12:22:00 2020-09-03 12:19:00

thank you for your answer, but I have to say it is not completely right. I did additional tests and noticed that there is a mistake. I added 4 new rows in my table, where I noticed the problem. If I have 4 or more rows with the same client and worker on the same day. when applying your code says that the last row has the same time as the rows 12 & 13 should have, because your code takes the values from the GROUP BY which returns the earliest time from the same client and worker
There isn't a single group by in the solution, but the first part of rcte indeed returns only the earliest worker/client combo (as intended and explained in the fiddle). The issue was actually in the second part of rcte where the initial solution depended on unique conn_start values within a worker/client combo. The extra sample shows that this was an invalid assumption. The new solution uses the id columns instead (assuming they are unique and always increasing as time moves on).

NickW · Accepted Answer · 2020-12-11 15:26:17Z

This should work. I used the following table so you may need to adjust the code to get it to work with your actual table. The column [ConnSetupCalc] was used to test the calculation rather than updating the actual data during testing and then having to re-create the correct data.

CREATE TABLE [dbo].[conn_data](
    [SessionNo] [int] NULL,
    [worker] [nvarchar](3) NULL,
    [client] [nvarchar](15) NULL,
    [ConnectionSetup] [datetime2](0) NULL,
    [ConnectionEnd] [datetime2](0) NULL,
    [RightTime] [nvarchar](15) NULL,
    [ConnSetupCalc] [datetime] NULL
)

This Stored Proc should calculate the data in line with your example:

CREATE PROCEDURE UpdConn
AS
    declare @MyCursor CURSOR;
    declare @SessionNo int;
    declare @Worker nvarchar(3);
    declare @Client nvarchar(15);
    declare @ConnStart datetime;
    declare @ConnEnd datetime;

    declare @Worker_prev nvarchar(3);
    declare @Client_prev nvarchar(15);
    declare @ConnStart_prev datetime;
    declare @ConnEnd_prev datetime;

    BEGIN
        SET @MyCursor = CURSOR FOR
            SELECT SessionNo
            ,worker
            ,client
            ,ConnectionSetup
            ,ConnectionEnd
            FROM [dbo].[conn_data]
            order by worker, client, ConnectionSetup

        OPEN @MyCursor
        FETCH NEXT FROM @MyCursor
        INTO @SessionNo, @Worker, @Client, @ConnStart, @ConnEnd

        WHILE @@FETCH_STATUS = 0
            BEGIN
                IF @Worker = @Worker_prev and @Client = @Client_prev and DATEDIFF(mi,@ConnEnd_prev,@ConnStart) between 0 and 5
                    BEGIN
                        UPDATE conn_data set ConnectionSetup = @ConnStart_prev where SessionNo = @SessionNo;
                        -- Test logic: UPDATE conn_data set ConnSetupCalc = @ConnStart_prev where SessionNo = @SessionNo;
                    END
                ELSE
                    BEGIN
                        set @ConnStart_prev = @ConnStart;
                    END
                
                SET @Worker_prev = @Worker;
                SET @Client_prev = @Client;
                SET @ConnEnd_prev = @ConnEnd;

                FETCH NEXT FROM @MyCursor
                INTO @SessionNo, @Worker, @Client, @ConnStart, @ConnEnd     
  
            END; 

    CLOSE @MyCursor ;
    DEALLOCATE @MyCursor;
END;

Collectives™ on Stack Overflow

SQL Server: Update multiple different rows with the same value from the same table

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related