Here is the actual code I tried but failed with but the concept is the same.

I tried with one ref to see if I could get it right with that...

The data is connected by REF but in relation to the dates it is Hist_Start_date >P_Start_Date but the bit I am struggling with is selecting just the immediate row and...

Hi Erland, Thanks for your help. I spotted the UNION ALL in your query and tried again with that instead and it worked. I didn't think to try that earlier...

That inserts three rows. Which leaves me confused even more.

Thanks Erland. The select runs fine and selects the necessary data. Insert to local table gives the same result. Identical duplicates are omitted. The reasons you ask 'why' are historic...

Thanks J Livingstone. 

You sure are a SQL Champion. I had a feeble effort myself using CASE and had some strange results.

That seems to work ChrisM. Thanks. 
Thanks J Livingstone too.
Just one other question. How do I get the original non NULL values to appear in the new column too...

Hi ChrisM,
I have never used APPLY but I am not sure how this would solve the issue. After reading about APPLY it seems more like a inner join which...

Hi,
I know this scenario I have is quite a messy situation. I have managed to get my data to a better stage but have encountered what I am hoping...

Hi Thom,
Excellent. After a second look it certainly does work. Just what I needed. Also only needed a slight change to my current script.
Thanks for your help.

Hi Thom,

Thanks for your suggestion. Not sure if I've understood you correctly but that does not work as sometimes table B will have 6 rows and Table A...

Thanks a million Sarah.

I was over complicating it. Much appreciated.:-)

Hi Jeff,

After studying your code and doing a little reading I have full understanding of your code. It's an approach i may not have applied off the top of my...

Thanks Jeff,

That is exactly what I needed. I would have never have thought of that approach to solving it. I was trying all kinds of tricks including putting values to...

Hi All,

Thank you for your input thus far.

To tell you what I have tried so far. I have had an attempt using SSIS but as I am new to that...