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
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
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.