|
|
Posted by Jozef de Veuster on 07/25/05 16:59
For the people interested, here is the solution I used:
DECLARE @sortField AS VARCHAR(20)
DECLARE @UserId AS INTEGER
SET @sortField = 'SCAN_DATE'
SET @UserId = 1
SELECT DOC_PID, SCAN_DATE, DOC_STATE, isViewed, DOC_COMMENT,
requestDelete, USER_FNAME, USER_NAME, DOSSIER_CODE, COUNT(NOTE_PID)
NrOfNotes,
CASE @sortField
WHEN 'DOSSIER_CODE'
THEN CAST(DOSSIER_CODE AS VARCHAR(50))
WHEN 'SCAN_DATE'
THEN CONVERT(VARCHAR, SCAN_DATE, 121)
ELSE
CAST(SCAN_DATE AS VARCHAR(50))
END AS ORDERFIELD
FROM MR_DOCS
LEFT OUTER JOIN MR_USERS
ON MR_DOCS.USER_FID = USER_PID
LEFT OUTER JOIN MR_DOSSIERS
ON DOSSIER_FID = DOSSIER_PID
LEFT OUTER JOIN MR_NOTES
ON DOC_PID = MR_NOTES.DOC_FID
WHERE MR_DOCS.USER_FID = @UserId
AND DOC_STATE IN (1, 3, 4)
AND REMINDER_DATE <= getdate()
AND MR_DOCS.isVisible = 1
AND TREE_FID IS NULL
AND TODO_FID IS NULL
-- scanned for this user (1), moved to this user (3), forwarded to this
user (4),
GROUP BY DOC_PID, SCAN_DATE, DOC_STATE, isViewed, DOC_COMMENT,
requestDelete, USER_FNAME, USER_NAME, DOSSIER_CODE
UNION
SELECT DOC_PID, SCAN_DATE, DOC_STATE, isViewed, DOC_COMMENT,
requestDelete, USER_FNAME, USER_NAME, DOSSIER_CODE, COUNT(NOTE_PID)
NrOfNotes,
CASE @sortField
WHEN 'DOSSIER_CODE'
THEN CAST(DOSSIER_CODE AS VARCHAR(50))
WHEN 'SCAN_DATE'
THEN CONVERT(VARCHAR, SCAN_DATE, 121)
ELSE
CAST(SCAN_DATE AS VARCHAR(50))
END AS ORDERFIELD
FROM MR_DOCS
LEFT OUTER JOIN MR_USERS
ON USER_FID = USER_PID
LEFT OUTER JOIN MR_DOSSIERS
ON DOSSIER_FID = DOSSIER_PID
LEFT OUTER JOIN MR_NOTES
ON DOC_PID = MR_NOTES.DOC_FID
WHERE BORROW_USER_FID = @UserId
AND DOC_STATE = 5
AND REMINDER_DATE <= getdate()
AND MR_DOCS.isVisible = 1
AND TREE_FID IS NULL
AND TODO_FID IS NULL
-- or borrowed to this user
GROUP BY DOC_PID, SCAN_DATE, DOC_STATE, isViewed, DOC_COMMENT,
requestDelete, USER_FNAME, USER_NAME, DOSSIER_CODE
ORDER BY ORDERFIELD DESC
Tim@Allgeier
*** Sent via Developersdex http://www.developersdex.com ***
Navigation:
[Reply to this message]
|