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.