Subject Re: SQL stop sorting at middle of table
From Keith <drkwchan@outlook.com>
Date Thu, 03 Jan 2019 08:49:59 -0500
Newsgroups dbase.getting-started

Mervyn Bick Wrote:

> On 2019-01-02 4:49 PM, Keith wrote:
>
> > Thx Ken,
> > When I execute your code.
> > The count() generates an error...File does not exist.
> >
> >
>
> Try running the attached program.
>
> If the Last_consult shows as 02/28/2018 you need to look very carefully
> at the contents of condate.
>
> Mervyn.
>
> clear
> select count(code) as Consults,min(condate) as First_consult,max(condate) as Last_consult from consult where condate between '01/01/2018' and '12/31/2018'
> list off
>
> select distinct code from consult where condate between '01/01/2018' and '12/31/2018'
> patients = 0
> scan
> patients ++
> endscan
> ? "Patients seen",patients
>
> select count(code) as No_of_visits,code from consult where condate between '01/01/2018' and '12/31/2018' group by code
> //? "Patients with multiple visits"  // uncomment if necessary
> extra_visits = 0
> scan
> if no_of_visits > 1
> //   ? code,no_of_visits //uncomment to see patients with extra visits
>     extra_visits += no_of_visits - 1
> endif
> endscan
> ? "Extra visits",extra_visits
>
> //If you want to see which patients had more than 1 visit
> //uncomment the two lines above
>
> // The following will give a list of consults/day.  This will be a LONG
> // list so uncomment the following lines only if you want this.
> // You may want to change the start date to '02/01/2018' as the problem
> // seems to be from March onwards.
>
> //? "Consults per day"
> //select count(code) as Visits_per_day,condate from consult where condate between '01/01/2018' and '12/31/2018' group by condate order by condate
> //list off
>
use

Thx Mervyn for your help.
Your codes generated the same result.
I check the content of Condate and found it is stored as "dd/mm/yyyy".
Would this affect the count?
Much appreciation for your help and as newbies I am studying your code line by line.
Best regards