You can do without joins with a little bit of trickery:

SELECT ProjectNo,

 CAST(SUBSTRING(MIN(CONVERT(VARCHAR,DateStarted,112) + IssueNo),9,10) AS INT) IssueNo,

 MIN(DateStarted) DateStarted

FROM Projects

GROUP BY ProjectNo

By taking the minimum of DateStarted + IssueNo and strip off the IssueNo...

If you rewrite the function and let it return the Codes string with intMasterAcctID as a parameter then you can do something like this:

SELECT intMasterAcctID, dbo.WarrantCodesByAcct(intMasterAcctID)

FROM   MasterRecord

You can use a powerful but little-known technique, namely a running update with variables:

DECLARE @Sales DECIMAL(10,2), @MV DECIMAL(10,2)

SELECT @Sales = 0, @MV = 0

UPDATE Table1

SET @MV =...

You could code and store the different product combinations as bitmasks, where each product corresponds to a bit of the bitmask (A=1, B=2, C=4, etc.). For 3 products there are...

Self joins and subselects can be avoided by using the following query:

SELECT Name, RIGHT(MIN(Grade + CAST(TestNo AS VARCHAR)),1), MIN(Grade), SUBSTRING(MIN(Grade + CAST(TestNo AS VARCHAR) + Comment),3,50)

FROM...