SQL Cursor

[jw2000]

I am trying to get a list of pilots by flightId using a cursor. My select returns all the information I need but it returns records by flightId.

select fp.*, p.*, f.*
from flightPilot fp, pilot p, flight f
where f.FlightStatusId = 2
and fp.pilotId = p.pilotId
and f.flightId = fp.flightId
order by fp.flightId

Ie.
FlightId, PilotId, flightInfo, pilotInfo
1, 5000 ...
1, 5001 ...
1, 5002 ...
1, 7508 ...

2, 6000 ...
2, 6001 ...
2, 6002 ...
2, 8801 ...


I need to use a cursor to get / create the following info so I can use it in an email:

Flight ID: 1, Flight Info
Pilot Id: 5000, Pilot Info
Pilot Id: 5001, Pilot Info
Pilot Id: 5002, Pilot Info
Pilot Id: 7508, Pilot Info

Next flight Id record using a cursor:

Flight ID: 2, Flight Info
Pilot Id: 6000, Pilot Info
Pilot Id: 6001, Pilot Info
Pilot Id: 6002, Pilot Info
Pilot Id: 8801, Pilot Info


Tables:

1) Pilot
pilotId (primary key)
pilotName, address, etc

2) Flight
flightId (primary key)
flightStatusId
flightName, destination, path, etc

3) FlightPilot - index table to see which flights belong to which pilots
flightId
pilotId

 

[hneal98]

It seems to me you would need a nested cursor. One to deal with and order the flights and one to deal with and order the pilots.

 

[jw2000]

I am not sure how the syntax would work here. Any suggestions?

 

[hneal98]

Here is one I had to do that gets member header info and then adds the immunizaiton detail to it. I had to use an Insert and Value to add the info to a text file. You can adjust it to your needs.

You can just copy the code and change the fields and tables to match what you have.

declare @pat_OrgID	varchar(3)
declare @pat_PatientID	varchar(15)
declare @pat_RecordDate	datetime
declare @pat_RecordTime	varchar(8)
declare @pat_RecordType	varchar(1)
declare @pat_QmacsMemid	varchar(15)
declare @pat_CHDPMemid	varchar(15)
declare @pat_MedicalNum	varchar(15)
declare @pat_Filler1	varchar(19)
declare @pat_LastName	varchar(20)
declare @pat_FirstName	varchar(15)
declare @pat_MiddleInit	varchar(15)
declare @pat_BirthSuffix varchar(3)
declare @pat_Filler2	varchar(69)
declare @pat_ResPhone	varchar(10)
declare @pat_ResAddr1	varchar(40)
declare @pat_ResStQual	varchar(30)
declare @pat_ResCity	varchar(30)
declare @pat_ResZip	varchar(9)
declare @pat_BirthDate	datetime 
declare @pat_Sex	varchar(1)
declare @pat_County	varchar(25)
declare @pat_SIIS_Disc	datetime
declare @pat_Filler3	varchar(8)
declare @pat_GuardFirstName	varchar(20)
declare @pat_GuardLastName	varchar(25)
declare @pat_Filler4	varchar(123)
declare @pat_Race	varchar(2)
declare @pat_Language	varchar(2)

declare @Imm_ra_id	varchar(18)
declare @Imm_OrgID	varchar(4)
declare @Imm_PatientID varchar(15)
declare @Imm_RecordType	varchar(1)
declare @Imm_RecordDate	datetime
declare @Imm_RecordTime	varchar(8)
declare @Imm_Vacine	varchar(5)
declare @Imm_DateGiven	datetime
declare @Imm_VaccSeries	varchar(1)
declare @Imm_Facility	varchar(60)
declare @Imm_Provider	varchar(60)
declare @Imm_ActionCode	varchar(1)
declare @Imm_ComboCode	varchar(2)
declare @Imm_PreviousDate	varchar(10)
declare @Imm_IMMS	varchar(1)
declare @Imm_Filler	varchar(3)
declare @Imm_CRN	varchar(15)
declare @Imm_CHDPMemID	varchar(15)
declare @Imm_PatType	varchar(1)


Begin Transaction
create table dbo.tmpAKCPatImm
( Ln char(2000))

DECLARE curPatientData CURSOR FOR
Select distinct
	OrgID,
	PatientID,
	RecordDate,
	RecordTime,
	RecordType,
	QmacsMemid,
	CHDPMemid,
	MedicalNum,
	Filler1,
	LastName,
	FirstName,
	MiddleInit,
	BirthSuffix,
	Filler2,
	ResPhone,
	ResAddr1,
	ResStQual,
	ResCity,
	ResZip,
	BirthDate,
	Sex,
	County,
	SIIS_Disc,
	Filler3,
	GuardFirstName,
	GuardLastName,
	Filler4,
	Race,
	Language
 from dbo.CHIAPatients
order by PatientID

	OPEN curPatientData
		FETCH NEXT FROM curPatientData into  @pat_OrgID,	
							 @pat_PatientID,	
						 	@pat_RecordDate,	
						 	@pat_RecordTime,	
						 	@pat_RecordType,	
						 	@pat_QmacsMemid,	
						 	@pat_CHDPMemid,	
						 	@pat_MedicalNum,	
						 	@pat_Filler1,	
						 	@pat_LastName,	
						 	@pat_FirstName,	
						 	@pat_MiddleInit,	
						 	@pat_BirthSuffix, 
						 	@pat_Filler2,	
						 	@pat_ResPhone,	
						 	@pat_ResAddr1,	
						 	@pat_ResStQual,	
						 	@pat_ResCity,	
						 	@pat_ResZip,	
						 	@pat_BirthDate,	 
						 	@pat_Sex,	
						 	@pat_County,	
						 	@pat_SIIS_Disc,	
						 	@pat_Filler3,
							@pat_GuardFirstName,	
						 	@pat_GuardLastName,	
						 	@pat_Filler4,	
							@pat_Race,	
						 	@pat_Language
		WHILE @@FETCH_STATUS = 0
			BEGIN

			insert into tmpAKCPatImm 
			values(
					dbo.fn_FldMask(@pat_OrgID,4)
				  + dbo.fn_FldMask(@pat_PatientID,14)
				  + dbo.fn_FldMask(CONVERT(CHAR(8),@pat_RecordDate,112),0)
				  + dbo.fn_FldMask(@pat_RecordTime,8)
				  + dbo.fn_FldMask(@pat_RecordType,1)
				  + dbo.fn_FldMask(@pat_MedicalNum,14)
				  + dbo.fn_FldMask(@pat_Filler1,19)
				  + dbo.fn_FldMask(@pat_LastName,20)
				  + dbo.fn_FldMask(@pat_FirstName,15)
				  + dbo.fn_FldMask(@pat_MiddleInit,15)
				  + dbo.fn_FldMask(@pat_BirthSuffix,3)
				  + dbo.fn_FldMask(@pat_Filler2,69)
				  + dbo.fn_FldMask(@pat_ResPhone,10)
				  + dbo.fn_FldMask(@pat_ResAddr1,20)
				  + dbo.fn_FldMask(@pat_ResStQual,20)
				  + dbo.fn_FldMask(@pat_ResCity,20)
				  + dbo.fn_FldMask(@pat_ResZip,9)
				  + dbo.fn_FldMask(CONVERT(CHAR(8),@pat_BirthDate,112),0)
				  + dbo.fn_FldMask(@pat_Sex,1)
				  + dbo.fn_FldMask(@pat_County,2)
				  + dbo.fn_FldMask(CONVERT(CHAR(8),@pat_SIIS_Disc,112),0)
				  + dbo.fn_FldMask(@pat_Filler3,8)
				  + dbo.fn_FldMask(@pat_GuardFirstName,15)
				  + dbo.fn_FldMask(@pat_GuardLastName,20)
				  + dbo.fn_FldMask(@pat_Filler4,123)
				  + dbo.fn_FldMask(@pat_Race,2)
				  + dbo.fn_FldMask(@pat_Language,2)
				  )

print dbo.fn_FldMask(@pat_OrgID,4)                              
print dbo.fn_FldMask(@pat_PatientID,14)                           
print dbo.fn_FldMask(CONVERT(CHAR(8),@pat_RecordDate,112),0)      
print dbo.fn_FldMask(@pat_RecordTime,8)                           
print dbo.fn_FldMask(@pat_RecordType,1)                           
print dbo.fn_FldMask(@pat_MedicalNum,14)                          
print dbo.fn_FldMask(@pat_Filler1,19)                             
print dbo.fn_FldMask(@pat_LastName,20)                            
print dbo.fn_FldMask(@pat_FirstName,15)                           
print dbo.fn_FldMask(@pat_MiddleInit,15)                          
print dbo.fn_FldMask(@pat_BirthSuffix,3)                          
print dbo.fn_FldMask(@pat_Filler2,69)                             
print dbo.fn_FldMask(@pat_ResPhone,10)                            
print dbo.fn_FldMask(@pat_ResAddr1,20)                            
print dbo.fn_FldMask(@pat_ResStQual,20)                           
print dbo.fn_FldMask(@pat_ResCity,20)                             
print dbo.fn_FldMask(@pat_ResZip,9)                             
print dbo.fn_FldMask(CONVERT(CHAR(8),@pat_BirthDate,112),0)       
print dbo.fn_FldMask(@pat_Sex,1)                             
print dbo.fn_FldMask(@pat_County,2)                             
print dbo.fn_FldMask(CONVERT(CHAR(8),@pat_SIIS_Disc,112),0)       
print dbo.fn_FldMask(@pat_Filler3,8)                             
print dbo.fn_FldMask(@pat_GuardFirstName,15)                      
print dbo.fn_FldMask(@pat_GuardLastName,20)                       
print dbo.fn_FldMask(@pat_Filler4,123)                            
print dbo.fn_FldMask(@pat_Race,2)                             
print dbo.fn_FldMask(@pat_Language,2) 

				DECLARE curImmunData CURSOR FOR
				select distinct PatientId, RecordType,Vacine,DateGiven,VaccSeries,Provider,ActionCode,CRN,Filler
					from CHIACrossImmun 
				where PatientID = @pat_PatientID
				order by Patientid
				OPEN curImmunData
				FETCH NEXT FROM curImmunData INTO   @Imm_PatientID, 
								 @Imm_RecordType,	
								 @Imm_Vacine,	
								 @Imm_DateGiven,	
								 @Imm_VaccSeries,	
								 @Imm_Provider,	
								 @Imm_ActionCode,	
								 @Imm_CRN,	
								 @Imm_Filler	
				WHILE @@FETCH_STATUS = 0
					BEGIN
						insert into tmpAKCPatImm 
						values(
								dbo.fn_FldMask(@pat_OrgID,4) 
								+dbo.fn_FldMask(@pat_PatientID,14)
								+dbo.fn_FldMask(CONVERT(CHAR(8),@pat_RecordDate,112),0)
								+dbo.fn_FldMask(@pat_RecordTime,8)
								+dbo.fn_FldMask(@Imm_RecordType,1)
								+ dbo.fn_FldMask(CASE @Imm_Vacine
													WHEN '67' THEN 'PR'
													ELSE @Imm_Vacine
													END,2)
								+ dbo.fn_FldMask(CONVERT(CHAR(8),@Imm_DateGiven,112),0)
								+ dbo.fn_FldMask(@Imm_VaccSeries,1)
								+dbo.fn_FldMask(' ',3)
								+dbo.fn_FldMask(' ',20)
								+dbo.fn_FldMask(@Imm_Provider,12)
								+dbo.fn_FldMask(@Imm_ActionCode,1)
								+dbo.fn_FldMask( @Imm_CRN,15)
								+dbo.fn_FldMask(@Imm_Filler,361)
							 )
						FETCH NEXT FROM curImmunData  INTO   @Imm_PatientID, 
								 @Imm_RecordType,	
								 @Imm_Vacine,	
								 @Imm_DateGiven,	
								 @Imm_VaccSeries,	
								 @Imm_Provider,	
								 @Imm_ActionCode,	
								 @Imm_CRN,	
								 @Imm_Filler	
					END	
				CLOSE curImmunData
				DEALLOCATE curImmunData
				
		FETCH NEXT FROM curPatientData into  @pat_OrgID,	
 									@pat_PatientID,	
								 	@pat_RecordDate,	
								 	@pat_RecordTime,	
								 	@pat_RecordType,	
								 	@pat_QmacsMemid,	
								 	@pat_CHDPMemid,	
								 	@pat_MedicalNum,	
								 	@pat_Filler1,	
								 	@pat_LastName,	
								 	@pat_FirstName,	
								 	@pat_MiddleInit,	
								 	@pat_BirthSuffix, 
								 	@pat_Filler2,	
								 	@pat_ResPhone,	
								 	@pat_ResAddr1,	
								 	@pat_ResStQual,	
								 	@pat_ResCity,	
								 	@pat_ResZip,	
								 	@pat_BirthDate,	 
								 	@pat_Sex,	
								 	@pat_County,	
								 	@pat_SIIS_Disc,	
								 	@pat_Filler3,
									@pat_GuardFirstName,	
								 	@pat_GuardLastName,	
								 	@pat_Filler4,	
									@pat_Race,	
								 	@pat_Language			
			END

CLOSE curPatientData
DEALLOCATE curPatientData
commit transaction
GO

 

[druer]

Is the goal that you are trying to email information for flight 1 to a specific user/group, but you will be emailing flight 2 information to a different user/group?