🚀 go-pugleaf

Hey folks-

I'm having a problem (probably brain related) solving an
SQL 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
        IP_LNAME    Char 16
        IP_FNAME    Char 12
        IP_TYPE    Char 8
            IP_TYPE indicates which type of procedure each doc can
perform:
            If 'N' located anywhere in 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
            For the report I'm trying to generate here, I only want docs
that include 'N' in this field.

    NORPLANT.DBF (Norplant Insertion procedures)
        N_CLID    Char 10 (Provides link to clients in main app - used
for counting purposes here.)
        N_DOCVEN    Char 3 (links to IP_ID above to determine what doc
performed 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 that is 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
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 but still am 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?

HELP!!!!!!! (Or, just shoot me.)