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

Adjust SQL Script to Show Column Value Based on Month Data

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
0
Chopp
Asked:
Chopp
  • 5
  • 4
4 Solutions
 
ChoppAuthor 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
0
 
jimbobmcgeeCommented:
Perhaps a ROW_NUMBER/PARTITION BY might do it.  If you could attach the CREATE TABLE and sample INSERT statements for your relevant tables, we might be able to help.

Otherwise, the ROW_NUMBER/PARTITION BY approach is along the lines of:
SELECT ...,
    CASE ROW_NUMBER() OVER (
        PARTITION BY patient_id 
        ORDER BY ISNULL(date_enrolled, '01-01-1900'), ISNULL(date_discharged, '01-01-1900')
    )
    WHEN 1 THEN 'New-Referral'
    ELSE 'Re-Referral'
    END AS ref_type
FROM ...

Open in new window

It does assume that your date_enrolled and date_discharged are guaranteed sequential, though...

J.
0
 
jimbobmcgeeCommented:
Edit: that should probably be:
ORDER BY ISNULL(date_enrolled, '01-01-1900'), ISNULL(date_discharged, '01-01-2900')

Open in new window

0
Independent Software Vendors: 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!

 
ChoppAuthor 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
0
 
jimbobmcgeeCommented:
That's a bit more tricky, because you have nothing strictly linking the prior record to the next to form a logical chain.  This is why I said it would only really work with ROW_NUMBER/PARTITION if you could guarantee your sequential order of dates, e.g. if your data looks like:
patient_id    episode_id    date_enrolled    date_discharged
1             1             01-01-2011       03-01-2011
1             2             05-01-2011       10-01-2011
1             3             11-02-2011       NULL

Open in new window

If this is not the case, I don't think I'm going to be able to help without some proper sample data/table scripts.  Can you supply some (anonymized) examples?
0
 
ChoppAuthor 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]
0
 
ChoppAuthor Commented:
Hi Experts,

The solution I now have is pasted below.
I am using a function to evaluate the records, and then calling the function in the view.

Thank you for your ideas, and I hope the solution copied below is interesting to you.

Until my next question..
~ Chopp

-- Delete if the function already exists
IF  EXISTS (SELECT * FROM LPDReportingAccessPsychLive.sys.objects
                  WHERE name = 'udf_HorizonsReporting_GetReferralType'
                  AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
 DROP FUNCTION [dbo].udf_HorizonsReporting_GetReferralType
GO

CREATE FUNCTION dbo.udf_HorizonsReporting_GetReferralType (@PatientID VarChar(20), @DateEnrolled DateTime)

RETURNS VarChar(50)
AS

/* ============================================================================
-- Author:        My Hero
-- Create date: 04/22/2011
-- Description:   Work out the referral type for each patient based on
                        pa.Patient_ID
                        pa.Date_Enrolled
                 
                  New-Referral =    There are no records in the patient assignment table with a
                                           pa.date_discharged value prior to the passed in enrollment_date
                                         
                  Re-Referral =     There must be a discharged_date < passed in enrollment_date
                                          There must be an enrolment_date < passed in enrollment_date
                                         
-- ============================================================================*/

BEGIN
      Declare @ReferralType VarChar(50)
      Declare @AreThereAnyPriorEnrollmentDates VarChar(10)
      Declare @AreThereAnyPriorDischargeDates VarChar(10)
     
      Set @ReferralType = '???'

      If Exists ( Select pa.date_enrolled From PsychLIVE.dbo.Patient_Assignment pa (NoLock)
                         Where pa.patient_id = @PatientID
                         And pa.date_enrolled Is Not NULL
                         And pa.date_enrolled < @DateEnrolled
                    )
            Begin
                  Set @AreThereAnyPriorEnrollmentDates = 'True'
            End
      Else
            Begin
                  Set @AreThereAnyPriorEnrollmentDates = 'False'
            End
           
     
      If Exists ( Select pa.date_discharged From PsychLIVE.dbo.Patient_Assignment pa (NoLock)
                         Where pa.patient_id = @PatientID
                         And pa.date_enrolled Is Not NULL
                         And pa.date_discharged < @DateEnrolled
                    )
            Begin
                  Set @AreThereAnyPriorDischargeDates = 'True'
            End
      Else
            Begin
                  Set @AreThereAnyPriorDischargeDates = 'False'
            End
           
      -- Decide if this patient is a new or re referral:
      If @AreThereAnyPriorDischargeDates = 'False'
       Set @ReferralType = 'New-Referral'
       
      If @AreThereAnyPriorDischargeDates = 'True' And @AreThereAnyPriorEnrollmentDates = 'True'
       Set @ReferralType = 'Re-Referral'
           
     
      -- Return the result of the function
      RETURN @ReferralType

END
GO




/****** Object:  View [dbo].[Vw_Referrals_ReReferrals]    Script Date: 04/22/2011 12:06:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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
      ,     dbo.udf_HorizonsReporting_GetReferralType(pa.patient_id, pa.date_enrolled) 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
0
 
jimbobmcgeeCommented:
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.
0
 
ChoppAuthor Commented:
Thank you for your help!
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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