This is basic SQL.  I suggest that you do your own homework.

I think that this will perform better, because LEFT is not SARGable, but LIKE can be SARGable when there is no leading wildcard as here.

SELECT p.ItemName...

Use LEAD to find the next value of the status and then pull records where the current or next status is 'Terminated'.

;
WITH CTE AS

MIN(), MAX(), and AVG() are properties of sets.  You don't have any sets here.

Also, blabla is not a valid argument.  If it's a string, it needs to be...

Also, if you create an ITVF, SQL will inline the code.  That is, it will replace the function call with the body of the code from the function.  That means that...

You can do this in T-SQL, but it gets complicated very quickly.  I would recommend that you use a reporting tool such as SSRS to produce these kinds of reports.