Skip to content

Instantly share code, notes, and snippets.

@ChrisMoney

ChrisMoney/row_number.sql

Last active December 28, 2021 13:37
Show Gist options
  • Save ChrisMoney/5542767 to your computer and use it in GitHub Desktop.
Save ChrisMoney/5542767 to your computer and use it in GitHub Desktop.
Download ZIP
SQL - Row_Number() OVER (ORDER BY)
Raw
row_number.sql
SELECT ApplicantID, Email, FName, LName, Application_Status,
(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = @programType)) AS Conflicts
WHERE RowNum = 1))) AS Conflict01,
(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames01
WHERE RowNum = 1) as Rev1Name,
(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs01
WHERE RowNum = 1))) AS Priority01,
------------------------------------------------------------------------------------------------
(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts02
WHERE RowNum = 2))) AS Conflict02,
(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames02
WHERE RowNum = 2) as Rev2Name,
(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs02
WHERE RowNum = 2))) AS Priority02,
------------------------------------------------------------------------------------------------
(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts03
WHERE RowNum = 3))) AS Conflict03,
(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames03
WHERE RowNum = 3) as Rev3Name,
(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs03
WHERE RowNum = 3))) AS Priority03,
------------------------------------------------------------------------------------------------
(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts04
WHERE RowNum = 4))) AS Conflict04,
(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames04
WHERE RowNum = 4) as Rev4Name,
(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs04
WHERE RowNum = 4))) AS Priority04,
------------------------------------------------------------------------------------------------
(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts05
WHERE RowNum = 5))) AS Conflict05,
(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames05
WHERE RowNum = 5) as Rev5Name,
(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs05
WHERE RowNum = 5))) AS Priority05,
------------------------------------------------------------------------------------------------
(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts06
WHERE RowNum = 6))) AS Conflict06,
(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames06
WHERE RowNum = 6) as Rev6Name,
(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs06
WHERE RowNum = 6))) AS Priority06,
------------------------------------------------------------------------------------------------
(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts07
WHERE RowNum = 7))) AS Conflict07,
(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames07
WHERE RowNum = 7) as Rev7Name,
(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs07
WHERE RowNum = 7))) AS Priority07,
------------------------------------------------------------------------------------------------
(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts08
WHERE RowNum = 8))) AS Conflict08,
(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames08
WHERE RowNum = 8) as Rev8Name,
(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs08
WHERE RowNum = 8))) AS Priority08,
------------------------------------------------------------------------------------------------
(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts09
WHERE RowNum = 9))) AS Conflict09,
(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames09
WHERE RowNum = 9) as Rev9Name,
(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs09
WHERE RowNum = 9))) AS Priority09,
------------------------------------------------------------------------------------------------
(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts10
WHERE RowNum = 10))) AS Conflict10,
(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames10
WHERE RowNum = 10) as Rev10Name,
(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs10
WHERE RowNum = 10))) AS Priority10,
------------------------------------------------------------------------------------------------
(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts11
WHERE RowNum = 11))) AS Conflict11,
(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames11
WHERE RowNum = 11) as Rev11Name,
(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs11
WHERE RowNum = 11))) AS Priority11,
------------------------------------------------------------------------------------------------
(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts12
WHERE RowNum = 12))) AS Conflict12,
(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames12
WHERE RowNum = 12) as Rev12Name,
(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs12
WHERE RowNum = 12))) AS Priority12,
------------------------------------------------------------------------------------------------
(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts13
WHERE RowNum = 13))) AS Conflict13,
(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames13
WHERE RowNum = 13) as Rev13Name,
(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs13
WHERE RowNum = 13))) AS Priority13,
------------------------------------------------------------------------------------------------
(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts14
WHERE RowNum = 14))) AS Conflict14,
(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames14
WHERE RowNum = 14) as Rev14Name,
(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs14
WHERE RowNum = 14))) AS Priority14,
------------------------------------------------------------------------------------------------
(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts15
WHERE RowNum = 15))) AS Conflict15,
(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames15
WHERE RowNum = 15) as Rev15Name,
(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs15
WHERE RowNum = 15))) AS Priority15,
------------------------------------------------------------------------------------------------
(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts16
WHERE RowNum = 16))) AS Conflict16,
(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames16
WHERE RowNum = 16) as Rev16Name,
(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs16
WHERE RowNum = 16))) AS Priority16,
------------------------------------------------------------------------------------------------
(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts17
WHERE RowNum = 17))) AS Conflict17,
(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames17
WHERE RowNum = 17) as Rev17Name,
(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs17
WHERE RowNum = 17))) AS Priority17,
------------------------------------------------------------------------------------------------
(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts18
WHERE RowNum = 18))) AS Conflict18,
(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames18
WHERE RowNum = 18) as Rev18Name,
(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs18
WHERE RowNum = 18))) AS Priority18,
------------------------------------------------------------------------------------------------
(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts19
WHERE RowNum = 19))) AS Conflict19,
(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames19
WHERE RowNum = 19) as Rev19Name,
(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs19
WHERE RowNum = 19))) AS Priority19,
------------------------------------------------------------------------------------------------
(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts20
WHERE RowNum = 20))) AS Conflict20,
(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames20
WHERE RowNum = 20) as Rev20Name,
(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs20
WHERE RowNum = 20))) AS Priority20,
------------------------------------------------------------------------------------------------
(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts21
WHERE RowNum = 21))) AS Conflict21,
(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames21
WHERE RowNum = 21) as Rev21Name,
(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs21
WHERE RowNum = 21))) AS Priority21,
------------------------------------------------------------------------------------------------
(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts22
WHERE RowNum = 22))) AS Conflict22,
(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames22
WHERE RowNum = 22) as Rev22Name,
(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs22
WHERE RowNum = 22))) AS Priority22,
------------------------------------------------------------------------------------------------
(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts23
WHERE RowNum = 23))) AS Conflict23,
(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames23
WHERE RowNum = 23) as Rev23Name,
(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs23
WHERE RowNum = 23))) AS Priority23,
------------------------------------------------------------------------------------------------
(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts24
WHERE RowNum = 24))) AS Conflict24,
(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames24
WHERE RowNum = 24) as Rev24Name,
(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs24
WHERE RowNum = 24))) AS Priority24,
------------------------------------------------------------------------------------------------
(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts25
WHERE RowNum = 25))) AS Conflict25,
(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames25
WHERE RowNum = 25) as Rev25Name,
(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs25
WHERE RowNum = 25))) AS Priority25,
------------------------------------------------------------------------------------------------
(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts26
WHERE RowNum = 26))) AS Conflict26,
(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames26
WHERE RowNum = 26) as Rev26Name,
(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs26
WHERE RowNum = 26))) AS Priority26,
------------------------------------------------------------------------------------------------
(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts27
WHERE RowNum = 27))) AS Conflict27,
(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames27
WHERE RowNum = 27) as Rev27Name,
(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs27
WHERE RowNum = 27))) AS Priority27,
------------------------------------------------------------------------------------------------
(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts28
WHERE RowNum = 28))) AS Conflict28,
(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames28
WHERE RowNum = 28) as Rev28Name,
(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs28
WHERE RowNum = 28))) AS Priority28,
------------------------------------------------------------------------------------------------
(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts29
WHERE RowNum = 29))) AS Conflict29,
(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames29
WHERE RowNum = 29) as Rev29Name,
(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs29
WHERE RowNum = 29))) AS Priority29,
------------------------------------------------------------------------------------------------
(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts30
WHERE RowNum = 30))) AS Conflict30,
(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames30
WHERE RowNum = 30) as Rev30Name,
(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs30
WHERE RowNum = 30))) AS Priority30
FROM Applicant
WHERE (Program_Type = 'mtpci')
@ChrisMoney
Copy link
Author

Most complicated SQL I ever wrote.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment