🚀 go-pugleaf

I've removed my original message and re-issued this one
due to an error message I received on my last attempt.
Please forgive me if this gets posted twice...

Hey folks-

I'm having a problem (probably brain related) solving
an SQL COUNT issue in VDB7.

Here's what I have:

    m_begdate (Report Start Date)
    m_enddate (Report Ending Date)

    IND_PROV.DBF (Doctors)
        IP_ID    Char 3
            Unique provider ID.
        IP_LNAME    Char 16
            Last Name
        IP_FNAME    Char 12
            First Name
        IP_TYPE    Char 8
            *Indicates type of procedure each doc
             can perform:
            *If 'N' located anywhere in this field
             the doc can perform Norplant insertions
            *If 'S' located anywhere in this field
             the doc can perform Sterilization procedures etc...
            *Some docs can perform all procedure types
             and would have 'ANSOPRUI' entered here
            *IN THIS CASE, I only want to count docs
             that include an 'N' in this field.

    NORPLANT.DBF (Norplant Insertion procedures)
        N_CLID    Char 10
            Provides link to clients in main app.
            Used only for counting purposes here.
        N_DOCVEN    Char 3
            Links to IP_ID above to determine which
            doc performed a client's Norplant Insertion.
        N_PRODATE    Date
            Date the procedure was performed.
            Could be blank if procedure applied for
            by client but not yet completed by doc.

Here's what I need:

I need an ACCURATE (sorry to be so picky) result set
comprised of the following columns and criteria (Can
this be done with a single SQL statement?):

IP_ID   IP_LNAME    IP_FNAME    COUNT OF N_CLID
-----   ----------  ----------  ---------------
001     Doe         John              3
002     Smith       Jane              0
003     Doctor      Another           1


Criteria:

    I need the counts to include only NORPLANT.DBF
    records where (counting on N_CLID):

    N_PRODATE is BETWEEN 'm_begdate' and 'm_enddate'
    inclusive.

    The result set must include ALL docs that can
    perform 'N' type procedures (Even if no procedure
    has been performed during the specified timeframe.
    If this is the case, a count of zero must be
    returned for each doc.


Here's what I've tried:

PROVIDERS.PRG
    SELECT Provider.IP_ID, Provider.IP_LNAME, Provider.IP_FNAME,
COUNT(Norplant_Procedures.N_CLID ) ;
        FROM "ind_prov.dbf" Provider ;
           LEFT OUTER JOIN "Norplant Procedures.SQL" Norplant_Procedures
;
        ON  (Provider.IP_ID = Norplant_Procedures.IP_ID) ;
        WHERE (Norplant_Procedures.N_PRODATE BETWEEN :m_begdate AND
:m_enddate) OR ;
            (Norplant_Procedures.N_PRODATE IS NULL) ;
        GROUP BY Provider.IP_ID, Provider.IP_LNAME, Provider.IP_FNAME ;
        ORDER BY Provider.IP_LNAME, Provider.IP_FNAME

which needs:

NORPLANT PROCEDURES.SQL
    SELECT Ind_prov.IP_ID, Norplant.N_PRODATE, Norplant.N_CLID
    FROM "ind_prov.dbf" Ind_prov
       LEFT OUTER JOIN "norplant.dbf" Norplant
       ON  (Ind_prov.IP_ID = Norplant.N_DOCVEN)
    WHERE  Ind_prov.IP_TYPE LIKE '%N%'

I've only done it this way for testing purposes and I'm
still not quite getting the results I need.  Some
providers being returned do not even have an 'N' in
the IP_TYPE field. Also, once I do get the correct
results, how do I reference the second query from the
first in my report?  Simply reference the object by name?

Sorry if I've given more information here than is needed.
I just wanted to make sure you had the whole picture.

Thanks for your assistance in advance.

- Craig