Computed Column Question

Am wondering if this is possible. In a table I'll
call 'Cases' I have Case_Start, Case_End, Case_Active
fields. Case_Start and Case_End are date fields
indicating when the case started and when the case ended.
Case_Start will always be a date since we can't have a
record unless we have this information. Case_End will
be null if the case is open and the ending date is not
known, it will be a date if we know the case ending
date. Case_Active is a bit field. I would like to make
it a computed field. I would like it to = 1 if the
Case_End date is not null and todays date is between the
Case_Start and Case_End date. I would like it to = 1 if
the Case_End field is null. I would like it to = 0 if
Case_End is not null and todays date is greater than the
Case_End date. Is this possible with a computed field
and how? If not, is there a better way to accomplish
what I'm trying to do? Thanks in advance.Hi Mark,
Use the following for the computed column:
Case_active AS CASE WHEN Case_start > GETDATE() OR Case_end < GETDATE() THEN
0 ELSE 1 END
I.e. a case is not active if the startdate is after today or the enddate is
before today, in all other cases, including an unknown enddate, the case is
active.
Jacco Schalkwijk
SQL Server MVP
"Mark" <anonymous@.discussions.microsoft.com> wrote in message
news:132a01c3fbaf$e2a700f0$a501280a@.phx.gbl...
> Am wondering if this is possible. In a table I'll
> call 'Cases' I have Case_Start, Case_End, Case_Active
> fields. Case_Start and Case_End are date fields
> indicating when the case started and when the case ended.
> Case_Start will always be a date since we can't have a
> record unless we have this information. Case_End will
> be null if the case is open and the ending date is not
> known, it will be a date if we know the case ending
> date. Case_Active is a bit field. I would like to make
> it a computed field. I would like it to = 1 if the
> Case_End date is not null and todays date is between the
> Case_Start and Case_End date. I would like it to = 1 if
> the Case_End field is null. I would like it to = 0 if
> Case_End is not null and todays date is greater than the
> Case_End date. Is this possible with a computed field
> and how? If not, is there a better way to accomplish
> what I'm trying to do? Thanks in advance.