Kevin,

>This going back and forth is completely meaningless.

>Post a full DDL and a listing of SPT, and what you're looking to generate.....IOW, give me a schema, your end result, and all relevant SQL code to go from DDL all the way to result set. If you want to post the code for that doctor's appointment result set that you talked about months ago, go ahead and post that.

I provided you with the appointment diary example months ago and never heard anything about it again. What guarrantee do I have?

Appart from that, this only one argument about using SPs VS SPT, which should not even be an issue for demonstrating: Any data munging you take off the server frees up resources from the server. Simple as that.

I can provide you with code and explanation, but can't provide you with DDL and example data. It would take me too many hours to do so.

You have asked me in the past to give me an example, and I did. You refused to do the hard work to prove your case for ADO.NET. Now you are asking me to prove my point? I think I pass on that one as I don't have any confidence in the outcome.

Even if you accept, we still are quarreling about the others.
But if you want a taste here it is. It defnately is not for the fainted hearts. It is not the complete story, I just compiled this from an existing project. I just don't have the time to spend a whole day on it.

Just notice that the raw data is retrieved from the database and then post processed. In the real application things are even more optimized, in the sense that when entering/deleting/changing a new appointment, not all data is retrieved from the database again, only the apointments.

The postprocessing is quite quick. The user does not notice a thing here, but it does not stress the server for doing this at the server and also reduces network trafic as in general the created amount of data is far largers the data retrieved from the server.

** Get the raw data from the SQL server first and then post process it 
** Get the rosters
SQLEXEC(Applic.SQLHandle, "SELECT M.*, lst_procedure "+;
	"FROM t_mainroster M LEFT JOIN t_list ON rst_procedure = lst_itemid "+;
	"WHERE rst_startdate <=?(dDateTo+1) AND rst_enddate >= ?dDate","Mainroster")

ConvertDatetimeToDate("MainRoster")

** Get blackout information
SQLEXEC(Applic.SQLHandle, "SELECT * FROM t_Blackouts WHERE blk_StartDate <=?dDateto AND blk_EndDate >= ?dDate  AND "+;
	"(blk_dr_id <> 0 OR blk_persfk <> 0)","Blk")

ConvertDatetimeToDate("blk")


** now get all doctors and resources that are active  in the given time period
** Yeah I know I can handle this bit TEXT .. ENDTEXT :)

cString1 = "SELECT DISTINCT CONVERT(char(80),'[R] '+cnt_option) AS source, (apt_dr_id*2)+1 AS ID, apt_dr_id AS Rst_dr_id, "+;
	"CAST(0 AS Int) AS Rst_persfk, CAST(2 AS int) AS Rst_sort, cnt_Color "+;
	"FROM t_apptment "+;
	 	"INNER JOIN s_contents ON apt_dr_id = cnt_content_id AND cnt_title_id = 400 "+;
	 	"WHERE Apt_appdate BETWEEN ?dDate AND ?(dDateTo+1) AND apt_dr_id <> 0 "+;
	"UNION "+;
	"SELECT DISTINCT CONVERT(char(80),'[R] '+cnt_option) AS source, (rst_dr_id*2)+1 AS ID, Rst_dr_id, "+;
	"CAST(0 AS Int) AS Rst_persfk, CAST(2 AS int) AS Rst_sort, cnt_Color "+;
	"FROM t_mainroster "+;
	 	"INNER JOIN s_contents ON rst_dr_id = cnt_content_id AND cnt_title_id = 400 "+;
	 	"WHERE rst_startdate <=?(dDateTo+1) AND (rst_enddate >= ?dDate OR rst_enddate < '19100101')"

cString2 = "SELECT DISTINCT CONVERT(char(80), RTRIM(prs_lastname)+', '+RTRIM(prs_firstname)+' '+prs_prefix) as Source, "+;
	"CONVERT(Int, rst_persfk*2) AS ID, CAST(0 AS Int) AS Rst_dr_id, Rst_persfk, CAST(1 as int) AS Rst_sort, "+;
	"cnt_color = CASE WHEN prs_color = 0 THEN CAST(16777215 as int) ELSE prs_color END "+;
	"FROM t_mainroster "+;
		"INNER JOIN t_persons ON prs_pk = rst_persfk "+;
		"WHERE rst_startdate <=?(dDateTo+1) AND (rst_enddate >= ?dDate OR rst_enddate < '19100101') "+;
	"UNION " +;
	"SELECT DISTINCT CONVERT(char(80), RTRIM(prs_lastname)+', '+RTRIM(prs_firstname)+' '+prs_prefix) as Source, "+;
	"CONVERT(Int, apt_persfk*2) AS ID, CAST(0 AS Int) AS Rst_dr_id, apt_Persfk AS Rst_persfk, CAST(1 as int) AS Rst_sort, "+;
	"cnt_color = CASE WHEN prs_color = 0 THEN CAST(16777215 as int) ELSE prs_color END "+;
	"FROM t_apptment "+;
	 	"INNER JOIN t_Persons ON apt_persfk = prs_pk "+;
	 	"WHERE Apt_appdate BETWEEN ?dDate AND ?(dDateTo+1) AND apt_persfk <> 0 "

		
SQLEXEC(applic.SqlHandle, cString1, "Res1")
SQLEXEC(applic.SqlHandle, cString2, "Res2")

** all doctors and resources in one list. To make a distinction between a doctor and resource, ;
the doctor is an even number, a resource and odd numbers. their original PK can be determine by dividing by it by two

SELECT * FROM Res1 UNION SELECT * FROM res2 INTO CURSOR Doctors 

** Get the parameters for the views and queries
nDr_id = IIF(doctors.rst_sort=2 AND THISFORM.nView # 2, doctors.rst_dr_id,0)
nPersfk= IIF(doctors.rst_sort=1 AND THISFORM.nView # 2, doctors.rst_persfk,0)
REQUERY("apptlist")
REQUERY("Apptnotes")


** All data is there now do the datamunging.
** Now depending on the selected layout of the view, mung it into the appropriate form for display and reporting.

DO CASE
	CASE THISFORM.nView=1
		THISFORM.GetDailyDrView(dDate)


	CASE THISFORM.nView=2
		THISFORM.GetDailyView(dDate)
		
	CASE THISFORM.nView=3
		SELECT apptlist
		THISFORM.Getweeklyview(dDate)

	CASE THISFORM.nView=4
		SELECT apptlist
		THISFORM.Get4weeklyview(dDate)
	
ENDCASE


*-

** An example of the GetWeeklyview method

FUNCTION Getweeklyview
LPARAMETERS dDate

** Create the resultset.
IF !USED("WeeklyView")
	cCommand = "CREATE CURSOR WeeklyView (Time C(5), DispTime C(10)"
	FOR nT = 1 TO 7
		cPrefix="_"+ALLTRIM(STR(nT))
		cCommand = cCommand + ", "+cPrefix+"_Patient C(50), "+cPrefix+"_pk I, "+cPrefix+"_Color I, "+cPrefix+"_Style N(1)"
	ENDFOR
	cCommand = cCommand + ")"
	SELECT 0
	&cCommand
ELSE
	SELECT WeeklyView
	ZAP
ENDIF
INDEX ON Time TAG cTime


** Now insert all timeslots as specified by the roster. Each roster can create multiple records in the resultset.
** See DO WHILE
FOR nT = 1 TO 7 && days of the week
	SELECT Mainroster
	cWhere = IIF(doctors.rst_sort=1, "rst_persfk = doctors.rst_persfk", "Rst_dr_id = Doctors.Rst_dr_id") + ;
		" AND Rst_"+GETWORDNUM("mon Tues Wednes Thurs fri Satur sun",DOW(dDate+nT-1,2))+"Day"

	SELECT MainRoster
	SCAN FOR rst_Timeslot # 0 AND BETWEEN(dDate+nT-1,rst_startdate,rst_enddate) AND &cWhere

		tStart=CTOT(DTOC(dDate) +" "+ Mainroster.rst_StartTime)
		tEnd=CTOT(DTOC(dDate) +" "+ Mainroster.rst_EndTime)
		tTime=tStart

		DO WHILE tTime < tEnd
			cTime = TTOC(tTime,2)
			IF !SEEK(cTime,"WeeklyView")
				INSERT INTO WeeklyView (Time) VALUES (cTime)
			ENDIF
			tTime=tTime + MainRoster.rst_Timeslot * 60
		ENDDO
	ENDSCAN
ENDFOR
THISFORM.RefreshWeeklyView(dDate, "Weeklyview", 7)

** Code to populate grid etc...


FUNCTION Refreshweeklyview

LPARAMETERS dDate, cAlias, nDays
LOCAL nSec, nT, cFieldsList


cAlias = EVL(cAlias, "WeeklyView")
nDays = EVL(nDays, 7)

#DEFINE STYLE_NOROSTER 0
#DEFINE STYLE_APPOINTMENT 1
#DEFINE STYLE_BLACKOUT 2
#DEFINE STYLE_AVAILABLE 3
#DEFINE STYLE_MEETING 4
#DEFINE STYLE_BLOCKED 5
#DEFINE STYLE_OBSOLETE 6
#DEFINE STYLE_DOUBLEBOOK 7
#DEFINE STYLE_ADDEDTIMESLOT 8


#DEFINE COLOR_NOROSTER THISFORM.BackColor
#DEFINE COLOR_APPOINTMENT RGB(255,255,223)
#DEFINE COLOR_BLACKOUT RGB(255,0,0)
#DEFINE COLOR_AVAILABLE RGB(255,255,255)
#DEFINE COLOR_MEETING RGB(128,255,128)
#DEFINE COLOR_BLOCKED RGB(192,0,0)
#DEFINE COLOR_OBSOLETE THISFORM.BackColor
#DEFINE COLOR_ADDEDTIMESLOT RGB(255,255,223)

LOCAL aColors[9]
aColors[STYLE_NOROSTER+1] = COLOR_NOROSTER
aColors[STYLE_APPOINTMENT+1] = COLOR_APPOINTMENT
aColors[STYLE_BLACKOUT+1] = COLOR_BLACKOUT
aColors[STYLE_AVAILABLE+1] = COLOR_AVAILABLE
aColors[STYLE_MEETING+1] = COLOR_MEETING
aColors[STYLE_BLOCKED+1] = COLOR_BLOCKED
aColors[STYLE_OBSOLETE+1] = COLOR_OBSOLETE
aColors[STYLE_ADDEDTIMESLOT+1] = COLOR_ADDEDTIMESLOT

nSec = SECONDS()
SELECT 0
USE (DBF(cAlias)) AGAIN ALIAS Weekly2 ORDER 1

cFieldList=""
FOR nT = 2 TO FCOUNT()
	cFieldList = cFieldList + IIF(!EMPTY(cFieldList),", ","")+FIELD(nT)
ENDFOR
BLANK FIELDS &cFieldList ALL

** Scan trough all appointments and insert them into Weekly2.
SELECT Apptlist
SCAN FOR !EMPTY(apt_apptime) AND (!EMPTY(Apptlist.apt_dr_id) OR !EMPTY(Apptlist.apt_persfk)) AND INLIST(apt_status, 0, 1, 2, 4)
	cPrefix = "_"+ALLTRIM(STR(apt_appdate - dDate + 1))
	cPkColumn = cPrefix+"_pk"
	
	SELECT Weekly2
	SEEK Apptlist.apt_apptime 
	LOCATE WHILE Time = STRTRAN(Apptlist.apt_apptime,".",":") FOR EMPTY(&cPkColumn) OR EVALUATE(cPrefix+"_Style") = 8
	
	IF !FOUND()
		** The timeslot was not available yet, so insert a new timeslot
		INSERT INTO Weekly2 (Time) VALUES (STRTRAN(Apptlist.apt_apptime,".",":"))
	ENDIF

	** Misusing the color column to store the lenght of the appointment
	** in order to, later when all appointments are stored, set all rows falling
	** within the timeframe of the appointment to the correct values.
	
	DO CASE
		CASE apptlist.apt_Acu_id = "meet"
			** A meeting as specified in the appointment table
			REPLACE (cPkColumn) WITH apptlist.apt_pk,;
				(cPrefix+"_Patient") WITH GetCaption("PATCHLABELS.MEETING"),;
				(cPrefix+"_Color") WITH IIF(Apptlist.apt_wholeday, -1, MAX(apptlist.apt_Length,1)),;
				(cPrefix+"_Style") WITH STYLE_MEETING
		
		CASE apptlist.apt_Acu_id = "black"
			** A blackout as specified in the appointment table
			REPLACE (cPkColumn) WITH apptlist.apt_pk,;
				(cPrefix+"_Patient") WITH GetCaption("CAPTION.BLOCKED"),;
				(cPrefix+"_Color") WITH IIF(Apptlist.apt_wholeday, -1, MAX(apptlist.apt_Length,1)),;
				(cPrefix+"_Style") WITH STYLE_BLOCKED
		
		CASE apptlist.apt_Acu_id = "roster"
			** a roster change as specified in the appointments table.
			tStart=CTOT(DTOC(dDate) +" "+ Apptlist.apt_apptime)
			tEnd= tStart + 300 * Apptlist.apt_length
			tTime=tStart

			SELECT list
			LOCATE FOR ItemId = apptlist.apt_itemid
			SELECT Weekly2

			DO WHILE tTime < tEnd
				cTime = LEFT(TTOC(tTime,2),5)
				DO CASE
					CASE !SEEK(cTime,"Weekly2")
						INSERT INTO Weekly2 (Time, (cPkColumn), (cPrefix+"_Style") ) ;
							VALUES (cTime, apptlist.apt_pk, 8)

					CASE EMPTY(NVL(EVALUATE("Weekly2."+cPkColumn),0))
						REPLACE (cPkColumn) 		WITH  apptlist.apt_pk,;
								(cPrefix+"_Style") 	WITH 8 ;
								IN Weekly2
				ENDCASE
				tTime=tTime + MAX(apptlist.apt_roster_id,5) * 60
			ENDDO
			= SEEK(TTOC(tStart,2),"Weekly2")
			REPLACE	(cPrefix+"_Color") WITH IIF(Apptlist.apt_wholeday, -1, MAX(apptlist.apt_Length,1)) IN weekly2

		CASE apptlist.apt_Acu_id = "notes"
			** notes for today
			** do nothing as this has no impact in the timeslots

		OTHERWISE
			** A regular appointment
			IF THISFORM.Showproc 
				SELECT list && remote view, loaded only once
				LOCATE FOR ItemId = apptlist.apt_itemid
				SELECT Weekly2

				cPatient=	IIF(EMPTY(Apptlist.apt_arrtime),"","*")+;
							ALLTRIM(List.Procedure)+;
							IIF(EMPTY(Apptlist.apt_Confirmed),""," +")
			ELSE
				
				cPatient=	IIF(EMPTY(Apptlist.apt_arrtime),"","*")+;
							Applic.GetDemoName(apptlist.apt_Acu_id)+;
							IIF(EMPTY(Apptlist.apt_Confirmed),""," +")
			ENDIF
			
			REPLACE (cPkColumn) WITH apptlist.apt_pk,;
				(cPrefix+"_Patient") WITH cPatient,;
				(cPrefix+"_Color") WITH MAX(apptlist.apt_Length,1),;
				(cPrefix+"_Style") WITH STYLE_APPOINTMENT
	ENDCASE
ENDSCAN


** If the view is empty, just insert one record (9:00 to be able to add appointments)
IF RECCOUNT("Weekly2") = 0
	INSERT INTO Weekly2 (Time) VALUES ("09:00")
ENDIF

** Mark all timeslots within each roster as avaliable, except for those that have been occupied by the ;
** Appointments

nColor=THISFORM.BackColor
SELECT Mainroster
nDr_id= VAL(THISFORM.Doctorid)

FOR nT = 1 TO nDays
	cWhere = IIF(doctors.rst_sort=1, "rst_persfk = doctors.rst_persfk", "Rst_dr_id = Doctors.Rst_dr_id") + ;
		" AND Rst_"+GETWORDNUM("monday Tuesday Wednesday Thursday friday Saturday sunday",DOW(dDate+nT-1,2))

	SCAN FOR rst_Timeslot # 0 AND BETWEEN(dDate+nT-1, rst_StartDate, rst_EndDate) AND &cWhere
	
		cPrefix = "_"+ALLTRIM(STR(nT))
		cColumn = cPrefix+"_Style"
		cColumn2= cPrefix+"_Patient"
		
		REPLACE ALL (cColumn) WITH STYLE_AVAILABLE, ;
					(cColumn2) WITH NVL(mainroster.lst_procedure,"") ;
			FOR time >= Mainroster.rst_StartTime AND time < Mainroster.rst_EndTime ;
				AND EMPTY(&cColumn) IN Weekly2
	ENDSCAN
ENDFOR

** Get the blackout data and apply them to the roster
SELECT Blk
FOR nT = 1 TO nDays
	cWhere = IIF(doctors.rst_sort=1, "blk_persfk = doctors.rst_persfk", "blk_dr_id = Doctors.Rst_dr_id")+;
		" AND blk_"+GETWORDNUM("Mon Tue Wed Thu Fri Sat Sun",DOW(dDate+nT-1,2))
		
	SCAN FOR BETWEEN(dDate+nT-1, blk.blk_StartDate, blk.blk_EndDate) AND &cWhere
	
		cPrefix = "_"+ALLTRIM(STR(nT))
		cColumn = cPrefix+"_Style"

		REPLACE ALL (cColumn) WITH STYLE_BLACKOUT ;
			FOR Blk.Blk_allday OR (time >= blk.blk_StartTime AND time < blk.blk_EndTime) ;
				AND !EMPTY(&cColumn) IN Weekly2
	ENDSCAN
ENDFOR

** Mark subsequent timeslots also used for an appointment or blackout.
STORE 0 TO nRed, nGreen, nBlue
=RGBComp(Doctors.cnt_color ,@nRed, @nGreen, @nBlue)
nDimColor=RGB(.75 * nRed, .75*nGreen, .75*nBlue)  && create a dim colour for overlapping appointments
aColors[STYLE_DOUBLEBOOK+1]=nDimColor

FOR nT = 1 TO nDays

	SELECT Weekly2
	cPrefix = "_"+ALLTRIM(STR(nT))
	cColumn = cPrefix+"_pk"

	LOCATE FOR !EMPTY(&cColumn)
	DO WHILE !EOF()
		nLength = EVALUATE(cPrefix+"_Color")
		cEndtime = TTOC(CTOT(DTOC(DATE())+" "+Time)+nLength * 300,2)

		nStyle = IIF(EVALUATE(cPRefix+"_Style")=STYLE_DOUBLEBOOK, STYLE_APPOINTMENT, EVALUATE(cPrefix+'_Style'))
		nPk = EVALUATE(cPrefix+"_Pk")
		
		SKIP
		nRecNo = IIF(EOF(),0,RECNO())

		** Make sure that following timeslots during the apointment are marked as belongin to the appointment
		IF nLength = -1
			** Whole day for blocked and meetings
			cCommand = 'REPLACE '+cPRefix+'_Style WITH nStyle, '+;
					cPrefix+'_Pk WITH nPk, '+;
					cPrefix+'_Patient WITH "..." '+;
					'WHILE .T. FOR INLIST('+cPrefix+'_Style, 0, 3,8) OR (INLIST('+cPrefix+'_Pk, nPk, 0) OR '+;
					cPrefix+'_Patient = "...")'
					
		ELSE
			** Appointments/blocked timeslots/meetings that have a limited lenght.
			cCommand = 'REPLACE '+cPRefix+'_Pk WITH IIF(INLIST('+cPrefix+'_Pk, nPk, 0) OR '+cPrefix+;
					'_Patient = "..." OR '+cPrefix+'_Style = 8,nPk, '+cPrefix+'_pk),'+;
					cPrefix+'_Style WITH IIF(INLIST('+cPrefix+'_Style, 1,8) and nStyle <> 8, IIF(nStyle = 1 and '+cPrefix+'_Style = 1,7, 1), '+;
					'IIF(INLIST('+cPrefix+'_Pk, nPk, 0) OR '+cPRefix+'_Patient = "...", nStyle, '+;
					cPRefix+'_Style)), '+;
					cPrefix+ '_Patient WITH IIF(nStyle <> 8 AND (INLIST('+cPrefix+'_Pk, nPk, 0) OR '+cPrefix+'_Patient = "..."), '+;
					'IIF('+cPrefix+'_Patient="...","----- double booked -----","..."), '+cPrefix+'_Patient) '+ ;
					'WHILE Time < cEndTime'
		ENDIF
		&cCommand

		IF nRecno # 0
			GO nRecNo
		ENDIF
		cPk = cPrefix+"_pk"
		LOCATE FOR !INLIST(&cPk, 0, nPk) REST
	ENDDO
	cCommand = 'REPLACE ALL '+cPrefix+'_Color WITH IIF('+cPRefix+'_Style = 1, Doctors.cnt_color ,aColors['+;
			cPrefix+'_style+1]),'+cPrefix+'_Patient WITH IIF('+cPrefix+'_Patient = "...","",'+cPrefix+'_Patient)'

	&cCommand
ENDFOR

** display the time in 12 hour format (AM/PM)

SELECT Weekly2

IF Settings.Hourformat = 12
	SET HOURS TO 12
ELSE
	SET HOURS TO 24
ENDIF
REPLACE ALL DispTime WITH TTOC(CTOT(DTOC(DATE())+" "+STRTRAN(Time,".",":")),2) IN Weekly2
SET HOURS TO 24
USE IN Weekly2
RETURN