How check (1) exists and (2) active in one select?

[Coder7]

Hi all.

I need some help again, please.

I need to find out if at least one 1 row exists (I'm using select top 1) in tableA where eval_id = 'whatever' and lineup_seq_nbr = 1. If no rows exist I need to do some process.

If a row does exist I need to detemine if the row is active or deactivated by interrogating the value of end_date (null = active, not null = deactivated).
-----------------
So far I have this:
select top 1 eval_id, end_date
from tableA
where eval_id = 'unitA'
and lineup_seq_nbr = 1

I can use not exists to determine whether or not there is a row. But if there is a row returned, how can I then interrogate the end_date value to determine if the row is active or deactivated?

Thanks as always for any help!!

 

[Coder7]

So sorry...forgot one other thing.

I also need to get a value for eff_begin_date and if the row is active, I need to interrogate the value of eff_begin_date.

My apologies for the omission on my original post

 

[SQLBill]

Can you give us some sample data and the results you would like to see?


-SQLBill

 

[Coder7]

Thanks for responding Bill.

Here's some sample table data:

eval_id code lineup_seq_nbr eff_begin_date end_date

work 1 1 8/1/3 9/15/3
work 2 1 8/1/3 9/15/3
work 1 2 7/1/3 7/31/3
play 5 1 9/24/3 NULL
play 7 2 6/1/3 9/23/3
fun 1 1 9/1/3 NULL

NOTE: a NULL end_date = an active row

eval_id 'work' has all inactive rows
eval_id 'play' has one active row and one inactive row
eval_id 'fun' has one active row and no inactive rows
eval_id 'toil' has no rows in the table
--------------------
I'm only interested in rows where the lineup_seq_nbr = 1 and I only need 1 row from the table for the specified eval_id (all rows with the same eval_id and lineup_seq_nbr = 1 will have the same values for eff_begin_date and end_date).

Using eff_begin_date and end_date (where lineup_seq_nbr = 1) and eval_id = input parameter value from an asp page, I need to categorize the data into one of the following four categories in order to determine which 'routine' I want to perform

the four categories:
1. the eval_id does not exist in the table at all (the 'toil' example above)

2. the eval_id is in the table but has only inactive rows (the 'work' example above)

3. the eval_id has only active rows (and the eff_begin_date < tomorrow (the 'fun' above)


4. the eval_id has both active rows and the eff_begin_date = tomorrow (the 'play' example above)

I hope this information helps and thanks again!