lyptus
asked on
loop through table and insert new record based on current records
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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'.