USE [Medicare]
GO
/****** Object:  StoredProcedure [dbo].[Medic_PatientPostedIPList]    Script Date: 10/22/2012 13:30:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[Medic_PatientPostedIPList] 

   @strSearch as varchar(20),
   @intOption as int,
   @strType as varchar(1),
   @intPatientType as int,
   @WithPayments as bit = 0

AS


if @WithPayments = 0

BEGIN
   /* All Patients */
   If @intOption = 1
      Begin
       If @intPatientType = 1
        Begin
         Select [Patient Name] =
                    Case A.ForPurging
	          When 1 Then
		'* '+Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                       Else
	             Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                    End,
                IsNull(A.MemberNumber,'') [Member Number],
                [Admitted] =
                   Case IsNull(B.AdmDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), B.AdmDate, 101 ) 
                End,
                [Discharged] =
                   Case IsNull(B.DcrDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), B.DcrDate, 101 ) 
                End,
                IsNull(A.IdNum,'') [Admission #],
                [Transmittal Date] =
                   Case IsNull(A.TransDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.TransDate, 101 ) 
                End
            from Medicare..tbMedPatient A Inner Join Patient_Data..tbPatient B
            On A.IdNum = B.IdNum
            Where (IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,'')) like @strSearch+'%' 
            and not exists(select idnum from medicare..tbmedpayment where idnum = a.idnum )
            Order by B.AdmDate desc
        End
       If @intPatientType = 2
        Begin
         Select [Patient Name] =
                    Case A.ForPurging
	          When 1 Then
		'* '+Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                       Else
	             Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                    End,
                IsNull(A.MemberNumber,'') [Member Number],
                [Admitted] =
                   Case IsNull(B.AdmDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), B.AdmDate, 101 ) 
                End,
                [Discharged] =
                   Case IsNull(B.DcrDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.DcrDate, 101 ) 
                End,
                IsNull(A.IdNum,'') [Admission #],
                [Transmittal Date] =
                   Case IsNull(A.TransDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.TransDate, 101 ) 
                End
            from Medicare..tbMedPatient A Inner Join Patient_Data..tbOutPatient B
            On A.IdNum = B.IdNum
            Where (IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,'')) like @strSearch+'%' 
            and not exists(select idnum from medicare..tbmedpayment where idnum = a.idnum )
            Order by B.AdmDate desc
        End
       If @intPatientType = 3
        Begin
         Select [Patient Name] =
                    Case A.ForPurging
	          When 1 Then
		'* '+Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                       Else
	             Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                    End,
                IsNull(A.MemberNumber,'') [Member Number],
                [Admitted] =
                   Case IsNull(A.AdmDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.AdmDate, 101 ) 

                End,
                [Discharged] =
                   Case IsNull(A.DcrDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.DcrDate, 101 ) 
                End,
                IsNull(A.IdNum,'') [Admission #],
                [Transmittal Date] =
                   Case IsNull(A.TransDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.TransDate, 101 ) 
                End
            from Medicare..tbMedPatient A 
            Where (IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,'')) like @strSearch+'%' 
            and not exists(select idnum from medicare..tbmedpayment where idnum = a.idnum ) and
	          Right(A.IdNum,1) = 'D'
            Order by A.AdmDate desc
        End
      End

   /* By Type 
       1 -- SSS
       2 -- GSIS
       3 -- OWWA
       4 -- ECC */
   If @intOption = 2
      Begin
       If @intPatientType = 1
        Begin
         Select [Patient Name] =
                    Case A.ForPurging
	          When 1 Then
		'* '+Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                       Else
	             Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                    End,
                IsNull(A.MemberNumber,'') [Member Number],
                [Admitted] =
                   Case IsNull(B.AdmDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), B.AdmDate, 101 ) 
                End,
                [Discharged] =
                   Case IsNull(B.DcrDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), B.DcrDate, 101 ) 
                End,
                IsNull(A.IdNum,'') [Admission #],
                [Transmittal Date] =
                   Case IsNull(A.TransDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.TransDate, 101 ) 
                End
            from Medicare..tbMedPatient A Inner Join Patient_Data..tbPatient B
            On A.IdNum = B.IdNum
            Where (IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,'')) like @strSearch+'%'  and MemberType = @strType
            and not exists(select idnum from medicare..tbmedpayment where idnum = a.idnum )
            Order by  B.AdmDate desc
        End
       If @intPatientType = 2
        Begin
         Select [Patient Name] =
                    Case A.ForPurging
	          When 1 Then
		'* '+Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                       Else
	             Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                    End,
                IsNull(A.MemberNumber,'') [Member Number],
                [Admitted] =
                   Case IsNull(B.AdmDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), B.AdmDate, 101 ) 
                End,
                [Discharged] =
                   Case IsNull(B.DcrDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.DcrDate, 101 ) 
                End,
                IsNull(A.IdNum,'') [Admission #],
                [Transmittal Date] =
                   Case IsNull(A.TransDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.TransDate, 101 ) 
                End
            from Medicare..tbMedPatient A Inner Join Patient_Data..tbOutPatient B
            On A.IdNum = B.IdNum
            Where (IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,'')) like @strSearch+'%' and MemberType = @strType
            and not exists(select idnum from medicare..tbmedpayment where idnum = a.idnum )
            Order by  B.AdmDate desc
        End
       If @intPatientType = 3
        Begin
         Select [Patient Name] =
                    Case A.ForPurging
	          When 1 Then
		'* '+Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                       Else
	             Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                    End,
                IsNull(A.MemberNumber,'') [Member Number],
                [Admitted] =
                   Case IsNull(A.AdmDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.AdmDate, 101 ) 
                End,
                [Discharged] =
                   Case IsNull(A.DcrDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.DcrDate, 101 ) 
                End,
                IsNull(A.IdNum,'') [Admission #],
                [Transmittal Date] =
                   Case IsNull(A.TransDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.TransDate, 101 ) 
                End
            from Medicare..tbMedPatient A 
            Where (IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,'')) like @strSearch+'%' and
	          Right(A.IdNum,1) = 'D' and MemberType = @strType
	          and not exists(select idnum from medicare..tbmedpayment where idnum = a.idnum )
            Order by  A.AdmDate desc
        End
      End
      
      
      END
      
      ELSE
      
      
      BEGIN
      
   /* All Patients */
   If @intOption = 1
      Begin
       If @intPatientType = 1
        Begin
         Select [Patient Name] =
                    Case A.ForPurging
	          When 1 Then
		'* '+Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                       Else
	             Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                    End,
                IsNull(A.MemberNumber,'') [Member Number],
                [Admitted] =
                   Case IsNull(B.AdmDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), B.AdmDate, 101 ) 
                End,
                [Discharged] =
                   Case IsNull(B.DcrDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), B.DcrDate, 101 ) 
                End,
                IsNull(A.IdNum,'') [Admission #],
                [Transmittal Date] =
                   Case IsNull(A.TransDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.TransDate, 101 ) 
                End
            from Medicare..tbMedPatient A Inner Join Patient_Data..tbPatient B
            On A.IdNum = B.IdNum
            Where (IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,'')) like @strSearch+'%' 
            and exists(select idnum from medicare..tbmedpayment where idnum = a.idnum )
            Order by  B.AdmDate desc
        End
       If @intPatientType = 2
        Begin
         Select [Patient Name] =
                    Case A.ForPurging
	          When 1 Then
		'* '+Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                       Else
	             Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                    End,
                IsNull(A.MemberNumber,'') [Member Number],
                [Admitted] =
                   Case IsNull(B.AdmDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), B.AdmDate, 101 ) 
                End,
                [Discharged] =
                   Case IsNull(B.DcrDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.DcrDate, 101 ) 
                End,
                IsNull(A.IdNum,'') [Admission #],
                [Transmittal Date] =
                   Case IsNull(A.TransDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.TransDate, 101 ) 
                End
            from Medicare..tbMedPatient A Inner Join Patient_Data..tbOutPatient B
            On A.IdNum = B.IdNum
            Where (IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,'')) like @strSearch+'%' 
            and exists(select idnum from medicare..tbmedpayment where idnum = a.idnum )
            Order by  B.AdmDate desc
        End
       If @intPatientType = 3
        Begin
         Select [Patient Name] =
                    Case A.ForPurging
	          When 1 Then
		'* '+Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                       Else
	             Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                    End,
                IsNull(A.MemberNumber,'') [Member Number],
                [Admitted] =
                   Case IsNull(A.AdmDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.AdmDate, 101 ) 

                End,
                [Discharged] =
                   Case IsNull(A.DcrDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.DcrDate, 101 ) 
                End,
                IsNull(A.IdNum,'') [Admission #],
                [Transmittal Date] =
                   Case IsNull(A.TransDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.TransDate, 101 ) 
                End
            from Medicare..tbMedPatient A 
            Where (IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,'')) like @strSearch+'%' and
	          Right(A.IdNum,1) = 'D'
            and exists(select idnum from medicare..tbmedpayment where idnum = a.idnum )
            Order by  A.AdmDate desc
        End
      End

   /* By Type 
       1 -- SSS
       2 -- GSIS
       3 -- OWWA
       4 -- ECC */
   If @intOption = 2
      Begin
       If @intPatientType = 1
        Begin
         Select [Patient Name] =
                    Case A.ForPurging
	          When 1 Then
		'* '+Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                       Else
	             Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                    End,
                IsNull(A.MemberNumber,'') [Member Number],
                [Admitted] =
                   Case IsNull(B.AdmDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), B.AdmDate, 101 ) 
                End,
                [Discharged] =
                   Case IsNull(B.DcrDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), B.DcrDate, 101 ) 
                End,
                IsNull(A.IdNum,'') [Admission #],
                [Transmittal Date] =
                   Case IsNull(A.TransDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.TransDate, 101 ) 
                End
            from Medicare..tbMedPatient A Inner Join Patient_Data..tbPatient B
            On A.IdNum = B.IdNum
            Where (IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,'')) like @strSearch+'%'  and MemberType = @strType
           and exists(select idnum from medicare..tbmedpayment where idnum = a.idnum )
            Order by  B.AdmDate desc
        End
       If @intPatientType = 2
        Begin
         Select [Patient Name] =
                    Case A.ForPurging
	          When 1 Then
		'* '+Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                       Else
	             Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                    End,
                IsNull(A.MemberNumber,'') [Member Number],
                [Admitted] =
                   Case IsNull(B.AdmDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), B.AdmDate, 101 ) 
                End,
                [Discharged] =
                   Case IsNull(B.DcrDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.DcrDate, 101 ) 
                End,
                IsNull(A.IdNum,'') [Admission #],
                [Transmittal Date] =
                   Case IsNull(A.TransDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.TransDate, 101 ) 
                End
            from Medicare..tbMedPatient A Inner Join Patient_Data..tbOutPatient B
            On A.IdNum = B.IdNum
            Where (IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,'')) like @strSearch+'%' and MemberType = @strType
           and exists(select idnum from medicare..tbmedpayment where idnum = a.idnum )
            Order by  B.AdmDate desc
        End
       If @intPatientType = 3
        Begin
         Select [Patient Name] =
                    Case A.ForPurging
	          When 1 Then
		'* '+Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                       Else
	             Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                    End,
                IsNull(A.MemberNumber,'') [Member Number],
                [Admitted] =
                   Case IsNull(A.AdmDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.AdmDate, 101 ) 
                End,
                [Discharged] =
                   Case IsNull(A.DcrDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.DcrDate, 101 ) 
                End,
                IsNull(A.IdNum,'') [Admission #],
                [Transmittal Date] =
                   Case IsNull(A.TransDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.TransDate, 101 ) 
                End
            from Medicare..tbMedPatient A 
            Where (IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,'')) like @strSearch+'%' and
	          Right(A.IdNum,1) = 'D' and MemberType = @strType
            and exists(select idnum from medicare..tbmedpayment where idnum = a.idnum )
            Order by  A.AdmDate desc
        End
      End
      
      END

GO




USE [Medicare]
GO
/****** Object:  StoredProcedure [dbo].[Medic_PatientPostedListIPByID]    Script Date: 10/22/2012 13:30:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO







ALTER PROCEDURE [dbo].[Medic_PatientPostedListIPByID]

   @strSearch as varchar(20),
   @intOption as int,
   @strType as varchar(1),
   @intPatientType as int,
   @WithPayments as bit = 0

AS

if @WithPayments = 0

BEGIN
   /* All Patients */
   If @intOption = 1
      Begin
       If @intPatientType = 1
        Begin
         Select [Patient Name] =
                    Case A.ForPurging
	          When 1 Then
		'* '+Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                       Else
	             Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                    End,
                IsNull(A.MemberNumber,'') [Member Number],
                [Admitted] =
                   Case IsNull(B.AdmDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), B.AdmDate, 101 ) 
                End,
                [Discharged] =
                   Case IsNull(B.DcrDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), B.DcrDate, 101 ) 
                End,
                IsNull(A.IdNum,'') [Admission #],
                [Transmittal Date] =
                   Case IsNull(A.TransDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.TransDate, 101 ) 
                End
            from Medicare..tbMedPatient A Inner Join Patient_Data..tbPatient B
            On A.IdNum = B.IdNum
            Where A.IdNum like @strSearch+'%'  and not exists(select idnum from medicare..tbmedpayment where IdNum like @strSearch+'%' )
            Order by B.AdmDate desc
        End
       If @intPatientType = 2
        Begin
         Select [Patient Name] =
                    Case A.ForPurging
	          When 1 Then
		'* '+Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                       Else
	             Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                    End,
                IsNull(A.MemberNumber,'') [Member Number],
                [Admitted] =
                   Case IsNull(B.AdmDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), B.AdmDate, 101 ) 
                End,
                [Discharged] =
                   Case IsNull(B.DcrDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.DcrDate, 101 ) 
                End,
                IsNull(A.IdNum,'') [Admission #],
                [Transmittal Date] =
                   Case IsNull(A.TransDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.TransDate, 101 ) 
                End
            from Medicare..tbMedPatient A Inner Join Patient_Data..tbOutPatient B
            On A.IdNum = B.IdNum
            Where A.IdNum like @strSearch+'%'   and not exists(select idnum from medicare..tbmedpayment where IdNum like @strSearch+'%' )
            Order by B.AdmDate desc
        End
       If @intPatientType = 3
        Begin
         Select [Patient Name] =
                    Case A.ForPurging
	          When 1 Then
		'* '+Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                       Else
	             Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                    End,
                IsNull(A.MemberNumber,'') [Member Number],
                [Admitted] =
                   Case IsNull(A.AdmDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.AdmDate, 101 ) 
                End,
                [Discharged] =
                   Case IsNull(A.DcrDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.DcrDate, 101 ) 
                End,
                IsNull(A.IdNum,'') [Admission #],
                [Transmittal Date] =
                   Case IsNull(A.TransDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.TransDate, 101 ) 
                End
            from Medicare..tbMedPatient A 
            Where A.IdNum like @strSearch+'%' and
	          Right(A.IdNum,1) = 'D'   and not exists(select idnum from medicare..tbmedpayment where IdNum like @strSearch+'%' )
            Order by A.AdmDate desc
        End
      End

   /* By Type 
       1 -- SSS
       2 -- GSIS
       3 -- OWWA
       4 -- ECC */
   If @intOption = 2
      Begin
       If @intPatientType = 1
        Begin
         Select [Patient Name] =
                    Case A.ForPurging
	          When 1 Then
		'* '+Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                       Else
	             Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                    End,
                IsNull(A.MemberNumber,'') [Member Number],
                [Admitted] =
                   Case IsNull(B.AdmDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), B.AdmDate, 101 ) 
                End,
                [Discharged] =
                   Case IsNull(B.DcrDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), B.DcrDate, 101 ) 
                End,
                IsNull(A.IdNum,'') [Admission #],
                [Transmittal Date] =
                   Case IsNull(A.TransDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.TransDate, 101 ) 
                End
            from Medicare..tbMedPatient A Inner Join Patient_Data..tbPatient B
            On A.IdNum = B.IdNum
            Where A.IdNum like @strSearch+'%'  and MemberType = @strType   and not exists(select idnum from medicare..tbmedpayment where IdNum like @strSearch+'%' )
            Order by B.AdmDate desc
        End
       If @intPatientType = 2
        Begin
         Select [Patient Name] =
                    Case A.ForPurging
	          When 1 Then
		'* '+Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                       Else
	             Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                    End,
                IsNull(A.MemberNumber,'') [Member Number],
                [Admitted] =
                   Case IsNull(B.AdmDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), B.AdmDate, 101 ) 
                End,
                [Discharged] =
                   Case IsNull(B.DcrDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.DcrDate, 101 ) 
                End,
                IsNull(A.IdNum,'') [Admission #],
                [Transmittal Date] =
                   Case IsNull(A.TransDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.TransDate, 101 ) 
                End
            from Medicare..tbMedPatient A Inner Join Patient_Data..tbOutPatient B
            On A.IdNum = B.IdNum
            Where A.IdNum like @strSearch+'%' and MemberType = @strType   and not exists(select idnum from medicare..tbmedpayment where IdNum like @strSearch+'%' )
            Order by B.AdmDate desc
        End
       If @intPatientType = 3
        Begin
         Select [Patient Name] =
                    Case A.ForPurging
	          When 1 Then
		'* '+Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                       Else
	             Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                    End,
                IsNull(A.MemberNumber,'') [Member Number],
                [Admitted] =
                   Case IsNull(A.AdmDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.AdmDate, 101 ) 
                End,
                [Discharged] =
                   Case IsNull(A.DcrDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.DcrDate, 101 ) 
                End,
                IsNull(A.IdNum,'') [Admission #],
                [Transmittal Date] =
                   Case IsNull(A.TransDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.TransDate, 101 ) 
                End
            from Medicare..tbMedPatient A 
            Where A.IdNum like @strSearch+'%' and
	          Right(A.IdNum,1) = 'D' and MemberType = @strType   and not exists(select idnum from medicare..tbmedpayment where IdNum like @strSearch+'%' )
            Order by A.AdmDate desc
        End
      End
      
    END  
      
      ELSE
      
      BEGIN
       /* All Patients */
   If @intOption = 1
      Begin
       If @intPatientType = 1
        Begin
         Select [Patient Name] =
                    Case A.ForPurging
	          When 1 Then
		'* '+Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                       Else
	             Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                    End,
                IsNull(A.MemberNumber,'') [Member Number],
                [Admitted] =
                   Case IsNull(B.AdmDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), B.AdmDate, 101 ) 
                End,
                [Discharged] =
                   Case IsNull(B.DcrDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), B.DcrDate, 101 ) 
                End,
                IsNull(A.IdNum,'') [Admission #],
                [Transmittal Date] =
                   Case IsNull(A.TransDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.TransDate, 101 ) 
                End
            from Medicare..tbMedPatient A Inner Join Patient_Data..tbPatient B
            On A.IdNum = B.IdNum
            Where A.IdNum like @strSearch+'%'   and exists(select idnum from medicare..tbmedpayment where IdNum like @strSearch+'%' )
            Order by B.AdmDate desc
        End
       If @intPatientType = 2
        Begin
         Select [Patient Name] =
                    Case A.ForPurging
	          When 1 Then
		'* '+Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                       Else
	             Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                    End,
                IsNull(A.MemberNumber,'') [Member Number],
                [Admitted] =
                   Case IsNull(B.AdmDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), B.AdmDate, 101 ) 
                End,
                [Discharged] =
                   Case IsNull(B.DcrDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.DcrDate, 101 ) 
                End,
                IsNull(A.IdNum,'') [Admission #],
                [Transmittal Date] =
                   Case IsNull(A.TransDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.TransDate, 101 ) 
                End
            from Medicare..tbMedPatient A Inner Join Patient_Data..tbOutPatient B
            On A.IdNum = B.IdNum
            Where A.IdNum like @strSearch+'%'  and exists(select idnum from medicare..tbmedpayment where IdNum like @strSearch+'%' )
            Order by B.AdmDate desc
        End
       If @intPatientType = 3
        Begin
         Select [Patient Name] =
                    Case A.ForPurging
	          When 1 Then
		'* '+Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                       Else
	             Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                    End,
                IsNull(A.MemberNumber,'') [Member Number],
                [Admitted] =
                   Case IsNull(A.AdmDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.AdmDate, 101 ) 
                End,
                [Discharged] =
                   Case IsNull(A.DcrDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.DcrDate, 101 ) 
                End,
                IsNull(A.IdNum,'') [Admission #],
                [Transmittal Date] =
                   Case IsNull(A.TransDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.TransDate, 101 ) 
                End
            from Medicare..tbMedPatient A 
            Where A.IdNum like @strSearch+'%' and  exists(select idnum from medicare..tbmedpayment where IdNum like @strSearch+'%' )
	          and Right(A.IdNum,1) = 'D'
            Order by A.AdmDate desc
        End
      End

   /* By Type 
       1 -- SSS
       2 -- GSIS
       3 -- OWWA
       4 -- ECC */
   If @intOption = 2
      Begin
       If @intPatientType = 1
        Begin
         Select [Patient Name] =
                    Case A.ForPurging
	          When 1 Then
		'* '+Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                       Else
	             Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                    End,
                IsNull(A.MemberNumber,'') [Member Number],
                [Admitted] =
                   Case IsNull(B.AdmDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), B.AdmDate, 101 ) 
                End,
                [Discharged] =
                   Case IsNull(B.DcrDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), B.DcrDate, 101 ) 
                End,
                IsNull(A.IdNum,'') [Admission #],
                [Transmittal Date] =
                   Case IsNull(A.TransDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.TransDate, 101 ) 
                End
            from Medicare..tbMedPatient A Inner Join Patient_Data..tbPatient B
            On A.IdNum = B.IdNum
            Where A.IdNum like @strSearch+'%'  and MemberType = @strType  and exists(select idnum from medicare..tbmedpayment where IdNum like @strSearch+'%' )
            Order by B.AdmDate desc
        End
       If @intPatientType = 2
        Begin
         Select [Patient Name] =
                    Case A.ForPurging
	          When 1 Then
		'* '+Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                       Else
	             Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                    End,
                IsNull(A.MemberNumber,'') [Member Number],
                [Admitted] =
                   Case IsNull(B.AdmDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), B.AdmDate, 101 ) 
                End,
                [Discharged] =
                   Case IsNull(B.DcrDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.DcrDate, 101 ) 
                End,
                IsNull(A.IdNum,'') [Admission #],
                [Transmittal Date] =
                   Case IsNull(A.TransDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.TransDate, 101 ) 
                End
            from Medicare..tbMedPatient A Inner Join Patient_Data..tbOutPatient B
            On A.IdNum = B.IdNum
            Where A.IdNum like @strSearch+'%' and MemberType = @strType  and exists(select idnum from medicare..tbmedpayment where IdNum like @strSearch+'%' )
            Order by B.AdmDate desc
        End
       If @intPatientType = 3
        Begin
         Select [Patient Name] =
                    Case A.ForPurging
	          When 1 Then
		'* '+Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                       Else
	             Ltrim(IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,''))
                    End,
                IsNull(A.MemberNumber,'') [Member Number],
                [Admitted] =
                   Case IsNull(A.AdmDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.AdmDate, 101 ) 
                End,
                [Discharged] =
                   Case IsNull(A.DcrDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.DcrDate, 101 ) 
                End,
                IsNull(A.IdNum,'') [Admission #],
                [Transmittal Date] =
                   Case IsNull(A.TransDate,'')
	         When '' Then ''
                      Else
                         Convert( varchar(10), A.TransDate, 101 ) 
                End
            from Medicare..tbMedPatient A 
            Where A.IdNum like @strSearch+'%' and
	          Right(A.IdNum,1) = 'D' and MemberType = @strType  and exists(select idnum from medicare..tbmedpayment where IdNum like @strSearch+'%' )
            Order by A.AdmDate desc
        End
      End
      
      
      
      END


GO