We help IT Professionals succeed at work.

Adjust SQL Script to Show Column Value Based on Month Data

334 Views
Last Modified: 2012-05-11
Hello Experts,

On Monday you helped me with a SQL script which I have been testing.  There is one remaining piece that I need, and I am hoping it can be done.  The column called, 'Referral' contains a value that will either say, 'New Referral' or 'Re-Referral'.  The way the script is currently working, this column is populated by looking at every record per person in the table.  But, I only want the value: 'Re-Referral' when there is an Enrollment Date and a Discharged Date that is less than (prior to) the Enrollment Date in the current record.  This is because a person can have multiple records, but they are not Re-Referrals until they have been Discharged and have a new Enrollment Date - it doesn't matter if they have a new 'Discharged Date'.  The Referral column for a person with multiple records is showing them as Re-Referrals in all of their records - even the first one where they are a 'New-Referral'.  My brain has struggled with a creative solution for this, and failed.  Can you think of a way?

The goal is to show the kids who have been enrolled ONE TIME as 'New Referrals', and to show the kids who have been discharged and re-enrolled as 'Re-Referrals', but only in their records after their first record of enrollment and discharged.

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 pa2.date_enrolled is not null
                                                      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 pa2.date_enrolled is not null
                                                      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_enrolled is not null
                                                            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 pa2.date_enrolled is not null
                                                      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 pa2.date_enrolled is not null
                                                                  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 '??'  -- << some value here
                  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'
and pa.date_enrolled is not null


Thank you very much for any help you can give me on this.
~Chopp
Comment
Watch Question

Author

Commented:
OOps, the title of the question is misleading - I don't care about the 'month'.  

I only care that the value: 'Re-Referrals' only shows up in records where: there is a previous enrolled date, a previous discharged date, and a new enrollment date.

And, that the value: 'New-Referrals' only shows up in records where there is an enrollment date with no previous enrollment date and discharged date.  

Thank you for your help.
~Chopp
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Oh my, you are brilliant!  This is almost perfect.  The only thing I still need is for the 'Re-Referral' value not to show up unless there has been a previous 'DISCHARGED DATE'.  This is because a kid is not considered a 'Re-Referral' unless they were enrolled AND discharged, and then came back for counseling and were enrolled again.  Is there a way to include in the script that 'Re-Referral' only shows up if there is a previous 'discharged date' and a post 'enrolled date'?

Thank you for your help!!!
~Chopp
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
I pasted the create table and insert scripts below for the two tables: PatientAssignment and PatientClinTrans.  I have to figure out how to send actual anonymized data.  

What I am hoping is that there is a way to evaluate each kid's records so that if there is a discharged date in ANY record PRIOR to the current record (which would have an enrollment date), then that would identify the 'Re-Referral'.


USE [PsychLIVE]
GO
/****** Object:  Table [dbo].[Patient_Assignment]    Script Date: 04/20/2011 16:02:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[Patient_Assignment](
      [patient_assignment_id] [int] IDENTITY(1,1) NOT NULL,
      [patient_id] [char](10) NOT NULL,
      [episode_id] [char](3) NOT NULL,
      [status] [char](2) NOT NULL,
      [primary_assignment] [dbo].[UD_N_For_No] NOT NULL,
      [is_inpatient] [dbo].[UD_N_For_No] NOT NULL,
      [clinic_id] [char](10) NOT NULL,
      [service_id] [char](10) NOT NULL,
      [program_id] [char](10) NOT NULL,
      [protocol_id] [char](10) NOT NULL,
      [date_referred] [datetime] NULL,
      [date_requested] [datetime] NULL,
      [date_scheduled] [datetime] NULL,
      [date_enrolled] [datetime] NULL,
      [date_discharged] [datetime] NULL,
      [last_date_of_service] [datetime] NULL,
      [diagnosis] [char](10) NULL,
      [dsm_no] [smallint] NULL,
      [diagnosis_version] [char](10) NULL,
      [axis_I_2] [varchar](10) NULL,
      [dsm_no_2] [smallint] NULL,
      [diagnosis_version_2] [varchar](10) NULL,
      [axis_I_3] [varchar](10) NULL,
      [dsm_no_3] [smallint] NULL,
      [diagnosis_version_3] [varchar](10) NULL,
      [discharge_reason] [char](10) NULL,
      [discharge_location] [char](10) NULL,
      [discharge_comments] [text] NULL,
      [comments] [text] NULL,
      [user_id] [dbo].[UD_User_Id] NULL,
      [entry_chron] [dbo].[UD_Entry_Chron] NULL,
      [patient_class] [varchar](10) NULL,
      [admission_type] [varchar](10) NULL,
      [servicing_facility] [varchar](10) NULL,
      [discharge_disposition] [varchar](10) NULL,
      [orig_user_id] [dbo].[UD_User_Id] NULL,
      [orig_entry_chron] [dbo].[UD_Entry_Chron] NULL,
      [bill_dx_tracked_at_assign] [dbo].[UD_N_For_No] NULL,
      [is_system_created] [varchar](1) NULL,
      [sort_sequence] [varchar](65) NULL,
      [treatment_plan_date] [datetime] NULL,
 CONSTRAINT [XPKPatient_Assignment] PRIMARY KEY NONCLUSTERED
(
      [patient_assignment_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Patient_Assignment]  WITH CHECK ADD  CONSTRAINT [FK_1_Patient_Assignment] FOREIGN KEY([protocol_id], [program_id], [service_id], [clinic_id])
REFERENCES [dbo].[Protocol] ([protocol_id], [program_id], [service_id], [clinic_id])
GO
ALTER TABLE [dbo].[Patient_Assignment] CHECK CONSTRAINT [FK_1_Patient_Assignment]
GO
ALTER TABLE [dbo].[Patient_Assignment]  WITH CHECK ADD  CONSTRAINT [FK_7_Patient_Assignment] FOREIGN KEY([patient_id], [episode_id])
REFERENCES [dbo].[Patient] ([patient_id], [episode_id])
GO
ALTER TABLE [dbo].[Patient_Assignment] CHECK CONSTRAINT [FK_7_Patient_Assignment]
GO
ALTER TABLE [dbo].[Patient_Assignment]  WITH CHECK ADD  CONSTRAINT [CHK1Patient_Assignment] CHECK  ((([diagnosis] is null or isnull([diagnosis],' ') <> ' ' and ((not([dsm_no] is null))) and ((not([diagnosis_version] is null)))) and ([axis_I_2] is null or isnull([axis_I_2],' ') <> ' ' and ((not([dsm_no_2] is null))) and ((not([diagnosis_version_2] is null)))) and ([axis_I_3] is null or isnull([axis_I_3],' ') <> ' ' and ((not([dsm_no_3] is null))) and ((not([diagnosis_version_3] is null))))))
GO
ALTER TABLE [dbo].[Patient_Assignment] CHECK CONSTRAINT [CHK1Patient_Assignment]
GO
ALTER TABLE [dbo].[Patient_Assignment]  WITH CHECK ADD  CONSTRAINT [CHK3Patient_Assignment] CHECK  (([status] = 'RE' or [status] = 'RF' or [status] = 'SC' or [status] = 'OL' or [status] = 'EN' or [status] = 'DI' or [status] = 'TR'))
GO
ALTER TABLE [dbo].[Patient_Assignment] CHECK CONSTRAINT [CHK3Patient_Assignment]


SELECT [patient_assignment_id]
      ,[patient_id]
      ,[episode_id]
      ,[status]
      ,[primary_assignment]
      ,[is_inpatient]
      ,[clinic_id]
      ,[service_id]
      ,[program_id]
      ,[protocol_id]
      ,[date_referred]
      ,[date_requested]
      ,[date_scheduled]
      ,[date_enrolled]
      ,[date_discharged]
      ,[last_date_of_service]
      ,[diagnosis]
      ,[dsm_no]
      ,[diagnosis_version]
      ,[axis_I_2]
      ,[dsm_no_2]
      ,[diagnosis_version_2]
      ,[axis_I_3]
      ,[dsm_no_3]
      ,[diagnosis_version_3]
      ,[discharge_reason]
      ,[discharge_location]
      ,[discharge_comments]
      ,[comments]
      ,[user_id]
      ,[entry_chron]
      ,[patient_class]
      ,[admission_type]
      ,[servicing_facility]
      ,[discharge_disposition]
      ,[orig_user_id]
      ,[orig_entry_chron]
      ,[bill_dx_tracked_at_assign]
      ,[is_system_created]
      ,[sort_sequence]
      ,[treatment_plan_date]
  FROM [PsychLIVE].[dbo].[Patient_Assignment]



USE [PsychLIVE]
GO
/****** Object:  Table [dbo].[Patient_Clin_Tran]    Script Date: 04/20/2011 16:03:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[Patient_Clin_Tran](
      [clinical_transaction_no] [int] NOT NULL,
      [group_clin_tran_no] [int] NOT NULL,
      [patient_id] [char](10) NOT NULL,
      [episode_id] [char](3) NOT NULL,
      [status] [char](2) NOT NULL,
      [is_error] [dbo].[UD_N_For_No] NOT NULL,
      [is_ready_to_complete] [char](1) NOT NULL,
      [hosp_status_code] [char](2) NOT NULL,
      [training_program_id] [char](10) NULL,
      [attending_id] [char](10) NULL,
      [referring_id] [char](10) NULL,
      [acuity] [char](10) NULL,
      [billable] [dbo].[UD_Y_For_Yes] NOT NULL,
      [billing_proc_code] [char](10) NULL,
      [billing_proc_hosp_status] [char](2) NULL,
      [billing_proc_duration] [int] NULL,
      [billing_duration_type] [char](2) NULL,
      [billing_proc_desc] [varchar](255) NULL,
      [billing_qty] [int] NULL,
      [billing_amt] [money] NULL,
      [ovrrd_fee_matrix_amt] [dbo].[UD_N_For_No] NOT NULL,
      [census_transaction_no] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
SET ANSI_PADDING ON
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [referral_no] [varchar](35) NULL
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [traffic_light] [char](3) NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [next_proc_hosp_status] [char](2) NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [authorization_required] [char](2) NOT NULL CONSTRAINT [DF1Patient_Clin_Tran]  DEFAULT ('NA')
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [authorization_no] [int] NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [vendor_auth_no] [int] NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [vendor_auth_seq_no] [int] NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [vendor_fee_matrix_id] [int] NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [vendor_amount] [money] NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [error_text] [text] NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [orig_user_id] [dbo].[UD_User_Id] NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [orig_entry_chron] [dbo].[UD_Entry_Chron] NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [user_id] [dbo].[UD_User_Id] NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [entry_chron] [dbo].[UD_Entry_Chron] NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [fee_matrix_id] [int] NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [cpt_code] [char](10) NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [cpt_code_id] [int] NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [patient_assignment_id] [int] NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [sort_sequence] [varchar](50) NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [schedule_id] [int] NULL
SET ANSI_PADDING ON
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [remark] [varchar](1000) NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [group_entity_id] [int] NULL
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [clinic_id] [char](10) NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [service_id] [char](10) NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [program_id] [char](10) NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [protocol_id] [char](10) NULL
SET ANSI_PADDING ON
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [proc_code] [char](10) NOT NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [proc_chron] [datetime] NOT NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [appt_date] [varchar](8) NOT NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [proc_duration] [int] NOT NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [duration_type] [char](2) NOT NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [proc_chron_end] [datetime] NOT NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [clinician_id] [char](10) NOT NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [billing_id] [char](10) NOT NULL
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [super_id] [char](10) NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [next_proc_code] [char](10) NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [next_proc_days] [int] NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [next_proc_duration] [int] NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [next_duration_type] [char](2) NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [type_of_service] [char](2) NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [place_of_service] [char](2) NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [location_code] [varchar](10) NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [vendor_id] [int] NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [axis_I_II_1] [varchar](10) NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [dsm_no_I_II_1] [smallint] NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [axis_I_II_2] [varchar](10) NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [dsm_no_I_II_2] [smallint] NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [axis_I_II_3] [varchar](10) NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [dsm_no_I_II_3] [smallint] NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [diagnosis_version_I_II] [varchar](10) NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [axis_III_1] [varchar](10) NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [dsm_no_III_1] [smallint] NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [diagnosis_version_III] [varchar](10) NULL
SET ANSI_PADDING ON
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [system_gen_scheduled] [char](1) NOT NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [date_scheduled] [datetime] NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [system_gen_show] [char](1) NOT NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [date_show] [datetime] NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [system_gen_no_show] [char](1) NOT NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [date_no_show] [datetime] NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [system_gen_start] [char](1) NOT NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [date_start] [datetime] NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [system_gen_stop] [char](1) NOT NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [date_stop] [datetime] NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [system_gen_complete] [char](1) NOT NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [date_complete] [datetime] NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [system_gen_cancel] [char](1) NOT NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [date_cancel] [datetime] NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [system_gen_error] [char](1) NOT NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [date_error] [datetime] NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [start_time] [datetime] NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [stop_time] [datetime] NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [clinical_transaction_no_old] [int] NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [clinical_transaction_no_new] [int] NULL
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [cancellation_count] [smallint] NULL
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD [cancellation_reason] [char](10) NULL
/****** Object:  Index [XPKPatient_Clin_Tran]    Script Date: 04/20/2011 16:03:04 ******/
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD  CONSTRAINT [XPKPatient_Clin_Tran] PRIMARY KEY NONCLUSTERED
(
      [clinical_transaction_no] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
/****** Object:  Index [XAK1Patient_Clin_Tran]    Script Date: 04/20/2011 16:03:04 ******/
ALTER TABLE [dbo].[Patient_Clin_Tran] ADD  CONSTRAINT [XAK1Patient_Clin_Tran] UNIQUE NONCLUSTERED
(
      [group_clin_tran_no] ASC,
      [patient_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Patient_Clin_Tran]  WITH CHECK ADD  CONSTRAINT [FK_2_Patient_Clin_Tran] FOREIGN KEY([group_clin_tran_no])
REFERENCES [dbo].[Group_Clin_Tran] ([group_clin_tran_no])
GO
ALTER TABLE [dbo].[Patient_Clin_Tran] CHECK CONSTRAINT [FK_2_Patient_Clin_Tran]
GO
ALTER TABLE [dbo].[Patient_Clin_Tran]  WITH CHECK ADD  CONSTRAINT [FK_3_Patient_Clin_Tran] FOREIGN KEY([attending_id])
REFERENCES [dbo].[Staff] ([staff_id])
GO
ALTER TABLE [dbo].[Patient_Clin_Tran] CHECK CONSTRAINT [FK_3_Patient_Clin_Tran]
GO
ALTER TABLE [dbo].[Patient_Clin_Tran]  WITH CHECK ADD  CONSTRAINT [FK_4_Patient_Clin_Tran] FOREIGN KEY([patient_id], [episode_id])
REFERENCES [dbo].[Patient] ([patient_id], [episode_id])
GO
ALTER TABLE [dbo].[Patient_Clin_Tran] CHECK CONSTRAINT [FK_4_Patient_Clin_Tran]
GO
ALTER TABLE [dbo].[Patient_Clin_Tran]  WITH CHECK ADD  CONSTRAINT [FK_5_Patient_Clin_Tran] FOREIGN KEY([patient_assignment_id])
REFERENCES [dbo].[Patient_Assignment] ([patient_assignment_id])
GO
ALTER TABLE [dbo].[Patient_Clin_Tran] CHECK CONSTRAINT [FK_5_Patient_Clin_Tran]
GO
ALTER TABLE [dbo].[Patient_Clin_Tran]  WITH CHECK ADD  CONSTRAINT [CHK10Patient_Clin_Tran] CHECK  (([system_gen_show] = 'N' or [system_gen_show] = 'Y'))
GO
ALTER TABLE [dbo].[Patient_Clin_Tran] CHECK CONSTRAINT [CHK10Patient_Clin_Tran]
GO
ALTER TABLE [dbo].[Patient_Clin_Tran]  WITH CHECK ADD  CONSTRAINT [CHK11Patient_Clin_Tran] CHECK  (([system_gen_no_show] = 'N' or [system_gen_no_show] = 'Y'))
GO
ALTER TABLE [dbo].[Patient_Clin_Tran] CHECK CONSTRAINT [CHK11Patient_Clin_Tran]
GO
ALTER TABLE [dbo].[Patient_Clin_Tran]  WITH CHECK ADD  CONSTRAINT [CHK12Patient_Clin_Tran] CHECK  (([system_gen_start] = 'N' or [system_gen_start] = 'Y'))
GO
ALTER TABLE [dbo].[Patient_Clin_Tran] CHECK CONSTRAINT [CHK12Patient_Clin_Tran]
GO
ALTER TABLE [dbo].[Patient_Clin_Tran]  WITH CHECK ADD  CONSTRAINT [CHK13Patient_Clin_Tran] CHECK  (([system_gen_stop] = 'N' or [system_gen_stop] = 'Y'))
GO
ALTER TABLE [dbo].[Patient_Clin_Tran] CHECK CONSTRAINT [CHK13Patient_Clin_Tran]
GO
ALTER TABLE [dbo].[Patient_Clin_Tran]  WITH CHECK ADD  CONSTRAINT [CHK14Patient_Clin_Tran] CHECK  (([system_gen_complete] = 'N' or [system_gen_complete] = 'Y'))
GO
ALTER TABLE [dbo].[Patient_Clin_Tran] CHECK CONSTRAINT [CHK14Patient_Clin_Tran]
GO
ALTER TABLE [dbo].[Patient_Clin_Tran]  WITH CHECK ADD  CONSTRAINT [CHK15Patient_Clin_Tran] CHECK  (([system_gen_cancel] = 'N' or [system_gen_cancel] = 'Y'))
GO
ALTER TABLE [dbo].[Patient_Clin_Tran] CHECK CONSTRAINT [CHK15Patient_Clin_Tran]
GO
ALTER TABLE [dbo].[Patient_Clin_Tran]  WITH CHECK ADD  CONSTRAINT [CHK16Patient_Clin_Tran] CHECK  (([system_gen_error] = 'N' or [system_gen_error] = 'Y'))
GO
ALTER TABLE [dbo].[Patient_Clin_Tran] CHECK CONSTRAINT [CHK16Patient_Clin_Tran]
GO
ALTER TABLE [dbo].[Patient_Clin_Tran]  WITH CHECK ADD  CONSTRAINT [CHK17Patient_Clin_Tran] CHECK  (([status] = 'SC' or [status] = 'ST' or [status] = 'SP' or [status] = 'SH' or [status] = 'CO' or [status] = 'NS' or [status] = 'CA' or [status] = 'ER'))
GO
ALTER TABLE [dbo].[Patient_Clin_Tran] CHECK CONSTRAINT [CHK17Patient_Clin_Tran]
GO
ALTER TABLE [dbo].[Patient_Clin_Tran]  WITH CHECK ADD  CONSTRAINT [CHK1Patient_Clin_Tran] CHECK  (([authorization_required] = 'NO' or ([authorization_required] = 'YE' or [authorization_required] = 'NA')))
GO
ALTER TABLE [dbo].[Patient_Clin_Tran] CHECK CONSTRAINT [CHK1Patient_Clin_Tran]
GO
ALTER TABLE [dbo].[Patient_Clin_Tran]  WITH CHECK ADD  CONSTRAINT [CHK2Patient_Clin_Tran] CHECK  (([hosp_status_code] = [billing_proc_hosp_status] or [billing_proc_hosp_status] is null))
GO
ALTER TABLE [dbo].[Patient_Clin_Tran] CHECK CONSTRAINT [CHK2Patient_Clin_Tran]
GO
ALTER TABLE [dbo].[Patient_Clin_Tran]  WITH CHECK ADD  CONSTRAINT [CHK3Patient_Clin_Tran] CHECK  (([billing_proc_code] is null and [billing_proc_hosp_status] is null and [billing_proc_duration] is null and [billing_duration_type] is null and [billing_qty] is null or ((not([billing_proc_code] is null))) and [billing_proc_code] = [proc_code] and ((not([billing_proc_hosp_status] is null))) and ((not([billing_proc_duration] is null))) and ((not([billing_duration_type] is null))) and ((not([billing_qty] is null))) or [is_error] = 'Y'))
GO
ALTER TABLE [dbo].[Patient_Clin_Tran] CHECK CONSTRAINT [CHK3Patient_Clin_Tran]
GO
ALTER TABLE [dbo].[Patient_Clin_Tran]  WITH CHECK ADD  CONSTRAINT [CHK5Patient_Clin_Tran] CHECK  (([is_ready_to_complete] = 'F' or ([is_ready_to_complete] = 'N' or [is_ready_to_complete] = 'Y')))
GO
ALTER TABLE [dbo].[Patient_Clin_Tran] CHECK CONSTRAINT [CHK5Patient_Clin_Tran]
GO
ALTER TABLE [dbo].[Patient_Clin_Tran]  WITH CHECK ADD  CONSTRAINT [CHK6Patient_Clin_Tran] CHECK  (([billing_amt] >= 0 and [billing_proc_code] <> 'BALFWD' or [billing_proc_code] = 'BALFWD' or [billing_amt] is null))
GO
ALTER TABLE [dbo].[Patient_Clin_Tran] CHECK CONSTRAINT [CHK6Patient_Clin_Tran]
GO
ALTER TABLE [dbo].[Patient_Clin_Tran]  WITH CHECK ADD  CONSTRAINT [CHK7Patient_Clin_Tran] CHECK  (([vendor_auth_no] is null and [vendor_auth_seq_no] is null or ((not([vendor_auth_no] is null))) and ((not([vendor_auth_seq_no] is null)))))
GO
ALTER TABLE [dbo].[Patient_Clin_Tran] CHECK CONSTRAINT [CHK7Patient_Clin_Tran]
GO
ALTER TABLE [dbo].[Patient_Clin_Tran]  WITH CHECK ADD  CONSTRAINT [CHK8Patient_Clin_Tran] CHECK  (([vendor_fee_matrix_id] is null and [vendor_amount] is null or ((not([vendor_fee_matrix_id] is null))) and ((not([vendor_amount] is null)))))
GO
ALTER TABLE [dbo].[Patient_Clin_Tran] CHECK CONSTRAINT [CHK8Patient_Clin_Tran]
GO
ALTER TABLE [dbo].[Patient_Clin_Tran]  WITH CHECK ADD  CONSTRAINT [CHK9Patient_Clin_Tran] CHECK  (([system_gen_scheduled] = 'N' or [system_gen_scheduled] = 'Y'))
GO
ALTER TABLE [dbo].[Patient_Clin_Tran] CHECK CONSTRAINT [CHK9Patient_Clin_Tran]


SELECT [clinical_transaction_no]
      ,[group_clin_tran_no]
      ,[patient_id]
      ,[episode_id]
      ,[status]
      ,[is_error]
      ,[is_ready_to_complete]
      ,[hosp_status_code]
      ,[training_program_id]
      ,[attending_id]
      ,[referring_id]
      ,[acuity]
      ,[billable]
      ,[billing_proc_code]
      ,[billing_proc_hosp_status]
      ,[billing_proc_duration]
      ,[billing_duration_type]
      ,[billing_proc_desc]
      ,[billing_qty]
      ,[billing_amt]
      ,[ovrrd_fee_matrix_amt]
      ,[census_transaction_no]
      ,[referral_no]
      ,[traffic_light]
      ,[next_proc_hosp_status]
      ,[authorization_required]
      ,[authorization_no]
      ,[vendor_auth_no]
      ,[vendor_auth_seq_no]
      ,[vendor_fee_matrix_id]
      ,[vendor_amount]
      ,[error_text]
      ,[orig_user_id]
      ,[orig_entry_chron]
      ,[user_id]
      ,[entry_chron]
      ,[fee_matrix_id]
      ,[cpt_code]
      ,[cpt_code_id]
      ,[patient_assignment_id]
      ,[sort_sequence]
      ,[schedule_id]
      ,[remark]
      ,[group_entity_id]
      ,[clinic_id]
      ,[service_id]
      ,[program_id]
      ,[protocol_id]
      ,[proc_code]
      ,[proc_chron]
      ,[appt_date]
      ,[proc_duration]
      ,[duration_type]
      ,[proc_chron_end]
      ,[clinician_id]
      ,[billing_id]
      ,[super_id]
      ,[next_proc_code]
      ,[next_proc_days]
      ,[next_proc_duration]
      ,[next_duration_type]
      ,[type_of_service]
      ,[place_of_service]
      ,[location_code]
      ,[vendor_id]
      ,[axis_I_II_1]
      ,[dsm_no_I_II_1]
      ,[axis_I_II_2]
      ,[dsm_no_I_II_2]
      ,[axis_I_II_3]
      ,[dsm_no_I_II_3]
      ,[diagnosis_version_I_II]
      ,[axis_III_1]
      ,[dsm_no_III_1]
      ,[diagnosis_version_III]
      ,[system_gen_scheduled]
      ,[date_scheduled]
      ,[system_gen_show]
      ,[date_show]
      ,[system_gen_no_show]
      ,[date_no_show]
      ,[system_gen_start]
      ,[date_start]
      ,[system_gen_stop]
      ,[date_stop]
      ,[system_gen_complete]
      ,[date_complete]
      ,[system_gen_cancel]
      ,[date_cancel]
      ,[system_gen_error]
      ,[date_error]
      ,[start_time]
      ,[stop_time]
      ,[clinical_transaction_no_old]
      ,[clinical_transaction_no_new]
      ,[cancellation_count]
      ,[cancellation_reason]
  FROM [PsychLIVE].[dbo].[Patient_Clin_Tran]
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
No objection to the closure, if the OP found a suitable answer.  Thanks for posting your findings.

The only thing I would say is that you should be careful with functions in your SELECT list.  This will lead to performance problems as the number of rows in the tables increase, as the function has to be called once for every row in the table.  If you can later refactor this into a proper JOIN-based query, you should do so.

If I get some time, I will look at what you have done here and try to refactor into a JOIN-based query.  In the meantime, though, good luck with your app.

J.

Author

Commented:
Thank you for your help!
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.