How to make this SQL query work (computed column with subquery)?

Question

I want the computed column to be true if the field asofdateis equal to the maximum asofdatein the table, otherwise false. I have tried the following, but I am getting a syntax error. What is the right way to do this?

select 
case asofdate
when select max(asofdate) from sometable then 1
else 0
end 
from sometable

Alternatively, is it possible to have a computed column along the lines

case asofdate
when END OF PREVIOUS MONTH then 1
else 0
end

Answer 1

You cannot do this in a calculated column, since a calculated column can only be calculated from the values ​​of other columns in the same record.

Instead, you can do this:

CREATE VIEW
        v_with_last
AS
SELECT  *, CASE asofdate WHEN MAX(asofdate) OVER () THEN 1 ELSE 0 END AS the_last
FROM    sometable

Unfortunately, you cannot index this view.

Answer 2

MSDN

, . , , , . .

, .

Answer 3

MS Sql Server

SELECT case asofdate 
    WHEN (SELECT MAX(asofdate) FROM sometable) THEN 1 
    ELSE 0 END
FROM sometable

-

SELECT DATEADD(dd,-(DAY(asofdate)),asofdate)

Answer 4

asofdate sometable ? , . , asofdate.

declare @maxAsOfDate datetime;

Select @maxAsOfDate = max(asofdate) from sometable;

Select case when asofdate >= @maxAsOfDate then 1 else 0 end from sometable

!

Answer 5

I don’t know if this will work, but you can create a calculated column with a formula like "MAX (asofdate)", create an index on that column, and then create a view that simply compares the calculated column with asofdate. This may not be the best solution, but it might be a little better than just doing a subquery.