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.
lyptusAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JimFiveCommented:
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'
0
lyptusAuthor 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'.
0
BrandonGalderisiCommented:
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

0
BrandonGalderisiCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.