rodchar
3/28/2007 9:46:00 PM
once again, thank you for your gracious help and explaining how you did it.
humbly and appreciative,
rod.
"Tom Cooper" wrote:
> Something like the following process.
>
> As I understood it, you wanted to update table1 with the data from the row
> for each employeeID that has the latest startDate. So one thing needed was
> to find the latest startDate for each employeeID. To do that, you would do:
>
> Select t2.employeeID, Max(t2.startDate) As startDate
> From table2 t2
> Group By t2.employeeID
>
> I don't know whether or not you have run across the concept of a derived
> table before, but you can treat any select statement as if it were a virtual
> table by enclosing it in parenthesis and then giving it a name. So in the
> statement, I have
>
> (Select t2.employeeID, Max(t2.startDate) As startDate
> From table2 t2
> Group By t2.employeeID) As x
>
> So, now effectively I have a table named x which has one row for each
> employeeID in table2 with the largest startDate for that employeeID. But I
> want the status and endDate that go with that startDate, so I inner join
> this derived table x back to table2 getting only those rows from table2
> which have the largest startDate for each employeeID. Note that I am
> referencing table2 more than once, so it is good practice to give the tables
> alias's so I can keep track of which reference we I am using. Sometimes an
> alias is syntacticly required, sometimes, as in this case, it just makes
> the query clearer. So I called this reference to table2 t2A.
>
> (Select t2.employeeID, Max(t2.startDate) As startDate
> From table2 t2
> Group By t2.employeeID) As x
> Inner Join table2 t2A On t1.employeeID = t2A.employeeID And x.startDate =
> t2A.startDate
>
> So now, in the t2A columns, we have for each employeeID, the latest
> startDate, and the endDate and status that goes with that startDate. You
> could test this by just doing a select of the columns in t2A,
>
> Select t2A.* From
> (Select t2.employeeID, Max(t2.startDate) As startDate
> From table2 t2
> Group By t2.employeeID) As x
> Inner Join table2 t2A On t1.employeeID = t2A.employeeID And x.startDate =
> t2A.startDate
>
> So now, we have the data we want to use for the update, so all that remains
> is to tell SQL to do the update, that part is
>
> Update t1
> Set startDate = t2A.startDate,
> endDate = t2A.endDate,
> status = t2A.status
> From table1 t1
>
> Putting it all together, we need to add an ON clause to tell SQL how to
> match rows from table1 to the virtual table x, so the final version looks
> like
>
> Update t1
> Set startDate = t2A.startDate,
> endDate = t2A.endDate,
> status = t2A.status
> From table1 t1
> Inner Join (Select t2.employeeID, Max(t2.startDate) As startDate
> From table2 t2
> Group By t2.employeeID) As x On t1.employeeID = x.employeeID
> Inner Join table2 t2A On t1.employeeID = t2A.employeeID And x.startDate =
> t2A.startDate
>
> A couple of final comments. First, this solution assumes that for everry
> employeeID, there is only one row in table2 with the largest startDate. If
> an employeeID has multiple rows in table2 with the same startDate and that
> startDate is the largest startDate for that employeeID, then table1 will be
> updated with one of those rows, but you will not be able to tell in advance
> which one.That is, if employeeID 1 has 4 rows in table2, one for Jan 1, one
> for Feb 1, and 2 for Mar 1, this query will update the row in table1 for
> employee1 with one of the Mar 1 rows. Second, this form of the UPDATE
> statement is proprietary to Microsoft SQL Server and is not ANSI standard.
> I prefer the Microsoft syntax, but there are others who would recommend you
> use the ANSI standard form.
>
> HTH,
> Tom
>
> "rodchar" <rodchar@discussions.microsoft.com> wrote in message
> news:C3F9B991-F0D3-4ECC-94B2-6C7C1CF67777@microsoft.com...
> > Tom,
> > thank you for your generous help. i want to ask you another question, if i
> > may, but i'm not sure how to ask. how do i begin to understand how you
> > came
> > up with this statement that works for me?
> >
> > thanks,
> > rodchar
> >
> > "Tom Cooper" wrote:
> >
> >> Update t1
> >> Set startDate = t2A.startDate,
> >> endDate = t2A.endDate,
> >> status = t2A.status
> >> From table1 t1
> >> Inner Join (Select t2.employeeID, Max(t2.startDate) As startDate
> >> From table2 t2
> >> Group By t2.employeeID) As x On t1.employeeID = x.employeeID
> >> Inner Join table2 t2A On t1.employeeID = t2A.employeeID And x.startDate =
> >> t2A.startDate
> >>
> >> Tom
> >>
> >> "rodchar" <rodchar@discussions.microsoft.com> wrote in message
> >> news:1754B712-6523-4573-9D38-0176954410CC@microsoft.com...
> >> > hey all,
> >> > i have the following 2 tables:
> >> > table1
> >> > -------
> >> > employeeID(PK)
> >> > ,Name
> >> > ,status (part-time/full-time)
> >> > ,startDate
> >> > ,endDate
> >> >
> >> > table2
> >> > -------
> >> > employeeID(FK)
> >> > ,status (part-time/full-time)
> >> > ,startDate
> >> > ,endDate
> >> >
> >> > Sample Population:
> >> > table1
> >> > -------
> >> > 1, John Smith, null, null, null
> >> > 2, Jane Doe, null, null, null
> >> >
> >> >
> >> > table2
> >> > -------
> >> > 1, FT, 8/1/06, null
> >> > 1, PT 7/1/06, 7/31/06
> >> > 2, PT, 3/1/05, 3/31/05
> >> > 2, FT 4/1/05, 4/30/05
> >> > 2, FT 5/1/05, null
> >> >
> >> > is there a simple way to update table1 with table2 information
> >> > (grabbing
> >> > only the most recent information for an employee from table2)? or any
> >> > psuedo-suggestions?
> >> >
> >> > thanks,
> >> > rodchar
> >>
> >>
> >>
>
>
>