🚀 go-pugleaf

Craig,

the SQL that you want should look something like this (the memory variables
are preceded with a ':')

Select * From "sterile.dbf" s, "client.dbf" c ;
   Where c.client_id = s.client_id and ;
         s.st_prodate between :m_begdate and :m_enddate and ;
         s.st_dt_con - c.cl_dob >= (21*365.25) and ;
         s.st_prodate- s.st_dt_con > 29 and ;
         s.st_stat = "1"

The first part of the Where clause is your join, I just guessed at client_id,
substitute whatever the appropriate fields are for your tables.  For the three
criteria for the st_prodate a 'between' can be used for simplification.

If you migrate this report to a server based database you may encounter
problems with 'st_dt_con - c.cl_dob >= (21*365.25)' as dates and intervals
can be handled altogether differently.	HTH.

Cheers,

Sean

In article <35B1A9AF.8788721@access.mountain.net>,
  Craig Cohernour <cohe106w@access.mountain.net> wrote:
> I'm extremely new to this fancy SQL stuff so please be patient ....
>
> I'm trying to set up a report in which the query object/statement needs
> to reference dates stored in memory variables.  My specific problem is
> this:
>
> The variables are public and are being picked up on a form just prior to
>
> running the report.
>
> The relationship between the two tables involved seems great, I simply
> don't know how to reference the memory variable in the sql statement or
> in
> the visual sql tools.
>
> Definitions:
>     m_begdate (variable) = Starting date for record inclusion.
>         (date-type)
>
>     m_enddate (variable) = Ending date for record inclusion.
>         (date-type)
>
>     m_progid (variable) = Program identification number
>         (2digit/character-type data.)
>
>     st_prodate (field) = Date of medical procedure.
>         (Sterilization table/date-type)
>
>     st_dte_con (field) = Date of consent signature.
>         (Sterilization table/date-type)
>
>     st_stat (field) = Status of the current application for this client.
>
>         (Sterilization table/character type)
>             Samples: 1-Active, 2-Cancelled, 3-Denied, 4-Expired
>
>     cl_dob (field) = Client's date of birth.
>         (Client table/date-type)
>
> The criteria that I need help setting up an SQL
> statement for is (or preferably using the visual tools):
>
>     STERILE->ST_PRODATE must not be blank.
>     STERILE->ST_PRODATE must be equal to or greater than M_BEGDATE.
>     STERILE->ST_PRODATE must be equal to or less than M_ENDDATE.
>     STERILE->ST_DTE_CON minus CLIENT->CL_DOB must be equal to or greater
> than (21*365.25).
>     STERILE->ST_PRODATE minus STERILE->ST_DTE_CON must be greater than
> 29. (30 or more)
>     STERILE->ST_STAT must equal "1".
>
> Can you help me?
>
> Thanks,
> Craig
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/rg_mkgrp.xp   Create Your Own Free Member Forum