|
Home > Archive > dBASE Programming > October 2006 > SQL question
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Charles 2006-10-12, 7:25 pm |
| What can I add to the following code to restrict the adjustme.dated fields to be within date()-90 and date()-730???
SELECT DISTINCT Adjustme.PT_NUMBER, Patients.PT_NUMBER, Adjustme.DATED, Patients.FIRST_NAME, Patients.LAST_NAME, Patients.ADDRESS1, Patients.CITY, Patients.STATE, Patients.ZIP, Patients.PHONE
FROM "C:\patientmaster\adjustme.dbf" Adjustme
INNER JOIN "C:\patientmaster\patients.dbf" Patients
ON (Adjustme.PT_NUMBER = Patients.PT_NUMBER)
ORDER BY Adjustme.DATED, Adjustme.pt_NUMBER DESC
Charles
| |
| Ivar B. Jessen 2006-10-12, 7:25 pm |
| On Thu, 12 Oct 2006 13:57:34 -0400, in dbase.programming,
Subject: SQL question,
Message-ID: <uBRSlfi7GHA.1772@news-server>,
Charles <grahamcp@journey.com> wrote:
>What can I add to the following code to restrict the adjustme.dated fields to be within date()-90 and date()-730???
>
>SELECT DISTINCT Adjustme.PT_NUMBER, Patients.PT_NUMBER, Adjustme.DATED, Patients.FIRST_NAME, Patients.LAST_NAME, Patients.ADDRESS1, Patients.CITY, Patients.STATE, Patients.ZIP, Patients.PHONE
>FROM "C:\patientmaster\adjustme.dbf" Adjustme
> INNER JOIN "C:\patientmaster\patients.dbf" Patients
> ON (Adjustme.PT_NUMBER = Patients.PT_NUMBER)
WHERE Adjustme.DATED BETWEEN :d90 and :d730
>ORDER BY Adjustme.DATED, Adjustme.pt_NUMBER DESC
PARAMS["d90"] = date()-90
PARAMS["d730"] = date()-730
Ivar B. Jessen
| |
| Sébastien de Breuck 2006-10-12, 7:25 pm |
| Charles Wrote:
> What can I add to the following code to restrict the adjustme.dated fields to be within date()-90 and date()-730???
>
> SELECT DISTINCT Adjustme.PT_NUMBER, Patients.PT_NUMBER, Adjustme.DATED, Patients.FIRST_NAME, Patients.LAST_NAME, Patients.ADDRESS1, Patients.CITY, Patients.STATE, Patients.ZIP, Patients.PHONE
> FROM "C:\patientmaster\adjustme.dbf" Adjustme
> INNER JOIN "C:\patientmaster\patients.dbf" Patients
> ON (Adjustme.PT_NUMBER = Patients.PT_NUMBER)
> ORDER BY Adjustme.DATED, Adjustme.pt_NUMBER DESC
>
Charles
If it's in a apllication, work with variables like :
DateFrom = date()-270
DateTo = date()-90
q = new query()
q.sql = [SELECT DISTINCT Adjustme.PT_NUMBER, Patients.PT_NUMBER, Adjustme.DATED, Patients.FIRST_NAME, Patients.LAST_NAME, Patients.ADDRESS1, Patients.CITY, Patients.STATE, Patients.ZIP, Patients.PHONE
FROM "C:\patientmaster\adjustme.dbf" Adjustme
INNER JOIN "C:\patientmaster\patients.dbf" Patients
ON (Adjustme.PT_NUMBER = Patients.PT_NUMBER)
WHERE Adjustme.DATED between '] + Datefrom + [' and '] + DateTo+ ['
ORDER BY Adjustme.DATED, Adjustme.pt_NUMBER DESC]
This wiill work.
If you just need, a sql, put your SQl in a program like :
DateFrom = date()-270
DateTo = date()-90
SELECT DISTINCT Adjustme.PT_NUMBER, Patients.PT_NUMBER, Adjustme.DATED, Patients.FIRST_NAME, Patients.LAST_NAME, Patients.ADDRESS1, Patients.CITY, Patients.STATE, Patients.ZIP, Patients.PHONE
FROM "C:\patientmaster\adjustme.dbf" Adjustme
INNER JOIN "C:\patientmaster\patients.dbf" Patients
ON (Adjustme.PT_NUMBER = Patients.PT_NUMBER)
WHERE Adjustme.DATED between :Datefrom and :DateTo
ORDER BY Adjustme.DATED, Adjustme.pt_NUMBER DESC
brow
You will need to check your date format before (set date to AMRICAN)
Regards
Sébastien
| |
| Charles 2006-10-13, 1:28 am |
| Thank you!
Ivar B. Jessen Wrote:
> On Thu, 12 Oct 2006 13:57:34 -0400, in dbase.programming,
> Subject: SQL question,
> Message-ID: <uBRSlfi7GHA.1772@news-server>,
> Charles <grahamcp@journey.com> wrote:
>
> WHERE Adjustme.DATED BETWEEN :d90 and :d730
> PARAMS["d90"] = date()-90
> PARAMS["d730"] = date()-730
>
>
> Ivar B. Jessen
| |
| Charles 2006-10-13, 1:28 am |
| Thank you!
Sébastien de Breuck Wrote:
> Charles Wrote:
>
>
> Charles
>
> If it's in a apllication, work with variables like :
> DateFrom = date()-270
> DateTo = date()-90
> q = new query()
> q.sql = [SELECT DISTINCT Adjustme.PT_NUMBER, Patients.PT_NUMBER, Adjustme.DATED, Patients.FIRST_NAME, Patients.LAST_NAME, Patients.ADDRESS1, Patients.CITY, Patients.STATE, Patients.ZIP, Patients.PHONE
> FROM "C:\patientmaster\adjustme.dbf" Adjustme
> INNER JOIN "C:\patientmaster\patients.dbf" Patients
> ON (Adjustme.PT_NUMBER = Patients.PT_NUMBER)
> WHERE Adjustme.DATED between '] + Datefrom + [' and '] + DateTo+ ['
> ORDER BY Adjustme.DATED, Adjustme.pt_NUMBER DESC]
>
> This wiill work.
> If you just need, a sql, put your SQl in a program like :
>
> DateFrom = date()-270
> DateTo = date()-90
> SELECT DISTINCT Adjustme.PT_NUMBER, Patients.PT_NUMBER, Adjustme.DATED, Patients.FIRST_NAME, Patients.LAST_NAME, Patients.ADDRESS1, Patients.CITY, Patients.STATE, Patients.ZIP, Patients.PHONE
> FROM "C:\patientmaster\adjustme.dbf" Adjustme
> INNER JOIN "C:\patientmaster\patients.dbf" Patients
> ON (Adjustme.PT_NUMBER = Patients.PT_NUMBER)
> WHERE Adjustme.DATED between :Datefrom and :DateTo
> ORDER BY Adjustme.DATED, Adjustme.pt_NUMBER DESC
> brow
>
> You will need to check your date format before (set date to AMRICAN)
>
> Regards
>
> Sébastien
|
|
|
|
|