I have a procedure that needs some adjustment..can someone help me please?
I have 2 tables: Survey Table and EvaluationVisit
depends on the particpant ID I need to display this table:
Particpant ID Date of Evaluation Evaluation Type School Name Age Gender
1 05/12/2009 begin
1 05/13/2009 end
All the time I create a Evaluation visit I need to have this displayed in this table indepentend if I have a Survey school Name, Age or Gender created..
Right now all my procedure does is bring the data if there is aSurvey and Evaluation with the same a participantID
here is my procedure:
ALTER PROCEDURE sp_GetAllEvaluationVisit(
@ParticipantID as int) AS
SET NOCOUNT ON
--declare @ParticipantID as int
--set @ParticipantID = '1'
e.ParticipantID as [ParticipantID], e.DateEvaluation as [DateEvaluation],
(case when (e.EvaluationID = 0) then ''
when (e.EvaluationID = 1) then 'Program Begin'
when (e.EvaluationID = 2) then 'Program End'
when (e.EvaluationID = 3) then 'Six Month'
end ) as [EvaluationType],
(case when (s.SchoolID = 0) then ''
when (s.SchoolID = 1) then 'Santee Educational Complex'
when (s.SchoolID = 2) then 'Jefferson'
when (s.SchoolID = 3) then 'Dorsey'
when (s.SchoolID = 4) then 'Fremont'
when (s.SchoolID = 5) then 'West Adams'
end ) as [SchoolName],
s.Age as [Age],
(case when (s.Gender = 1) then 'Male'
when (s.Gender = 2) then 'Female'
end ) as [Gender]
from tbl_EvaluationVisit e join tbl_Survey s on e.ParticipantID = @ParticipantID and s.ParticipantID = @ParticipantID