We help IT Professionals succeed at work.

SQL View - Add Column Based on Two Conditions

Medium Priority
532 Views
Last Modified: 2012-05-11
Hello Experts,

I have a 2005 SQL View that returns a specified set of records, but I need to add an addtional column will return one of the following values: 'Referral' or 'Re-Referral'
Those two values are based on the following conditions:

if the distinctcount({pa.date_enrolled} per {pcl.Patient_ID}) = 1 then  "New Referral" else

if the (distinctcount({pa.date_enrolled} per {pcl.Patient_ID}) > 1 and
distinctcount({Vw_MinProchChronCompletedPerEnrollmentAllServices.date_discharged}) >= 1) then  "Re-referral"

The View script so far is:

CREATE View [dbo].[Vw_MinProchChronCompletedPerEnrollmentAllServices]
AS
Select      pcl.clinical_transaction_no
      ,     pcl.patient_id
      ,     pcl.episode_id
      ,     pcl.status
      ,     pcl.proc_chron
      ,     MinPCL.MinProchChron As MinProchChron
      ,     pcl.service_id
      ,     pcl.program_id
      ,     pcl.proc_code
      ,     pcl.clinician_id
      ,     pcl.patient_assignment_id
      ,     pa.date_enrolled
      ,     pa.date_discharged

From PsychLive.dbo.Patient_Clin_Tran pcl (nolock)
 Inner Join (    
                        Select      pcl2.patient_id
                              ,     pcl2.episode_id
                              ,     pcl2.patient_assignment_id
                              ,     Min(pcl2.proc_chron) As MinProchChron
                        From PsychLive.dbo.Patient_Clin_Tran pcl2 (nolock)
                       
                        Where pcl2.Status = 'CO'
                                               
                        Group By        pcl2.patient_id
                                    ,   pcl2.episode_id
                                    ,     pcl2.patient_assignment_id
                                   
                  ) As MinPCL
      On    pcl.patient_id = MinPCL.patient_id
      And   pcl.episode_id = MinPCL.episode_id
      And   pcl.patient_assignment_id = MinPCL.patient_assignment_id
      And pcl.proc_chron = MinPCL.MinProchChron
     
 Inner Join PsychLIVE.dbo.Patient_Assignment pa (NoLock)
      On    pcl.Patient_ID = pa.Patient_ID
      And   pcl.Episode_ID = pa.Episode_ID
      And pcl.patient_assignment_id = pa.patient_assignment_id
     
where pcl.Status = 'CO'


I will be overjoyed if you can figure out a way to get the 'Referral' or 'Re-Referral' data into this View.

This is to be used in a Crystal Report that shows which teenagers in trouble had a single counseling assignment, or if they had more than a single counseling assignment.

~Chopp
Comment
Watch Question

CERTIFIED EXPERT
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
You guys are tremendous!!  Thank you so much!  I was about to give up.
I did tweak your scripts a bit.  I pasted the final script below.  I'm still testing, but I think this is it.

Thanks again.
~Chopp

CREATE View [dbo].[Vw_Referrals_ReReferrals]
AS
Select      pcl.clinical_transaction_no
      ,     pcl.patient_id
      ,     pcl.episode_id
      ,     pcl.status
      ,     pcl.proc_chron
      ,     MinPCL.MinProchChron As MinProchChron
      ,     pcl.service_id
      ,     pcl.program_id
      ,     pcl.proc_code
      ,     pcl.clinician_id
      ,     pcl.patient_assignment_id
      ,     pa.date_enrolled
      ,     pa.date_discharged
                  -- if there exists another date_enrolled, count>1, otherwise it is =1
      ,            case
                  when NOT exists ( select * from PsychLive.dbo.Patient_Assignment pa2 (nolock)
                                                      where pa2.Patient_ID = pcl.Patient_ID
                                                      and IsNull(pa2.date_enrolled, '1/1/1900') <> IsNull(pa.date_enrolled,'1/1/1900'))
                  then 'New Referral'

                                 -- More than one enrolled date:
                          when exists ( select * from PsychLive.dbo.Patient_Assignment pa2 (nolock)
                                                      where pa2.Patient_ID = pcl.Patient_ID
                                                      and IsNull(pa2.date_enrolled, '1/1/1900') <> IsNull(pa.date_enrolled,'1/1/1900'))

                                -- AND there are no date_discharged dates
                                And  Not exists ( select * from PsychLive.dbo.Patient_Assignment pa2 (nolock)
                                                            where pa2.Patient_ID = pcl.Patient_ID
                                                            and pa2.date_discharged is not null )
                  then 'New Referral'


                          When Exists (     select * from PsychLive.dbo.Patient_Assignment pa2 (nolock)
                                                      where pa2.Patient_ID = pcl.Patient_ID
                                                      and IsNull(pa2.date_enrolled, '1/1/1900') <> IsNull(pa.date_enrolled,'1/1/1900'))
                                 And Exists (     select * from PsychLive.dbo.Patient_Assignment pa2 (nolock)
                                                                  where pa2.Patient_ID = pcl.Patient_ID
                                                                  and IsNull(pa2.date_discharged, '1/1/1900') <> IsNull(pa.date_discharged,'1/1/1900'))
                  then 'Re-Referral'


                  -- if there is a discharge date, then it is >= 1 for 2nd condition
                  when pa.date_discharged is not null
                  then 'Re-Referral'
                  else '??'  
                  end As Referral
From PsychLive.dbo.Patient_Clin_Tran pcl (nolock)
 Inner Join (    
                        Select      pcl2.patient_id
                              ,     pcl2.episode_id
                              ,     pcl2.patient_assignment_id
                              ,     Min(pcl2.proc_chron) As MinProchChron
                        From PsychLive.dbo.Patient_Clin_Tran pcl2 (nolock)
                        Where pcl2.Status = 'CO'
                        Group By        pcl2.patient_id
                                    ,   pcl2.episode_id
                                    ,     pcl2.patient_assignment_id
                  ) As MinPCL
      On    pcl.patient_id = MinPCL.patient_id
      And   pcl.episode_id = MinPCL.episode_id
      And   pcl.patient_assignment_id = MinPCL.patient_assignment_id
      And pcl.proc_chron = MinPCL.MinProchChron
 Inner Join PsychLIVE.dbo.Patient_Assignment pa (NoLock)
      On    pcl.Patient_ID = pa.Patient_ID
      And   pcl.Episode_ID = pa.Episode_ID
      And pcl.patient_assignment_id = pa.patient_assignment_id
where pcl.Status = 'CO'


Author

Commented:
Thank you very much and have a nice evening!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.