Anonymous

DECLARE @hkDate DATETIMESET @hkDate = getdate()SELECT a.Label, b.Open_Date, b.Close_Date,status = CASEWHEN 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'ENDFROM Unit_Desc a, Rez_Desc b LEFT JOIN Rez_Details c ON b.Rez_ID = c.Rez_IDWHERE(b.Rez_Type = 0 OR a.Open_Date <= @hkDate+1 AND a.Close_Date > @hkDate)AND b.Unit_ID = a.Unit_IDAND b.Open_Date <= @hkDate+1AND b.Close_Date >= @hkDateAND b.Cancelled != -1ORDER BY a.Label