SELECT DISTINCT a.ServiceLineName [Service Line], a.AdmitDateTime [Admission Date], a.AdmissionStatusDescription, a.PatientInsuranceName [Payer], a.PatientLastName [Last Name], a.PatientFirstName [First Name], p.MRN +'-'+ a.AdmissionID [MRN and Admission #], CAST(ar.ReferralDate as DATE) [Referral Date], CAST(a.InitialReferralDate AS DATE) [Initial Referral Date], CAST(a.SOCDate as DATE) [SOC Date],

sg.ServiceLineGroupingName [Branch],

f.Name [FacilityName], fm.LastName [Marketer First Name] , fm.FirstName [Marketer Last Name]

FROM rpt.AdmissionReferrals ar

JOIN rpt.Facilities f ON f.FacilitySID=ar.FacilitySID

LEFT JOIN rpt.facilitymarketers fm ON fm.FacilitySID=f.FacilitySID AND  ar.ReferralDate BETWEEN fm.StartDate AND fm.EndDate

 JOIN rpt.Admissions a ON a.AdmissionSID=ar.AdmissionSID

 JOIN rpt.Patients p ON p.PatientSID=a.PatientSID

 JOIN rpt.ServiceLineGroupings sg ON sg.ServiceLineSID=a.ServiceLineSID

WHERE sg.ServiceLineGroupingName IN ('Hamden', 'Rocky Hill')

AND a.SOCDate != '12/31/9999' AND a.AdmitDateTime != '12/31/9999'

AND a.AdmitDateTime >= @StartDate AND a.AdmitDateTime <= @EndDate