How to loop through table to get all values?

[jayy66]

ok, I have a stored procedure that gets the dates of each time a document was last signed. It looks something like this:

SELECT 	ISNULL(CONVERT(varchar (8),TreatAuth, 10),'') as TreatAuth,
 		ISNULL(CONVERT(varchar (8),HipaaAck, 10),'') as HipaaAck,
 		ISNULL(CONVERT(varchar (8),FinRespWRI, 10),'') as FinRespWRI,
  		ISNULL(CONVERT(varchar (8),FinRespIns, 10),'') as FinRespIns,
  		ISNULL(CONVERT(varchar (8),WorkStat, 10), '') as WorkStat,
  		ISNULL(CONVERT(varchar (8),DrugAlcohol, 10),'') as DrugAlcohol,
  		ISNULL(CONVERT(varchar (8),ReleasePHI, 10),'') as ReleasePHI
FROM 		tblPtSigs  WITH (NOLOCK) 
WHERE 	PatientID = @tempPatientID

My problem is that any given patient can have multiple visits and therefore previously signed the required documents.

For example the table can look something like this:
patientid treatDate hipaaDate
102 02/02/2006 NULL
102 NULL 02/10/2006
102 NULL 02/15/2006

The stored procedure will only grab the last dates meaning that treatDate will return a null when it has been signed on 2/2/2006. How can i get all the last dates for each signed document??

thankx

 

[jayy66]

oh yea, patientid is not the primary key in the table. The key is patientVisitID. A patient can have multiple visits.

 

[cjlarue]

Try

ISNULL(CONVERT(varchar (8),Max(TreatAuth), 10),'')asTreatAuth

 

[jayy66]

Thanks, that workes