DECLARE @hkDate DATETIME
SET @hkDate = getdate()
SELECT a.Label, b.Open_Date, b.Close_Date,
status = CASE
WHEN datediff(dayofyear, b.Close_Date, @hkDate)=0 THEN 'C/O'
WHEN datediff(dayofyear, @hkDate, b.Open_Date)=1 THEN 'C/I'
WHEN b.Rez_Type = 1 THEN 'Owner'
WHEN (datediff(dayofyear, b.Open_Date, b.Close_Date) >= 30 AND c.tVal = datename(dw, @hkDate+1)) THEN 'S/O/C'
WHEN (datediff(dayofyear, b.Open_Date, b.Close_Date) >= 30 AND c.tVal != datename(dw, @hkDate+1)) THEN ''
ELSE 'S/O'
END
FROM Unit_Desc a, Rez_Desc b LEFT JOIN Rez_Details c ON b.Rez_ID = c.Rez_ID
WHERE
(b.Rez_Type = 0 OR a.Open_Date <= @hkDate+1 AND a.Close_Date > @hkDate)
AND b.Unit_ID = a.Unit_ID
AND b.Open_Date <= @hkDate+1
AND b.Close_Date >= @hkDate
AND b.Cancelled != -1
ORDER BY a.Label