All of that processing can be as easily done in the sproc using cursors and temptables. You then return the completed dataset to the program. Again, your standard doesn't fly.



>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,