A SQL mind twister (at least for me):

I have a huge table that I am trying to SQL-Select and get a 1-row result that looks
like this:

cPolicySymbol	cPolicyNumber	tPolicyEffectiveDate	tPolicyExpirationDate	iEffSrcID	iExpSrcID
BA1		0000103		1994-07-01 12:00:00 AM	2002-07-01 12:00:00 AM	2		1

I have gotten it down to an interim table that is not quite there:

Cpolicysymbol	Cpolicynumber	Tpolicyeffectivedate  	Tpolicyexpirationdate 	iAppID
BA1          	0000103      	2001.07.01 12:00:00 AM	2002.07.01 12:00:00 AM	1       	
BA1          	0000103      	1994.07.01 12:00:00 AM	2001.07.01 12:00:00 AM	2       	

What I am trying to do now is use the lowest tPolicyEffectiveDate value among the
two rows and then create a new column called iEffSrcID which indicates which
iAppID it came from. Then I have to find the highest tPolicyExpirationDate value
among the two rows and then create a new column called iExpSrcID
which indicates which iAppID it came from. The following SQL made the two-row
interim result set:

  Select ;
    cPolicySymbol , cPolicyNumber , ;
    Min(tPolicyEffectiveDate) as tPolicyEffectiveDate , ;
    Max(tPolicyExpirationDate) as tPolicyExpirationDate , ;
    iAppID ;
  From PremPolicyXref Where ;
    cPolicySymbol = "BA1" and cPolicyNumber = "0000103"  and iPremApplicationID = 1 ;
    or cPolicySymbol = "BA1" and cPolicyNumber = "0000103" and iPremApplicationID = 2 ;
  Group by cPolicySymbol , cPolicyNumber , iPremApplicationID

I will be calling this SQL with ADO on the VFPOLEDB provider, so I need to get it into
a single SQL-Select because the provider will only take a single command. So the
interim statement has to fit into a subquery of some kind, right?
I'm open to whatever anyone on here can suggest. Help?


Thanks!