Worst Join So Far

gistfile1.txt

#tmpResults t WITH (NOLOCK)
INNER JOIN table1 t1 WITH (NOLOCK)
	ON t1.ALT_ID_WHO = 'M 17  ' + t.ALT_KEY
INNER JOIN table2 t2 WITH (NOLOCK)
	ON SUBSTRING(t1.ALT_REF_ID,3,11) = t2.MemberNumber
	AND CAST(CAST(t2.numericbeginningdate AS CHAR(8)) AS DATE) <= t.StartingDate
	AND CAST(CAST(t2.numericendingdate AS CHAR(8)) AS DATE) >= t.StartingDate
	AND t2.VOID <> 'V'

Make sure you structure your data properly, otherwise you have to figure out joins that look like this.