Link to home
Create AccountLog in
Avatar of monica73174
monica73174

asked on

Logically add Having statement to a Stored Proc

When I pass in Ready_Only = 1 I need to somehow add this statement to the stored proc below.  I tried to wrap it in a case statement and a if else and it just throws errors.

HAVING SUM(CASE WHEN JAT.PATIENT_CODE_ID IS NOT NULL AND DOC.PATIENT_CODE_ID IS NULL AND DOC_PRINT.PATIENT_CODE_ID IS NOT NULL THEN 1 ELSE 0 END) > 0


USE [MHL_DB]
GO
/****** Object:  StoredProcedure [dbo].[DOCUMENT_ACITIVITY_REPORT_HCFA]    Script Date: 2/15/2013 11:27:32 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[DOCUMENT_ACITIVITY_REPORT_HCFA]
( 
	@START_DATE datetime = NULL,
	@END_DATE datetime = NULL,
	@REPORT_TYPE VARCHAR(20) = NULL,
	@AFFILIATE_ID INT = 0, 
	@COMPANY_NAME VARCHAR(20) = '', 
	@COMPANY_CODE VARCHAR(20) = '', 
	@READY_ONLY BIT=0,
	@JOB_ID INT=0
)
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
	SELECT
		DISTINCT
		AF.AFFILIATE_ID AS AFFILIATE,
		AF.NAME,
		J.JOB_ID,
		J.JOB_TYPE,
		J.CLIENT_DSCR_ID1,
		C.CLIENT_NAME,
		CL.LOCATION_NAME,
		J.START_DATE,
		J.END_DATE,
		COUNT(DISTINCT JAT.PATIENT_CODE_ID) AS APPOINTMENTS_COUNT,
		COUNT(DISTINCT DOC.PATIENT_CODE_ID) AS PRINTED_PACKETS
FROM
		JOB J
		INNER JOIN
			CLIENT C
		ON
			C.CLIENT_ID = J.CLIENT_ID
		INNER JOIN
			JOB_APPOINTMENT JAT
		ON
			JAT.JOB_ID = J.JOB_ID
			AND JAT.APPMT_STATUS = 'O'
			AND JAT.LAB_SERVICE_APPOINTMENT <> 'N'
			AND JAT.PATIENT_ID <> 0
			AND JAT.PATIENT_ID IS NOT NULL
			AND (JAT.BLOCKED <> 'Y'
         OR JAT.BLOCKED IS NULL)
		INNER JOIN
     CLIENT_LOCATION CL
		ON
     J.CLIENT_DSCR_ID1 = CL.CLIENT_LOCATION_ID
		INNER JOIN
			PATIENT P
		ON
			P.PATIENT_ID = JAT.PATIENT_ID
		INNER JOIN
			PATIENT_CODE PC
		ON
			PC.ID = JAT.PATIENT_CODE_ID
		LEFT OUTER JOIN
			(
				SELECT
					MIN(ACR.AFFILIATE_ID) AS AFFILIATE_ID,
						(
							SELECT
								A.NAME
							FROM
								AFFILIATE A
							WHERE
								A.ID = MIN(ACR.AFFILIATE_ID)
						) AS NAME,
						ACR.CLIENT_ID
				FROM
					AFFILIATE_CLIENT_REL ACR
				GROUP BY
					ACR.CLIENT_ID
			) AS AF
		ON
			C.CLIENT_ID = AF.CLIENT_ID
		LEFT OUTER JOIN
			(
				SELECT
					DR.PATIENT_CODE_ID,
					MAX(DEL.EVENT_START_TIMESTAMP) AS EVENT_START_TIMESTAMP
				FROM
					DOCUMENT_RUN DR
				INNER JOIN
					DOCUMENT_EVENT_LOG_DETAIL DELD
				ON
					DR.DOCUMENT_EVENT_LOG_DETAIL_ID = DELD.ID
					AND DELD.DELETED IS NULL
				INNER JOIN
					DOCUMENT_EVENT_LOG DEL
				ON
					DEL.ID = DELD.DOCUMENT_EVENT_LOG_ID
					AND DEL.DELETED IS NULL
				WHERE
					DR.DELETED IS NULL
					AND DR.PDF_COMPOSE_END_TIMESTAMP IS NOT NULL
				GROUP BY DR.PATIENT_CODE_ID
			) AS DOC
		ON
			DOC.PATIENT_CODE_ID = JAT.PATIENT_CODE_ID
  		LEFT JOIN
				dbo.iter$simple_intlist_to_tbl(@COMPANY_CODE) LI
			ON
				J.CLIENT_DSCR_ID1 = LI.NUMBER

		WHERE
			1 = 1 AND
			CASE when @job_ID <> 0 then @job_ID else -1 end = case when @job_ID <> 0 then j.JOb_ID  else -1 end AND
			CASE when (@COMPANY_NAME IS NOT NULL AND RTRIM(LTRIM(@COMPANY_NAME)) <> '') then C.CLIENT_NAME else -1 end LIKE case when (@COMPANY_NAME IS NOT NULL AND RTRIM(LTRIM(@COMPANY_NAME)) <> '') then '%' + @COMPANY_NAME + '%'  else -1 end AND
			CASE WHEN @AFFILIATE_ID <> 0 then  @AFFILIATE_ID else -1 end = case when @AFFILIATE_ID <> 0 then AF.AFFILIATE_ID else -1 END
		GROUP BY
			J.JOB_ID,
			J.CLIENT_DSCR_ID1,
			C.CLIENT_NAME,
			J.START_DATE,
			J.JOB_TYPE,
			J.END_DATE,
			AF.AFFILIATE_ID,
			AF.NAME,
		    CL.LOCATION_NAME

		


END

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Robert Schutt
Robert Schutt
Flag of Netherlands image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer