SET NOCOUNT ON
-----------------------------------------------------------------------
/* Physical Exam Recalls - AGE 18-39 (No-Email) for Internal Medicine*/
/* RecallTypeId = 2						     */
/* Created 08/27/07 by: ccrespo					     */
/* Rules:	 - 18-39 recall w/ in 3 year 			     */
/*		 - recalls within current month			     */
/*		 - must run first or within previous month *once*    */
-----------------------------------------------------------------------
/*Prevent User From Running This Recall w/in same month*/
DECLARE @RecallTypeId int
DECLARE @RecallInterval int
SET @RecallTypeId = 2
SET @RecallInterval = 3
/*if exists (SELECT * FROM RecallLog WHERE RecallTypeId = @RecallTypeId
	AND month(RunDate) = month(getdate()) AND year(RunDate) = year(getdate()))
BEGIN
  PRINT 'Physical Exam Recall Already Run For Current Month'
END
ELSE if not exists (SELECT * FROM RecallLog WHERE RecallTypeId = @RecallTypeId
	AND month(RunDate) = month(getdate()) AND year(RunDate) = year(getdate()))
BEGIN*/
DECLARE @PatientProfileId int
DECLARE @DoctorId int
DECLARE @FacilityId int
DECLARE @MaxVisit DateTime
DECLARE @RecallDate DateTime

--DECLARE c_PhysicalExamRecall CURSOR LOCAL STATIC FORWARD_ONLY FOR
SELECT 	pv.PatientProfileId,
	pv.DoctorId,
	pv.FacilityId,
	max(pv.visit) as MaxVisit, 
	dateadd(yy,@RecallInterval,max(pv.Visit)) as RecallDate
FROM [WMG-MILLBROOK].PracticeManager.dbo.PatientVisitProcs pvp
INNER JOIN [WMG-MILLBROOK].PracticeManager.dbo.PatientVisit pv on pv.PatientVisitId = pvp.PatientVisitId
INNER JOIN [WMG-MILLBROOK].PracticeManager.dbo.PatientProfile pp on pp.PatientProfileId = pv.PatientProfileId
WHERE pvp.CPTCode 
IN ('99395','99396','99397','99385','99386','99387')
AND pv.DoctorId in (
SELECT DoctorId FROM [WMG-MILLBROOK].PracticeManager.dbo.vSelectDoctor 
	WHERE SpecialtyMId IN (224,228) AND (Inactive IS NULL OR INACTIVE =0)) /*IM-224,Pulm-228*/
AND pv.PatientProfileId NOT IN (
SELECT OwnerId 
	FROM [WMG-MILLBROOK].PracticeManager.dbo.Appointments
	WHERE Canceled IS NULL AND ApptKind = '1' AND ApptTypeId IN (
		SELECT ApptTypeId 
		FROM [WMG-MILLBROOK].PracticeManager.dbo.ApptType 
		WHERE Name LIKE '%Physical%'
			AND (Inactive IS NULL
			OR Inactive = 0))
	AND ApptStart >= getdate() AND ApptStart <= dateadd(yy,1,getdate())
	)
	AND pv.BillStatus <> 18 --Exclude Voided Visits
GROUP BY pv.PatientProfileId, pp.birthdate, pv.DoctorId, pv.FacilityId
HAVING month(dateadd(yy,@RecallInterval,max(pv.visit))) = month(dateadd(mm,0,getdate()))
AND year(dateadd(yy,@RecallInterval,max(pv.visit))) = year(getdate())
AND (CASE
	WHEN dateadd(year, datediff (year, pp.birthdate, getdate()), pp.birthdate) > getdate()
	THEN datediff (year, pp.birthdate, getdate()) - 1
	ELSE datediff (year, pp.birthdate, getdate())
	END) BETWEEN 18 AND 39
ORDER BY pv.PatientProfileId

[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.

Connection Broken