• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • Last Modified:

SQL Help

I would like to expand my exlusion list to also filter out notes that start with these:

Changed Collections Status to
Changed Next Contact Date to

The note starts with this ... how would I accomplish this?
SELECT
CASE CAST(dbo.PatientCorrespondence.Description AS VARCHAR(8000))
        		  WHEN '**long**' THEN CAST(dbo.PatientCorrespondence.DescriptionLong AS VARCHAR(8000))
        		  ELSE CAST(dbo.PatientCorrespondence.Description AS VARCHAR(8000))
        		END AS Notes
	FROM
        		PatientVisit pv1 
		LEFT JOIN PatientCorrespondence ON pv1.PatientVisitId = PatientCorrespondence.PatientVisitId 
			AND PatientCorrespondence.Description <> 'Visit Transferred to Collection' AND PatientCorrespondence.Description <> 'Visit Removed from Collection'  
	WHERE
		pv1.PatientVisitId = PatientVisit.PatientVisitId
	ORDER BY 
		PatientCorrespondence.Created DESC

Open in new window

0
Jeff S
Asked:
Jeff S
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
something like this

SELECT Notes
FROM (
SELECT PatientCorrespondence.Created,
CASE CAST(dbo.PatientCorrespondence.Description AS VARCHAR(8000))
      WHEN '**long**' THEN CAST(dbo.PatientCorrespondence.DescriptionLong AS VARCHAR(8000))
      ELSE CAST(dbo.PatientCorrespondence.Description AS VARCHAR(8000))
     END AS Notes
FROM  PatientVisit pv1
LEFT JOIN PatientCorrespondence ON pv1.PatientVisitId = PatientCorrespondence.PatientVisitId
  AND PatientCorrespondence.Description <> 'Visit Transferred to Collection' AND PatientCorrespondence.Description <> 'Visit Removed from Collection'  
WHERE pv1.PatientVisitId = PatientVisit.PatientVisitId
) A
WHERE NOTES not like 'Changed Collections Status to %' AND Notes NOT LIKE 'Changed Next Contact Date to%'

ORDER BY Created DESC
0
 
Jeff SAuthor Commented:
THANK YOU
0
 
tigin44Commented:
as I understand you are looking for something like this
SELECT
	CASE CAST(dbo.PatientCorrespondence.Description AS VARCHAR(8000))
		WHEN '**long**' THEN CAST(dbo.PatientCorrespondence.DescriptionLong AS VARCHAR(8000))
		ELSE CAST(dbo.PatientCorrespondence.Description AS VARCHAR(8000))
    END AS Notes
FROM PatientVisit pv1 
        LEFT JOIN PatientCorrespondence ON pv1.PatientVisitId = PatientCorrespondence.PatientVisitId 
WHERE PatientCorrespondence.Description <> 'Visit Transferred to Collection' 
   OR PatientCorrespondence.Description <> 'Visit Removed from Collection'  
   OR PatientCorrespondence.Description NOT LIKE 'Changed Collections Status to%'  
   OR PatientCorrespondence.Description NOT LIKE 'Changed Next Contact Date to%'  
ORDER BY PatientCorrespondence.Created DESC

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now