We help IT Professionals succeed at work.

loop through table and insert new record based on current records

lyptus
lyptus asked
on
I have a table the keeps track of employee goals. Some of my reports are not working with this table because they don't have a goal set for 2008. I want to loop through the table and grab their last goal and insert a row with that goal for the current date. Here is my table.
EmployeeGoal
empID
date
roundTypeID
goal

example of data.
empID     date        roundtypeID     goal
1000        1/1/07           1                  3
1000        6/1/07           1                  4
1000        1/1/07           2                  6
1000        6/1/07           2                  7
1001        1/1/07           1                  8
1001        6/1/07           1                  10
1001        1/1/07           2                  12
1001        6/1/07           2                  13

I am only concerned with the max date for each empID and roundTypeID.
example
empID     date        roundtypeID     goal
1000        6/1/07           1                  4
1000        6/1/07           2                  7
1001        6/1/07           1                  10
1001        6/1/07           2                  13

I would then use this data to insert these 4 new records
empID     date        roundtypeID     goal
1000        1/29/08          1                  4
1000        1/29/08           2                  7
1001        1/29/08           1                  10
1001        1/29/08           2                  13

so for each empID and roundtype ID I am looking at the max date, getting the goal and then inserting a new record with same empID, roundtypeID, and goal and inserting the current date as the date.
Comment
Watch Question

Commented:
INSERT INTO empdata(Empid, Date, roundTypeID, goal)
Select EmpId, '20080129', roundtypeid, goal
FROM empdata e
inner join (Select EmpID, Max(date) as MaxDate) t
on e.empid = t.empid and e.date = t.Maxdate
where t.MaxDate < '20080101'

Author

Commented:
I changed the query to this.
INSERT INTO EmployeeGoal(empID, Date, roundTypeID, goal)
Select empID, '20080129', roundtypeid, goal
FROM employeeGoal e
inner join (Select empID, Max(date) as MaxDate) t
on e.empID = t.empID and e.date = t.Maxdate
where t.MaxDate < '20080101'

I get this error.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'empID'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'date'.
Msg 209, Level 16, State 1, Line 2
Ambiguous column name 'empID'.
You had no from or grouping in your derived table.
INSERT INTO EmployeeGoal(empID, Date, roundTypeID, goal)
Select empID, '20080129', roundtypeid, goal
FROM employeeGoal e
inner join (Select empID, Max(date) as MaxDate from EmployeeGoal group by EmpId) t
on e.empID = t.empID and e.date = t.Maxdate
where t.MaxDate < '20080101'

Open in new window

And you will get an ambiguous column reference empID unless you qualify it as either e.empID or t.empID like below.
INSERT INTO EmployeeGoal(empID, Date, roundTypeID, goal)
Select e.empID, '20080129', roundtypeid, goal
FROM employeeGoal e
inner join (Select empID, Max(date) as MaxDate from EmployeeGoal group by EmpId) t
on e.empID = t.empID and e.date = t.Maxdate
where t.MaxDate < '20080101'

Open in new window

Explore More ContentExplore courses, solutions, and other research materials related to this topic.