CREATE VIEW [dbo].[v_MedicalPatientSubjectiveDetails]
AS
SELECT	ROW_NUMBER() 
        OVER (ORDER BY V.signInDate,C.recId)   ID
		,V.surfaceItemId surfaceItemId
		,C.recId complaintID
		,C.complaint
		,V.signInDate
		,CONVERT(VARCHAR(10), V.signInDate,120) date
		,ISNULL(L.Score,0) score
		,V.note
FROM	pal_MedicalPatientVisitLog V
		JOIN pal_MedicalPatientSubjectivesLog S
			ON S.VisitLogID = V.recId
		JOIN pal_MedicalPatientComplaints C
			ON C.recId = S.complaintId
		LEFT JOIN pal_MedicalPatientSubjectivesLog L
			ON L.VisitLogID = V.recId
			AND L.ComplaintID = C.recId
GROUP BY V.surfaceItemId
		,C.recId
		,C.complaint
		,V.signInDate
		,L.Score
		,V.note



