Inserting data fromn one row into the previous row 1

[goofaholix]

I have a report which returns a list of rows that contain a start date and an end date.

However the end date is not populated.

If I were to group the data by Employee and order it by start date I wondered whether there is any way to grab the start date from row 2 and insert it into the end date field or row 1.

I presume if it's doable at all I'll need a stored procedure with a temp table, and maybe use cursors.

Any ideas?

Thanks.

 

[PHV]

Please post the actual sql code.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[goofaholix]

The SQL code hasn't been built yet as I don't know how to do it.

However if you imagine the table is as follows:

ROW EMP ID START DATE END DATE
1 111 2/10/04
2 111 13/10/04

Is there any way to copy the value of 13/10/04 from row 2 to the end date field in row 1.

Thanks.

 

[PHV]

I mean the SQL code producing the actual rows.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

Nevermind, the basic idea:
SELECT A.Row, A.EmpID, A.StartDate, Min(B.StartDate) As EndDate
FROM yourTable A LEFT JOIN yourTable B ON A.EmpID=B.EmpID AND A.StartDate<B.StartDate)
GROUP BY A.Row, A.EmpID, A.StartDate


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[goofaholix]

It works!

Skeptically I tried it, now I'm impressed, thanks a lot.

One thing I lost 3 rows out of 8000, I can only assume they were duplicates, which is fine as I'd want them gone anyway.

 

[PHV]

So, you discovered yourself the typo: extra left parenthese in the WHERE clause.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244