As you've heard probably ton's of times before:

Find the upper boss of an employee

USE Employee ORDER TAG emd_id
DO WHILE KEYMATCH("Employess.Boss_id") AND SEEK("Employess.Boss_id")
ENDDO
RETURN Employee.emp_id

I know it is possible in YUKON, but not in oracle neither, in SQL 2000 in a simple SQL statment.
Now see what you come up with in SQL-server/.NET

But again, you insist to compare VFP/DBF vs .NET/SQL which is not a fair comparison. Try to compare VFP/SQL vs .NET/SQL. You can't stand losing can't you ?

A real world example: Only a few tables listed here to give you an idea.

Table appointments
-Appointmenttype
-Personid
-AppointmentRoom
-doctorid
-Date
-Time
-endTime
-Procedure
-...

Table Doctor_Appointmentroster
-Startdate
-EndDate
-DoctorId
-TimeStart
-TimeEnd
-TimeSlotLength
-...

Table Blackout
-Doctorid
-Room
-DateFrom
-TimeFrom
-Dateto
-TimeTo
-Reason
-...

Table Persons
-Id
-Firstname
-Prefix
-Lastname
-...

Objective1: Create an overview of all appointments of one given day for one doctor, displaying its roster, the appointments, the procedure, the room in which it is held and the blackouts.

Objective2: Create an overview for one day listing all appointments of all doctors (Time on Y axis and doctors on X axis)

Objective3: Create an overview for multiple days listing all appointments of one doctor (Time on Y axis and dates on X axis)

VFP solution
(Not providing complete nor function code, but direction how to solve this)


Objective1
* Get the appointments for today from the SQL server
* Get the blackouts for today from the SQL server
* Get the roster(s) for the selected doctor from the SQL Server
* Get all the persons who have an appointment for today

CREATE CURSOR ApptOverview (Time, Apptype, Person, Procedure, blackoutflag,...)
SET CARRY ON
INDEX ON Time TAG Time

SELECT Blackout
INDEX ON Time TAG Time
SET NEAR ON

SELECT Doctor_Appointmentroster
SCAN
cTime = Roster.Time
DO WHILE cTime < Doctor_Appointmentroster
SEEK cTime IN BlacKout
** Set near is on, so finds next large item if no match
INSERT INTO ApptOverView (Time, Blackoutflag) ;
VALUES(cTime, BETWEEN(cTime,Blackouts.timeFrom, Blackouts.TimeTo))
cTime = TTOC(CTOT(CTOD(DATE())+" : "+cTime)+ TimeSlotLength ,2)
ENDDO
ENDSCAN

SELECT Appointments
SCAN
lFirst = .T.
ctime = Appointments.Time
SEEK cTime IN ApptoverView
DO WHILE ApptOverview.Time >= Appointments.EndTime
DO CASE
CASE lFirst AND EOF("Apptoverview") && Timeslot does not exist in roster, so append.
SEEK cTime IN BlacKout
** Set near is on, so finds next large item if no match
INSERT INTO ApptOverView (Time, Blackoutflag, person, procedure) ;
VALUES(cTime, BETWEEN(cTime,Blackouts.timeFrom, Blackouts.TimeTo), ;
appointments.Person, appointments.procedure)

CASE !EMPTY(PErson)
** Timeslot has been taken by another patient/appointment (Double booking)
** So add another duplicate slot, with append blank (SET CARRY = ON)
INSERT INTO ApptOverView (person, procedure) ;
VALUES(appointments.Person, appointments.procedure)

OTHERWISE
** The slot is still empty, so occupy it
REPLACE Person WITH Appointments.Person, Procedure WITH Appointments.procedure
ENDCASE
lFirst = .F. (We don't care for intermediate slots)
SKIP IN ApptOverView
ENDDO
ENDSCAN

SELECT *, Firstname, Lastname ;
FROM Apptoverview ;
LEFT JOIN Persons ON person = ID ;
INTO CURSOR Result

THISFORM.AppointmentGrid".RecordSource = "Result"


I'm sure you get the hint at objective 2 and 3.

This, my fried, is what we call data munging. Trying to do this in .NET requires much, much more than this as there is no SEEK equivalent (No, you can't use a PK here, since it might have duplicate keys for doublebooking) and also no SET NEAR and SET CARRY on functionality, and you've got to poke arround with collections in ado to search for records and insert records at certain positions.

Also notice that this solution optimizes bandwidth, because only the neccesary data is downloaded from the server. When doing all the processing on the server you'd probably end up with way more data to be downloaded into a cursor.

Walter,