Declaring pilotID AS INT = 1 in SQL, but I need it to be 1, then 2, then 3 (LOOPING)
===

I have a monstrous query, that if you can completely understand, I will give you some serious props:

    DECLARE @flightDateOrNow DATE = GETDATE()
    DECLARE @pilotID INT = 1
    
    SELECT P7.pilotID 
    FROM Pilot P7
    WHERE location = 'DPG';
    
    
    SELECT P1.pilotID, BasicInfo.lName, BasicInfo.fName, 
    		PilotExamTemp.examType, PilotExamTemp.examDate,
    		
    		(CASE
    			WHEN EXISTS (SELECT 1 
    						 FROM PilotGFRCertification 
    						 WHERE pilotID = @pilotID 
    							AND certificationPosition = 'AVO'
    							AND certification = 'TNG')
    				AND NOT EXISTS (SELECT 1 
    								 FROM PilotGFRCertification 
    								 WHERE pilotID = @pilotID 
    									AND certificationPosition = 'AVO'
    									AND certification = 'AVO')
    				THEN 'X'
    			ELSE ''
    		 END) TNG,
    		 
    		 (CASE
    			WHEN EXISTS (SELECT 1 
    						 FROM PilotGFRCertification 
    						 WHERE pilotID = @pilotID 
    							AND certificationPosition = 'AVO'
    							AND certification = 'AVO')
    				THEN 'X'
    			ELSE ''
    		 END) AVO,
    		
    		(CASE
    			WHEN EXISTS (SELECT 1 
    						 FROM PilotGFRCertification 
    						 WHERE pilotID = @pilotID 
    							AND certificationPosition = 'AVO'
    							AND certification = 'MC')
    				THEN 'X'
    			ELSE ''
    		 END) MC,
    		
    		(CASE
    			WHEN EXISTS (SELECT 1 
    						 FROM PilotGFRCertification 
    						 WHERE pilotID = @pilotID 
    							AND certificationPosition = 'AVO'
    							AND certification = 'IO')
    				THEN 'X'
    			ELSE ''
    		 END) [IO],
    		
    		(CASE
    			WHEN EXISTS (SELECT 1 
    						 FROM PilotGFRCertification 
    						 WHERE pilotID = @pilotID 
    							AND certificationPosition = 'AVO'
    							AND certification = 'SO')
    				THEN 'X'
    			ELSE ''
    		 END) SO,
    		
    		 ISNULL((SELECT SUM(P3.flightHours) MCTotal
    		  FROM PilotLog P3
    		  WHERE P3.pilotID = P1.pilotID
    			AND (flightDate <= P1.flightDate)
    			AND topLevelPosition = 'AVO'
    			AND dutyStation = 'MC'
    		 ), 0) MCTotal,
    		 
    		 ISNULL((SELECT SUM(P3.flightHours) AVOTotal
    		  FROM PilotLog P3
    		  WHERE P3.pilotID = P1.pilotID
    			AND (flightDate <= P1.flightDate)
    			AND topLevelPosition = 'AVO'
    			AND dutyStation <> 'MC'
    		 ), 0) AVOTotal,
    		 
    		 (SELECT DATEADD(day, 60, (SELECT TOP 1 flightDate 
    									FROM PilotLog P3
    									WHERE P3.flightDate < P1.flightDate
    										AND P3.topLevelPosition = 'AVO'
    										AND P3.dutyStation = 'MC'
    									ORDER BY P3.flightDate DESC))
    		 
    		 ) currency60Day,
    		 (  
    			SELECT COUNT(*) 
    			FROM PilotLog P4 
    			WHERE P4.pilotID = @pilotID  
    				AND topLevelPosition = 'AVO'
    				AND sortieGained = 'True'
    				AND (P4.flightDate BETWEEN (CASE     
    										   WHEN P1.flightDate > (DATEADD(dd, -DAY(DATEADD(m,1,(DATEADD(dd, -DAY(DATEADD(m,1,(DATEADD(yyyy, DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), P1.flightDate) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID))))), DATEADD(m,7,(DATEADD(yyyy, DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), P1.flightDate) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID))))))   )), DATEADD(m,7,(DATEADD(dd, -DAY(DATEADD(m,1,(DATEADD(yyyy, DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), P1.flightDate) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID))))), DATEADD(m,7,(DATEADD(yyyy, DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), P1.flightDate) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID))))))   )))    
    												THEN (DATEADD(yyyy, 1, (DATEADD(dd,-(DAY(DATEADD(m,1,(DATEADD(yyyy, DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), P1.flightDate) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID)))))-1),DATEADD(m,1,(DATEADD(yyyy, DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), P1.flightDate) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID)))))) ))    
    										   WHEN P1.flightDate > (DATEADD(dd, -DAY(DATEADD(m,1,(DATEADD(yyyy, DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), P1.flightDate) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID))))), DATEADD(m,7,(DATEADD(yyyy, DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), P1.flightDate) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID))))))       
    												THEN (DATEADD(dd,-(DAY(DATEADD(m,1,(DATEADD(yyyy, DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), P1.flightDate) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID)))))-1),DATEADD(m,7,(DATEADD(yyyy, DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), P1.flightDate) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID))))))        
    										   ELSE (DATEADD(dd,-(DAY(DATEADD(m,1,(DATEADD(yyyy, DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), P1.flightDate) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID)))))-1),DATEADD(m,1,(DATEADD(yyyy, DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), P1.flightDate) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID))))))    
    										End) 
    										AND 
    										(P1.flightDate))) semiSorties,
    		(  
    			SELECT SUM(P4.flightHours)
    			FROM PilotLog P4 
    			WHERE P4.pilotID = @pilotID  
    				AND topLevelPosition = 'AVO'
    				AND sortieGained = 'True'
    				AND (P4.flightDate BETWEEN (CASE     
    										   WHEN P1.flightDate > (DATEADD(dd, -DAY(DATEADD(m,1,(DATEADD(dd, -DAY(DATEADD(m,1,(DATEADD(yyyy, DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), P1.flightDate) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID))))), DATEADD(m,7,(DATEADD(yyyy, DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), P1.flightDate) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID))))))   )), DATEADD(m,7,(DATEADD(dd, -DAY(DATEADD(m,1,(DATEADD(yyyy, DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), P1.flightDate) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID))))), DATEADD(m,7,(DATEADD(yyyy, DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), P1.flightDate) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID))))))   )))    
    												THEN (DATEADD(yyyy, 1, (DATEADD(dd,-(DAY(DATEADD(m,1,(DATEADD(yyyy, DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), P1.flightDate) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID)))))-1),DATEADD(m,1,(DATEADD(yyyy, DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), P1.flightDate) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID)))))) ))    
    										   WHEN P1.flightDate > (DATEADD(dd, -DAY(DATEADD(m,1,(DATEADD(yyyy, DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), P1.flightDate) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID))))), DATEADD(m,7,(DATEADD(yyyy, DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), P1.flightDate) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID))))))       
    												THEN (DATEADD(dd,-(DAY(DATEADD(m,1,(DATEADD(yyyy, DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), P1.flightDate) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID)))))-1),DATEADD(m,7,(DATEADD(yyyy, DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), P1.flightDate) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID))))))        
    										   ELSE (DATEADD(dd,-(DAY(DATEADD(m,1,(DATEADD(yyyy, DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), P1.flightDate) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID)))))-1),DATEADD(m,1,(DATEADD(yyyy, DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), P1.flightDate) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID))))))    
    										End) 
    										AND 
    										(P1.flightDate))) semiSortieHours,
    										
    		(CASE     
    		   WHEN @flightDateOrNow > (DATEADD(dd, -DAY(DATEADD(m,1,(DATEADD(dd, -DAY(DATEADD(m,1,(DATEADD(yyyy, DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), @flightDateOrNow) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID))))), DATEADD(m,7,(DATEADD(yyyy, DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), @flightDateOrNow) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID))))))   )), DATEADD(m,7,(DATEADD(dd, -DAY(DATEADD(m,1,(DATEADD(yyyy, DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), @flightDateOrNow) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID))))), DATEADD(m,7,(DATEADD(yyyy, DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), @flightDateOrNow) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID))))))   )))    
    				THEN (DATEADD(yyyy, 1, (DATEADD(dd, -DAY(DATEADD(m,1,(DATEADD(yyyy, DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), @flightDateOrNow) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID))))), DATEADD(m,7,(DATEADD(yyyy, DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), @flightDateOrNow) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID))))))   ))    
    		   WHEN @flightDateOrNow > (DATEADD(dd, -DAY(DATEADD(m,1,(DATEADD(yyyy, DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), @flightDateOrNow) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID))))), DATEADD(m,7,(DATEADD(yyyy, DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), @flightDateOrNow) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID))))))        
    				THEN (DATEADD(dd, -DAY(DATEADD(m,1,(DATEADD(yyyy, DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), @flightDateOrNow) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID))))), DATEADD(m,13,(DATEADD(yyyy, DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), @flightDateOrNow) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID))))))      
    		   ELSE (DATEADD(dd, -DAY(DATEADD(m,1,(DATEADD(yyyy, DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), @flightDateOrNow) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID))))), DATEADD(m,7,(DATEADD(yyyy, DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), @flightDateOrNow) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID))))))      
    		End) semiEnd,
    		
    		
    		(CASE   
    			WHEN @flightDateOrNow > (DATEADD(dd, -DAY(DATEADD(m,1,(DATEADD(yyyy, (DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), @flightDateOrNow)) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID))) )), DATEADD(m,13,(DATEADD(yyyy, (DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), @flightDateOrNow)) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID))) ))) THEN (DATEADD(yyyy, 1, (DATEADD(dd, -DAY(DATEADD(m,1,(DATEADD(yyyy, (DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), @flightDateOrNow)) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID))) )), DATEADD(m,13,(DATEADD(yyyy, (DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), @flightDateOrNow)) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID))) )))))  
    			ELSE (DATEADD(dd, -DAY(DATEADD(m,1,(DATEADD(yyyy, (DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), @flightDateOrNow)) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID))) )), DATEADD(m,13,(DATEADD(yyyy, (DATEDIFF(yy, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID), @flightDateOrNow)) - 1, (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID))) )))  
    		END) annualEnd,
    		
    		PMTemp.expirationDate, PMTemp.classFAAMedical
    		
    		
    FROM PilotLog P1, Pilot BasicInfo,
    		(SELECT DATEADD(dd, -DAY(DATEADD(m,1, medicalDate)), DATEADD(m,13,medicalDate)) expirationDate, classFAAMedical  
             FROM PilotMedical  
             WHERE (pilotID = @pilotID)  
    			AND medicalDate = (SELECT MAX(medicalDate) FROM PilotMedical WHERE pilotID = @pilotID)) AS PMTemp,
    		(SELECT *
    		 FROM PilotExam
    		 WHERE (pilotID = @pilotID)
    			AND examPosition = 'AVO'
    		    AND examDate = (SELECT MAX(examDate) FROM PilotExam WHERE pilotID = @pilotID AND examPosition = 'AVO')) AS PilotExamTemp
    		
    		
    WHERE P1.pilotID = @pilotID 
    	AND P1.topLevelPosition = 'AVO'
    	AND P1.pilotID = BasicInfo.pilotID
    	AND P1.flightDate = (SELECT MAX(P5.flightDate) FROM PilotLog P5 WHERE pilotID = @pilotID)
    	ORDER BY flightDate DESC

 
 
This results in:

    pilotID
    1
    2
    3
    4
    5


    pilotID	lName	fName	examType	examDate	TNG	AVO	MC	IO	SO	MCTotal	AVOTotal	currency60Day	semiSorties	semiSortieHours	   semiEnd	annualEnd  	expirationDate	classFAAMedical
    1	    Wilkins	Landon	Written	    2012-10-11		X	X	X		16.3	7.0	         2012-08-14	      2	        16.7	         2012-10-31	2012-10-31	2013-04-30	       Third



But what I want is the real long line for each of those pilotIDs. Is there an easy way to do this? Or do I have to completely restructure my SQL? (BTW, the really hairy part of that SQL is me figuring out the pilot's semi annual start and end period, as it is based on their birth month. Quite annoying really)