Lynn has a great blog post with lots of common datetime routines. http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/%5B/url%5D

See if that helps.

I am willing to help but since this sounds like homework the best I will...

Your requirements are incredibly unclear. Nobody can understand what you are trying to do here. Let's take your ddl and sample data and try again.

DECLARE @param1 VARCHAR(25) = 'Banana',

@param2 VARCHAR(25)...

About all you can do is use your select statement you posted and tweak it a little to help build your sql.

I think you meant to post this as a reply to Gail? http://www.sqlservercentral.com/Forums/Topic1313580-1291-1.aspx

Well I didn't actually look at an execution plan so maybe I am not...

Great job posting ddl. The sample data is too sparse for the second table to be able to provide much help. Also I am pretty uncertain what you want for...

This sounds a lot like homework. What have you tried so far?

I did and found it rather odd what it was doing. 😛

OK so we ignore the order by and assume a random order. The way you coded it is making what should be a set based update into an RBAR update....

There is 1 and only 1 way to ensure the order of data...

Yeah that didn't really help explain what you want but maybe something like this?

select t1.ID from #Temp1 t1

join #Temp2 t2 on t2.ID = t1.ID and t2.GDesc = @param2

join #Temp4 t4...

I am glad that you are seeing the benefit of providing consumable ddl and sample data. Unfortunately your create table statements don't work and you have a mismatched number of...

I was kind of wondering if you were trying to do a "previous" row type of thing. Of course what is previous? You have no order by. If you can...

I don't quite get what you are trying to do with your updates. They are quite strange. I agree the behavior is a bit odd but so is the logic....

Excellent job posting ddl and sample data. It is totally unclear what you want as output. If you can explain, this is probably pretty simple.