Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Help

Posted on 2009-12-18
3
Medium Priority
?
249 Views
Last Modified: 2012-05-08
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
Comment
Question by:Jeff S
3 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 26084588
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
 
LVL 7

Author Closing Comment

by:Jeff S
ID: 31667975
THANK YOU
0
 
LVL 26

Expert Comment

by:tigin44
ID: 26084643
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question