Update table by looping values from another

Transact-SQL
1

Afternoon,

I need up update a schedule table. There is no join on these tables before the update. What I need to do is take the people in each group, for example the field techs, and loop through them and assign them each to a week. I have something that does update but it overwrites the last one inserted.

This is what I have so far:

DECLARE @id int

DECLARE c1 CURSOR FOR 
select id from dbo.Employees where groupid = 5 and active = 1 

OPEN c1 FETCH NEXT FROM c1 
INTO @id 

WHILE @@FETCH_STATUS = 0 
BEGIN 
   UPDATE dbo.OnCallShifts_Test
    SET RTOCId = @id
   PRINT @id 
  FETCH NEXT FROM c1 INTO @id 
  END 
 CLOSE c1 
 DEALLOCATE c1

Can anyone tell me what I need to do to make this work?

Thanks

Laura

2

The way you've written it, you will update dbo.OnCallShifts_Test for each row retrieved by the cursor. it's not really clear to me what you want to do. you mentioned

but there is no week column mentioned in the query.

Perhaps you could post some sample before and after data ( a few rows from each table)

3

Sorry I'm just taking this table inserting the week start and end date from anther table and then attempting updating the column with this query from the employees table.

So what I want to happen is for tech 18 (chris) and 36 (Dan) it loops through the table and inserts 18 then 36 into the RTOCid column then loops again until the table is full then exits.

so it would eventually look like this:

7/13/15 --- 7/20/15 Dan
7/20/15 --- 7/27/15 Chris
7/27/15 --- 8/3/15 Dan
8/3/15 --- 8/10/15 Chris

Does that make more sense?

Laura

4 
INSERT INTO dbo.OnCallShifts_Test ( Start_Date, End_Date, RTOCId )
SELECT wse.start_date, wse.end_date, e.id
FROM dbo.Employees e
CROSS JOIN dbo.week_start_and_end_dates_table wse
WHERE e.groupid = 5 AND e.active = 1 AND wse.start_date >= '20150713'
5

Thanks Scott, but that gives me 106 rows instead of 53 so it assigns Dan to a week then loops through again and gives Chris the week. So each week is listed twice.

6

Sorry, didn't look closely enough:

;WITH cte_emp AS (
    SELECT id, ROW_NUMBER() OVER(ORDER BY id) AS row_num
    FROM dbo.Employees
    WHERE groupid = 5 AND active = 1 
)
INSERT INTO dbo.OnCallShifts_Test ( Start_Date, End_Date, RTOCId )
SELECT wse.start_date, wse.end_date, cte_emp.id
FROM cte_emp
CROSS JOIN (
    SELECT MAX(row_num) AS emp_count
    FROM cte_emp
) AS cte_emp_count
INNER JOIN (
    SELECT start_date, end_date, ROW_NUMBER() OVER (ORDER BY start_date) AS row_num
    FROM dbo.week_start_and_end_dates_table
    WHERE start_date >= '20150713'
) AS wse ON 
    wse.row_num % emp_count + 1 = cte_emp.row_num
1 Like
7

I know I am a pain and I really do appreciate your expertise. I am using an ancient version of SQL (management won't upgrade so WITH and CTE aren't an option for me.

8

No problem:

CREATE TABLE #emp (
    ident int IDENTITY(0, 1) PRIMARY KEY, --starting @ 0 makes it much easier to 
                                          --match 1st emp with 1st date
    id int NOT NULL
    )

CREATE TABLE #wse (
    ident int IDENTITY(0, 1) PRIMARY KEY,--starting @ 0 makes it much easier to 
                                         --match 1st emp with 1st date
    start_date datetime NULL,
    end_date datetime NULL
    )
    
INSERT INTO #emp
SELECT id
FROM dbo.Employees
WHERE groupid = 5 AND active = 1
ORDER BY id

INSERT INTO #wse
SELECT start_date, end_date
FROM dbo.week_start_and_end_dates_table
WHERE start_date >= '20150713'
ORDER BY start_date

INSERT INTO dbo.OnCallShifts_Test ( Start_Date, End_Date, RTOCId )
SELECT wse.start_date, wse.end_date, emp.id
FROM #emp emp
CROSS JOIN (
    SELECT COUNT(*) AS emp_count
    FROM #emp
) AS emp_count
INNER JOIN #wse wse ON
    wse.ident % emp_count.emp_count = emp.ident
2 Likes
9

You sir are amazing! Thank you so much! I can't thank you enough.

Laura