question

Shikha071185 avatar image
Shikha071185 asked

Sub Query help???

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

sql-servercase-statement
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·

what's your question?

0 Likes 0 ·

1 Answer

·
Jon Crawford avatar image
Jon Crawford answered

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)

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.