[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL View - Add Column Based on Two Conditions

Posted on 2011-04-18
4
Medium Priority
?
514 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
0
Comment
Question by:Chopp
  • 2
4 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 1400 total points
ID: 35420743
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
                  -- if there exists another date_enrolled, count>1, otherwise it is =1
      ,            case
                  when not exists (select * from Patient_Assignment pa2 (nolock)
                        where pa2.Patient_ID = pcl.Patient_ID
                        and pa2.date_enrolled <> pa.date_enrolled)
                  then 'New 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 '???'  -- << some value here
                  end
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'
0
 
LVL 32

Assisted Solution

by:Ephraim Wangoya
Ephraim Wangoya earned 600 total points
ID: 35420821

Can you try it this way
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
      ,     case
				when (select count(distinct date_enrolled) from Patient_Assignment where Patient_ID = Patient_Clin_Tra.Patient_ID) = 1 then
				  'New Referral'
				when ((select count(distinct date_enrolled) from Patient_Assignment where Patient_ID = Patient_Clin_Tra.Patient_ID) > 1) 
				   and ((select count(distinct date_discharged) from Patient_Assignment where Patient_ID = Patient_Clin_Tra.Patient_ID) >= 1) then  
				  'Re-referral'            
				else
				  ''  
				end 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'

Open in new window

0
 

Author Comment

by:Chopp
ID: 35421413
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'


0
 

Author Closing Comment

by:Chopp
ID: 35421428
Thank you very much and have a nice evening!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
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…

872 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