How do I make this SQL query work (computed column with nested query)?

I want to have a computed column that is true if the field asofdate is equal to the maximum asofdate in the table, otherwise false. I 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 of

case asofdate
when END OF P开发者_运维知识库REVIOUS MONTH then 1
else 0
end

From the MSDN article

A computed column is computed from an expression that can use other columns in the same table. The expression can be a noncomputed column name, constant, function, and any combination of these connected by one or more operators. The expression cannot be a subquery.

So no you can't make a computed column that way.

You cannot do it in an computed column, since the computed column can be only computed from the values of other columns in the same record.

You can do it in a view, instead:

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.

In MS Sql Server

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

EDIT based on comments

To get the end of the previous month -

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

I dont know if this would work, but you could create a computed column with formula as 'MAX(asofdate)', create an index on that column and then create a view that just compares the computed column with asofdate. Maybe not the best possible solution, but might be slightly better than just doing a subquery.

so the maximum value of asofdate is stored in a table called sometable right? In that case you dont have to check it using a subquery for every record. You can stored it in a variable and then check the current asofdate towards that.

declare @maxAsOfDate datetime;

Select @maxAsOfDate = max(asofdate) from sometable;

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

Cheers!