>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!

Something like this?

SELECT cPolicySymbol, cPolicyNumber , ;
        tPolicyEffectiveDate AS tMinPolicyEffectiveDate, ;
        {//} AS tMaxPolicyExpirationDate ;
    FROM BigTable ;
    WHERE tPolicyEffectiveDate IN (SELECT Min(tPolicyEffectiveDate) FROM BigTable) ;
UNION
    SELECT cPolicySymbol , cPolicyNumber , ;
        {//} AS tMinPolicyEffectiveDate, ;
        tPolicyExpirationDate AS tMaxPolicyExpirationDate ;
        FROM BigTable ;
        WHERE tPolicyExpirationDate IN (SELECT Max(tPolicyExpirationDate) FROM BigTable)

Not sure if the empty date format will work properly for you.

A couple of issues.

1. The SELECT in itself cannot guarantee that only one record will be returned for each of the unioned selects. That's a data driven issue, and you may have problems if multiple records have the same effective or expiration date.

2. The goal is essentially a cross tab result set. Within the constraints of a one line SQL statement, I don't know how do that. The above query returns two rows, assuming that there is only one record for each of the min and max values. Will that work for you?

Jay