select y.*,
case
when Sub_count <=3 & (cast(Submitted_UW_Date as date) = cast(LAST_WEEKDAY_DATE as Date)-1) then "1 Day"
when Sub_count <=1 & Submitted_UW_Date = LAST_WEEKDAY_DATE then "1 Day"
When Sub_count >3 & Submitted_UW_Date = LAST_WEEKDAY_DATE-1 then "2 Days"
else "Days Exceed"
end as Days
from (select count(submitted_UW_Date) as Sub_count from dbo.Encompass_Main where Cond_Approval_Date is null)y, Encompass_Main, DailyStatus where Submitted_UW_Date= LAST_WEEKDAY_DATE
you should use the newer join style of
SELECT * FROM table1 JOIN table2 ON table1.column = table2.column
instead of your natural join with the column equality in the WHERE clause.
But your problem is that your derived table y there only has a count() as a column, there's nothing else to join onto that would match your WHERE clause. Plus your WHERE has no table names in it, so no telling which column is supposed to be in which table. (system might work that out, but I can't)
20 People are following this question.
