Query show min & max date for each client 1

[upplepop]

I have a table that records when clients have entered and exited a program. Clients can enter multiple times, often sequentially:

UID ClientID EntryDate  ExitDate    InProgram
1	5     1/1/2006	1/2/2006	N
2	5     1/2/2006	1/3/2006	N
3	5     1/3/2006	1/4/2006	N
4	5     1/4/2006	1/5/2006	N
5	6     1/2/2006	1/3/2006	N
6	6     1/3/2006	1/4/2006	N
7	7     1/6/2006	1/7/2006	N

I am having trouble with a query that would find the first EntryDate and last ExitDate for each client. The optimal results would be like this:

ClientID FirstDate  LastDate    InProgram
   5     1/1/2006    1/5/2006	    N
   6     1/2/2006    1/4/2006	    N
   7     1/6/2006    1/7/2006	    N

The query needs a "WHERE InProgram = 'N'" clause at the end as well.

Thanks in advance for your help!

 

[gmmastros]

This...

Select ClientId,
       Min(EntryDate) As FirstDate,
       Max(LastDate) As LastDate,
       InProgram
From   Table
Group By ClientId
Having InProgram = 'N'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[upplepop]

Thanks for the fast response George!

However, I'm having problems getting the query to work. I am getting these errors:

Column 'InProgram' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Column 'InProgram' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

 

[gmmastros]

I hope you can appreciate the difficulty 'we' having when helping people. Sometimes things like this slip through. It's embarassing.

Select ClientId,
       Min(EntryDate) As FirstDate,
       Max(LastDate) As LastDate,
       InProgram
From   Table
Group By ClientId[!], InProgram[/!]
Having InProgram = 'N'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[upplepop]

Thanks, it worked!