/* For paging, we retrieve the selected UserIDs, in order, into a temporary table  */
CREATE TABLE #TempRowLookup (
	RowID INT IDENTITY(0,1) PRIMARY KEY, -- We can retrieve any sequence of ordered rows for a specific page using RowID
	UserID	INT 
)

/* We don't need records beyond the end of page */
DECLARE	@RowLimit INT
SET	@RowLimit = @PageSize * (@CurrentPage + 1)
SET	ROWCOUNT @RowLimit

INSERT	INTO #TempRowLookup (UserID)
	SELECT	U.UserID
		FROM dbo.Users U 
		LEFT OUTER JOIN dbo.CourseRecords CR
				ON	CR.UserID = U.UserID 
				AND	CR.CourseID = @CourseID
				AND	CR.CRecordFinish BETWEEN @CompleteStart AND @CompleteFinish
		LEFT OUTER JOIN dbo.Courses CS ON CS.CourseID = CR.CourseID
		LEFT OUTER JOIN dbo.Branches B on U.BranchID=B.BranchID 
		LEFT OUTER JOIN dbo.Centers C on U.CenterID=C.CenterID 
	
		WHERE	U.ClientID = @ClientID
		AND	(@Email IS NULL OR Email LIKE @Email)
		AND	(@Firstname IS NULL OR Firstname LIKE @Firstname)
		AND	(@Middlename IS NULL OR Middlename LIKE @Middlename)
		AND	(@Lastname IS NULL OR Lastname LIKE @Lastname)
		AND	(@Username IS NULL OR Username LIKE @Username)
		AND	(@EmployeeType IS NULL OR EmployeeType LIKE @EmployeeType)
		AND	(@CenterID IS NULL OR U.CenterID = @CenterID OR U.CenterID = 0)
		AND	(@BranchID IS NULL OR U.BranchID = @BranchID)
		AND	(@Wildcard IS NULL OR B.Branchname LIKE @Branchname)
		AND	(@Offline IS NULL OR U.Offline = @Offline)
		AND	(@InactiveMask IS NULL OR (U.Inactive & @InactiveMask <> 0))
		AND	(@Completed IS NULL 
			OR (@Completed=0 AND CRecordFinish IS NULL) 
			OR (@Completed=1 AND CRecordFinish BETWEEN @CompleteStart AND @CompleteFinish) 
			) 
		AND	(@Acknowledge IS NULL 
			OR (@Acknowledge=1 AND CRAcknowledge=1) 
			OR (@Acknowledge=0 AND (CRAcknowledge = 0 or CRAcknowledge = -1)))
	
		ORDER BY 
			CASE	WHEN	@Order1 = 'Username'		THEN Username
				WHEN	@Order1 = 'Firstname'		THEN Firstname
				WHEN	@Order1 = 'Middlename'		THEN Middlename
				WHEN	@Order1 = 'Lastname'		THEN Lastname
				WHEN	@Order1 = 'EmployeeType'	THEN EmployeeType
				WHEN	@Order1 = 'Centername'		THEN Centername
				WHEN	@Order1 = 'Branchname'		THEN Branchname
				WHEN	@Order1 = 'Email'		THEN Email
				WHEN	@Order1 = 'CompletedDate'	THEN CONVERT(VARCHAR, CRecordFinish, 120)
				ELSE	'nosorta'
			END ASC ,
			CASE	WHEN	@Order1 = 'Username-DESC'	THEN Username
				WHEN	@Order1 = 'Firstname-DESC'	THEN Firstname
				WHEN	@Order1 = 'Middlename-DESC'	THEN Middlename
				WHEN	@Order1 = 'Lastname-DESC'	THEN Lastname
				WHEN	@Order1 = 'EmployeeType-DESC'	THEN EmployeeType
				WHEN	@Order1 = 'Centername-DESC'	THEN Centername
				WHEN	@Order1 = 'Branchname-DESC'	THEN Branchname
				WHEN	@Order1 = 'Email-DESC'		THEN Email
				WHEN	@Order1 = 'CompletedDate-DESC'	THEN CONVERT(VARCHAR, CRecordFinish, 120)
				ELSE	'nosortb'
			END DESC ,
			CASE	WHEN	@Order2 = 'Username'		THEN Username
				WHEN	@Order2 = 'Firstname'		THEN Firstname
				WHEN	@Order2 = 'Middlename'		THEN Middlename
				WHEN	@Order2 = 'Lastname'		THEN Lastname
				WHEN	@Order2 = 'EmployeeType'	THEN EmployeeType
				WHEN	@Order2 = 'Centername'		THEN Centername
				WHEN	@Order2 = 'Branchname'		THEN Branchname
				WHEN	@Order2 = 'Email'		THEN Email
				WHEN	@Order1 = 'CompletedDate'	THEN CONVERT(VARCHAR, CRecordFinish, 120)
				ELSE	'nosortc'
			END ASC ,
			CASE	WHEN	@Order2 = 'Username-DESC'	THEN Username
				WHEN	@Order2 = 'Firstname-DESC'	THEN Firstname
				WHEN	@Order2 = 'Middlename-DESC'	THEN Middlename
				WHEN	@Order2 = 'Lastname-DESC'	THEN Lastname
				WHEN	@Order2 = 'EmployeeType-DESC'	THEN EmployeeType
				WHEN	@Order2 = 'Centername-DESC'	THEN Centername
				WHEN	@Order2 = 'Branchname-DESC'	THEN Branchname
				WHEN	@Order2 = 'Email-DESC'		THEN Email
				WHEN	@Order1 = 'CompletedDate-DESC'	THEN CONVERT(VARCHAR, CRecordFinish, 120)
				ELSE	'nosortd'
			END DESC ,
			CASE	WHEN	@Order3 = 'Username'		THEN Username
				WHEN	@Order3 = 'Firstname'		THEN Firstname
				WHEN	@Order3 = 'Middlename'		THEN Middlename
				WHEN	@Order3 = 'Lastname'		THEN Lastname
				WHEN	@Order3 = 'EmployeeType'	THEN EmployeeType
				WHEN	@Order3 = 'Centername'		THEN Centername
				WHEN	@Order3 = 'Branchname'		THEN Branchname
				WHEN	@Order3 = 'Email'		THEN Email
				WHEN	@Order1 = 'CompletedDate'	THEN CONVERT(VARCHAR, CRecordFinish, 120)
				ELSE	'nosorte'
			END ASC ,
			CASE	WHEN	@Order3 = 'Username-DESC'	THEN Username
				WHEN	@Order3 = 'Firstname-DESC'	THEN Firstname
				WHEN	@Order3 = 'Middlename-DESC'	THEN Middlename
				WHEN	@Order3 = 'Lastname-DESC'	THEN Lastname
				WHEN	@Order3 = 'EmployeeType-DESC'	THEN EmployeeType
				WHEN	@Order3 = 'Centername-DESC'	THEN Centername
				WHEN	@Order3 = 'Branchname-DESC'	THEN Branchname
				WHEN	@Order3 = 'Email-DESC'		THEN Email
				WHEN	@Order1 = 'CompletedDate-DESC'	THEN CONVERT(VARCHAR, CRecordFinish, 120)
				ELSE	'nosortf'
			END DESC 

SET ROWCOUNT 0

/* Retrieve the recordset for the Page */
SELECT	CASE	WHEN	@Fmt = 0
		THEN	'<a href="ad-useredit.asp?UserID=' + CAST(U.UserID as VARCHAR)+ 
				'" target="_blank">' + CAST(U.UserID AS VARCHAR) + '</a>'
		ELSE	CAST (U.UserID AS VARCHAR)
	END as UserID ,
	FirstName , 
	Middlename as Mi ,
	LastName , 
	CASE	WHEN	LEN(C.Centername) > 20 Then STUFF(C.Centername, 20, 0, '<wbr>') 
		ELSE	C.Centername
	END AS Centername,
	CASE	WHEN	LEN(B.Branchname) > 100 Then STUFF(B.Branchname, 10, 0, '<wbr>') 
		ELSE	REPLACE(B.Branchname, '/', '/<wbr>')
	END AS Branchname,
	Username, 
	Email,
	CourseIteration AS FY ,
	CRecordID as Certificate ,
	CASE	WHEN	@Fmt = 0
		THEN
			CASE	WHEN CRecordFinish IS NULL THEN ''
				ELSE REPLACE(CONVERT(varchar, CRecordFinish, 0), ' ', ' ') END
		ELSE
			CASE	WHEN CRecordFinish IS NULL THEN ''
				ELSE CONVERT(varchar, CRecordFinish, 0) END

	END AS [Completed], 
	CASE	WHEN 	@Fmt = 0
		THEN
			CASE	WHEN Duration is NULL THEN ''
				WHEN Duration > 120 THEN '> 2 hr'
				ELSE CAST(Duration/60 AS VARCHAR) + ' hh, ' +
				     CAST(Duration-(Duration/60)*60 AS VARCHAR) + ' mm'
			END 
		ELSE
			CASE	WHEN Duration is NULL THEN ''
				WHEN Duration > 120 THEN '> 2 hr'
				ELSE CAST(Duration/60 AS VARCHAR) + ' hh, ' +
				     CAST(Duration-(Duration/60)*60 AS VARCHAR) + ' mm'
			END 
	END AS Duration,
	CASE	CRAcknowledge
		WHEN 0 THEN 'Deny'
		WHEN 1 THEN 'Accept'
		ELSE 'None'
	END AS Accept ,
	U.EmployeeType as [Type],
	CASE U.ISSO	WHEN 1 THEN 'Yes' ELSE 'No' END AS ISSO ,
	CASE U.Offline	WHEN 1 THEN 'Yes' ELSE 'No' END AS Offline ,
	CASE U.Inactive	WHEN 1 THEN 'Active' WHEN 2 THEN 'Inactive' ELSE 'Deleted' END AS Status ,
	CS.Lang AS Lg

	FROM	#TempRowLookup R
	INNER JOIN dbo.Users U ON U.UserID = R.UserID
	LEFT OUTER JOIN dbo.CourseRecords CR
			ON	CR.UserID = U.UserID 
			AND	CR.CourseID = @CourseID
			AND	CR.CRecordFinish BETWEEN @CompleteStart AND @CompleteFinish
	LEFT OUTER JOIN dbo.Courses CS ON CS.CourseID = CR.CourseID
	LEFT OUTER JOIN dbo.Branches B on U.BranchID=B.BranchID 
	LEFT OUTER JOIN dbo.Centers C on U.CenterID=C.CenterID 

	WHERE	R.RowID >= @PageSize * @CurrentPage
	AND	R.RowID < @PageSize * (@CurrentPage + 1)
	OR	@PageSize = 0 -- Return all rows in recordset
	ORDER BY R.RowID

DROP TABLE #TempRowLookup


RETURN(0)

quote:

---

Originally posted by SamC

"index intersection thing..." - could you be any more cryptic? OH! That index intersection thing !

---