USE MHC
GO
ALTER PROCEDURE dbo.usp_IHC_Report_Hospice_ReferralsLog
	@StartDate DateTime = NULL, 
	@EndDate DateTime = NULL
AS
	IF @StartDate IS NULL 
	SET @StartDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)
	IF @EndDate IS NULL
	SET @EndDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
SELECT DISTINCT VW_PT_DATA.PATIENT_CODE [ID], 
                VW_PT_DATA.NAME_FIRST [First Name], 
                VW_PT_DATA.NAME_LAST [Last Name], 
                PT_ADMISSION.PHYSICIAN_ID1, 
                PT_ADMISSION.PHYSICIAN_ID2, 
                PT_ADMISSION.REFERRAL_SOURCE , 
                PT_ADMISSION.PROSPECT_ADMIT_DATE, 
                PT_ADMISSION.PROSPECT_TERM_DATE, 
                PT_STATUS.STATUS_CODE, 
                VW_RESOURCES.NAME_FIRST NAME_FIRST_2, 
                VW_RESOURCES.NAME_LAST NAME_LAST_2, 
                PT_STATUS.CREATE_DATE, 
                VW_RESOURCES.ORGANIZATION_NAME, 
                VW_RESOURCES_2.NAME_FIRST NAME_FIRST_3, 
                VW_RESOURCES_2.NAME_LAST NAME_LAST_3, 
                O_TEAM.TEAM_DESCRIPTION [Team], 
                VW_PT_DATA.DATE_OF_BIRTH, 
                A_DEMOGRAPHIC.DESCRIPTION DESCRIPTION_2, 
                PT_PAY_SOURCE.INSURANCE_CODE [Payor], 
                PT_ADMISSION.FIRST_CERTIFICATION_DATE, 
                PT_STATUS.ADMISSION_ID, 
                O_BRANCH.BRANCH_NAME [Branch], 
                Case when VW_RESOURCES.ORGANIZATION_NAME is null then VW_RESOURCES.NAME_FIRST + ' ' + VW_RESOURCES.NAME_LAST else VW_RESOURCES.ORGANIZATION_NAME end Case_when_VW_RESOURCES_OR, 
                Case when DatedifF(yy,VW_PT_DATA.Date_of_birth,getdate()) >=18 then '' else 'Yes' end Case_when_DatedifF_yy_VW, 
                datename(month,PT_STATUS.STATUS_DATE) datename_month_PT_STATUS, 
                datepart(month,PT_STATUS.STATUS_DATE) datepart_month_PT_STATUS, 
                datepart(year,PT_STATUS.STATUS_DATE) datepart_year_PT_STATUS_S, 
                Min(PT_STATUS.STATUS_DATE) [Referral Date]--Min_PT_STATUS_STATUS_DATE
FROM VW_PT_DATA VW_PT_DATA
      INNER JOIN PT_ADMISSION PT_ADMISSION ON 
     (PT_ADMISSION.PATIENT_ID = VW_PT_DATA.PATIENT_ID)
      INNER JOIN PT_STATUS PT_STATUS ON 
     (PT_STATUS.ADMISSION_ID = PT_ADMISSION.ADMISSION_ID)
      AND (PT_STATUS.PATIENT_ID = PT_ADMISSION.PATIENT_ID)
      INNER JOIN A_PATIENT_STATUS A_PATIENT_STATUS ON 
     (A_PATIENT_STATUS.ADMIN_SET_ID = PT_STATUS.ADMIN_SET_ID)
      AND (A_PATIENT_STATUS.STATUS_CODE = PT_STATUS.STATUS_CODE)
      LEFT OUTER JOIN VW_RESOURCES VW_RESOURCES ON 
     (VW_RESOURCES.RESOURCE_ID = PT_ADMISSION.REFERRAL_SOURCE)
      INNER JOIN O_DATASET O_DATASET ON 
     (O_DATASET.DATASET_ID = VW_PT_DATA.DATASET_ID)
      LEFT OUTER JOIN RES_ADDRESS RES_ADDRESS ON 
     (RES_ADDRESS.RESOURCE_ID = VW_RESOURCES.RESOURCE_ID)
      LEFT OUTER JOIN VW_RESOURCES VW_RESOURCES_2 ON 
     (VW_RESOURCES_2.RESOURCE_ID = PT_ADMISSION.PHYSICIAN_ID1)
      LEFT OUTER JOIN O_TEAM O_TEAM ON 
     (O_TEAM.TEAM_ID = PT_ADMISSION.TEAM_ID)
      LEFT OUTER JOIN PT_DEMOGRAPHIC PT_DEMOGRAPHIC ON 
     (PT_DEMOGRAPHIC.PATIENT_ID = PT_STATUS.PATIENT_ID)
      AND (PT_DEMOGRAPHIC.ADMIN_SET_ID = PT_STATUS.ADMIN_SET_ID)
      LEFT OUTER JOIN A_DEMOGRAPHIC A_DEMOGRAPHIC ON 
     (A_DEMOGRAPHIC.DEMO_TYPE_ID = PT_DEMOGRAPHIC.DEMO_TYPE_ID)
      AND (A_DEMOGRAPHIC.DEMO_ID = PT_DEMOGRAPHIC.DEMO_ID)
      AND (A_DEMOGRAPHIC.ADMIN_SET_ID = PT_DEMOGRAPHIC.ADMIN_SET_ID)
      LEFT OUTER JOIN PT_PAY_CONTROL PT_PAY_CONTROL ON 
     (PT_PAY_CONTROL.PATIENT_ID = PT_STATUS.PATIENT_ID)
      AND (PT_PAY_CONTROL.ADMIN_SET_ID = PT_STATUS.ADMIN_SET_ID)
      LEFT OUTER JOIN PT_PAY_SOURCE PT_PAY_SOURCE ON 
     (PT_PAY_SOURCE.PATIENT_ID = PT_PAY_CONTROL.PATIENT_ID)
      AND (PT_PAY_SOURCE.ADMIN_SET_ID = PT_PAY_CONTROL.ADMIN_SET_ID)
      AND (PT_PAY_SOURCE.PAY_SOURCE_POINTER = PT_PAY_CONTROL.PRIMARY_POINTER)
      LEFT OUTER JOIN O_BRANCH O_BRANCH ON 
     (O_BRANCH.BRANCH_ID = PT_STATUS.BRANCH_ID)
      AND (O_BRANCH.DATASET_ID = PT_STATUS.DATASET_ID)
WHERE (( PT_STATUS.STATUS_CODE IN ('P01','P03') )
       OR ((( PT_STATUS.STATUS_CODE LIKE 'A%' )
       OR ( PT_STATUS.STATUS_CODE IN ('F01','F02','F03','F04','F05','F06') )
      )
       AND ( PT_ADMISSION.PROSPECT_ADMIT_DATE IS NULL  )
      ))
       AND ( O_DATASET.DATASET_NAME = 'AAA Hospice' )
--     AND ( PT_STATUS.STATUS_DATE BETWEEN CONVERT(DATETIME,'2009-08-02',120) AND CONVERT(DATETIME,'2009-08-08 23:59:00',120) )
	AND ( PT_STATUS.STATUS_DATE >= @StartDate AND PT_STATUS.STATUS_DATE < @StartDate)
       AND ( O_TEAM.DATASET_ID = VW_PT_DATA.DATASET_ID )
       AND ( PT_DEMOGRAPHIC.DEMO_TYPE_ID = 1 )
       AND ( PT_PAY_CONTROL.PAY_CONTROL_END_DATE IS NULL  )
       AND (
      ( PT_STATUS.STATUS_DATE NOT LIKE PT_ADMISSION.FIRST_CERTIFICATION_DATE )
       OR ( PT_ADMISSION.FIRST_CERTIFICATION_DATE IS NULL  )
      )
GROUP BY VW_PT_DATA.PATIENT_CODE, 
         VW_PT_DATA.NAME_FIRST, 
         VW_PT_DATA.NAME_LAST, 
         PT_ADMISSION.PHYSICIAN_ID1, 
         PT_ADMISSION.PHYSICIAN_ID2, 
         PT_ADMISSION.REFERRAL_SOURCE, 
         PT_ADMISSION.PROSPECT_ADMIT_DATE, 
         PT_ADMISSION.PROSPECT_TERM_DATE, 
         PT_STATUS.STATUS_CODE, 
         VW_RESOURCES.NAME_FIRST, 
         VW_RESOURCES.NAME_LAST, 
         PT_STATUS.CREATE_DATE, 
         VW_RESOURCES.ORGANIZATION_NAME, 
         VW_RESOURCES_2.NAME_FIRST, 
         VW_RESOURCES_2.NAME_LAST, 
         O_TEAM.TEAM_DESCRIPTION, 
         VW_PT_DATA.DATE_OF_BIRTH, 
         A_DEMOGRAPHIC.DESCRIPTION, 
         PT_PAY_SOURCE.INSURANCE_CODE, 
         PT_ADMISSION.FIRST_CERTIFICATION_DATE, 
         PT_STATUS.ADMISSION_ID, 
         O_BRANCH.BRANCH_NAME, 
         PT_STATUS.STATUS_DATE
ORDER BY O_TEAM.TEAM_DESCRIPTION, 28, 
         VW_PT_DATA.NAME_LAST, 
         VW_PT_DATA.NAME_FIRST
GO

-- =============================================
-- Example to execute the stored procedure
-- =============================================
EXECUTE dbo.usp_IHC_Report_Hospice_ReferralsLog
EXECUTE dbo.usp_IHC_Report_Hospice_ReferralsLog '20090701','20090731'
EXECUTE dbo.usp_IHC_Report_Hospice_ReferralsLog '07/01/2009','07/31/2009'
GO