Is there a way to subtract from COUNT(fieldname)?

Transact-SQL
1

I have a query where I need to count the number of patient days, then when it is an amendment, I need to subtract 1 from the counted amount. My query as is, runs, but doesnt subtract the amendments.Can this be done?
The query is as follows:
SELECT DISTINCT
CONVERT (DATE,CLARITY_ADT.EVENT_TIME,101-1)as 'PE Date'
,CASE WHEN LEFT(PAT_ENC_HSP.DEPARTMENT_ID,6) = 101021 THEN 'A' --BRMC
WHEN LEFT(PAT_ENC_HSP.DEPARTMENT_ID,6) = 101022 THEN 'H' --HVMC
WHEN LEFT(PAT_ENC_HSP.DEPARTMENT_ID,6) = 101023 THEN 'L' --LPH
WHEN LEFT(PAT_ENC_HSP.DEPARTMENT_ID,6) = 101024 THEN 'R' --HCMH
WHEN LEFT(PAT_ENC_HSP.DEPARTMENT_ID,6) = 101025 THEN 'S' --SN
WHEN LEFT(PAT_ENC_HSP.DEPARTMENT_ID,6) = 101028 THEN 'M' END AS 'FACILITY' --MVRMC
,CASE WHEN LEFT(PAT_ENC_HSP.DEPARTMENT_ID,6) = 101021 THEN
(SELECT DISTINCT
--case when (min(EVENT_TYPE_C) = '6' and min(EVENT_SUBTYPE_C) in (1,3)) then
--COUNT (PAT_ENC_HSP.PAT_ENC_CSN_ID)
--else (COUNT (PAT_ENC_HSP.PAT_ENC_CSN_ID) - 1) end AS [COUNT]
COUNT (DISTINCT PAT_ENC_HSP.PAT_ENC_CSN_ID) AS [COUNT]
FROM CLARITY_ADT
INNER JOIN PAT_ENC_HSP PAT_ENC_HSP
ON CLARITY_ADT.PAT_ENC_CSN_ID = PAT_ENC_HSP.PAT_ENC_CSN_ID
WHERE EVENT_TYPE_C = '6'
AND EVENT_TIME BETWEEN @StartDate and @EndDate
AND LEFT(CLARITY_ADT.DEPARTMENT_ID,6) IN ('101021')
AND PAT_ENC_HSP.ADT_PAT_CLASS_C = '101')
WHEN LEFT(PAT_ENC_HSP.DEPARTMENT_ID,6) = 101023 THEN
(SELECT DISTINCT
COUNT (DISTINCT PAT_ENC_HSP.PAT_ENC_CSN_ID) AS [COUNT]
FROM CLARITY_ADT
INNER JOIN PAT_ENC_HSP PAT_ENC_HSP
ON CLARITY_ADT.PAT_ENC_CSN_ID = PAT_ENC_HSP.PAT_ENC_CSN_ID
WHERE EVENT_TYPE_C = '6'
AND EVENT_TIME BETWEEN @StartDate and @EndDate
AND LEFT(CLARITY_ADT.DEPARTMENT_ID,6) = ('101023')
AND PAT_ENC_HSP.ADT_PAT_CLASS_C = '101')
WHEN LEFT(PAT_ENC_HSP.DEPARTMENT_ID,6) = 101024 THEN
(SELECT DISTINCT
COUNT (DISTINCT PAT_ENC_HSP.PAT_ENC_CSN_ID) AS [COUNT]
FROM CLARITY_ADT
INNER JOIN PAT_ENC_HSP PAT_ENC_HSP
ON CLARITY_ADT.PAT_ENC_CSN_ID = PAT_ENC_HSP.PAT_ENC_CSN_ID
WHERE EVENT_TYPE_C = '6'
AND EVENT_TIME BETWEEN @StartDate and @EndDate
AND LEFT(CLARITY_ADT.DEPARTMENT_ID,6) = ('101024')
AND PAT_ENC_HSP.ADT_PAT_CLASS_C = '101')
WHEN LEFT(PAT_ENC_HSP.DEPARTMENT_ID,6) = 101025 THEN
(SELECT DISTINCT
COUNT (DISTINCT PAT_ENC_HSP.PAT_ENC_CSN_ID) AS [COUNT]
FROM CLARITY_ADT
INNER JOIN PAT_ENC_HSP PAT_ENC_HSP
ON CLARITY_ADT.PAT_ENC_CSN_ID = PAT_ENC_HSP.PAT_ENC_CSN_ID
WHERE EVENT_TYPE_C = '6'
AND EVENT_TIME BETWEEN @StartDate and @EndDate
AND LEFT(CLARITY_ADT.DEPARTMENT_ID,6) = ('101025')
AND PAT_ENC_HSP.ADT_PAT_CLASS_C = '101')
WHEN LEFT(PAT_ENC_HSP.DEPARTMENT_ID,6) = 101028 THEN
(SELECT DISTINCT
COUNT (DISTINCT PAT_ENC_HSP.PAT_ENC_CSN_ID) AS [COUNT]
FROM CLARITY_ADT
INNER JOIN PAT_ENC_HSP PAT_ENC_HSP
ON CLARITY_ADT.PAT_ENC_CSN_ID = PAT_ENC_HSP.PAT_ENC_CSN_ID
WHERE EVENT_TYPE_C = '6'
AND EVENT_TIME BETWEEN @StartDate and @EndDate
AND LEFT(CLARITY_ADT.DEPARTMENT_ID,6) = ('101028')
AND PAT_ENC_HSP.ADT_PAT_CLASS_C = '101')
WHEN LEFT(PAT_ENC_HSP.DEPARTMENT_ID,6) = 101022 THEN
(SELECT DISTINCT
COUNT (DISTINCT PAT_ENC_HSP.PAT_ENC_CSN_ID) AS [COUNT]
FROM CLARITY_ADT
INNER JOIN PAT_ENC_HSP PAT_ENC_HSP
ON CLARITY_ADT.PAT_ENC_CSN_ID = PAT_ENC_HSP.PAT_ENC_CSN_ID
WHERE EVENT_TYPE_C = '6'
AND EVENT_TIME BETWEEN @StartDate and @EndDate
AND LEFT(CLARITY_ADT.DEPARTMENT_ID,6) = ('101022')
AND PAT_ENC_HSP.ADT_PAT_CLASS_C = '101')
END AS [PATIENT DAYS]

	,CASE WHEN LEFT(PAT_ENC_HSP.DEPARTMENT_ID,6) = 101021 THEN
	(SELECT DISTINCT
	COUNT (DISTINCT PAT_ENC_HSP.PAT_ENC_CSN_ID) AS [COUNT]
	FROM CLARITY_ADT
	INNER JOIN PAT_ENC_HSP PAT_ENC_HSP
	ON CLARITY_ADT.PAT_ENC_CSN_ID = PAT_ENC_HSP.PAT_ENC_CSN_ID
	WHERE EVENT_TYPE_C = '1'
	AND EVENT_TIME BETWEEN @StartDate and @EndDate
	AND LEFT(CLARITY_ADT.DEPARTMENT_ID,6) IN ('101021')
	AND PAT_ENC_HSP.ADT_PAT_CLASS_C = '101')
	WHEN LEFT(PAT_ENC_HSP.DEPARTMENT_ID,6) = 101023 THEN
	(SELECT DISTINCT
	COUNT (DISTINCT PAT_ENC_HSP.PAT_ENC_CSN_ID) AS [COUNT]
	FROM CLARITY_ADT
	INNER JOIN PAT_ENC_HSP PAT_ENC_HSP
	ON CLARITY_ADT.PAT_ENC_CSN_ID = PAT_ENC_HSP.PAT_ENC_CSN_ID
	WHERE EVENT_TYPE_C = '1'
	AND EVENT_TIME BETWEEN @StartDate and @EndDate
	AND LEFT(CLARITY_ADT.DEPARTMENT_ID,6) = ('101023')
	AND PAT_ENC_HSP.ADT_PAT_CLASS_C = '101')
	WHEN LEFT(PAT_ENC_HSP.DEPARTMENT_ID,6) = 101024 THEN
	(SELECT DISTINCT
	COUNT (DISTINCT PAT_ENC_HSP.PAT_ENC_CSN_ID) AS [COUNT]
	FROM CLARITY_ADT
	INNER JOIN PAT_ENC_HSP PAT_ENC_HSP
	ON CLARITY_ADT.PAT_ENC_CSN_ID = PAT_ENC_HSP.PAT_ENC_CSN_ID
	WHERE EVENT_TYPE_C = '1'
	AND EVENT_TIME BETWEEN @StartDate and @EndDate
	AND LEFT(CLARITY_ADT.DEPARTMENT_ID,6) = ('101024')
	AND PAT_ENC_HSP.ADT_PAT_CLASS_C = '101')
	WHEN LEFT(PAT_ENC_HSP.DEPARTMENT_ID,6) = 101025 THEN
	(SELECT DISTINCT
	COUNT (DISTINCT PAT_ENC_HSP.PAT_ENC_CSN_ID) AS [COUNT]
	FROM CLARITY_ADT
	INNER JOIN PAT_ENC_HSP PAT_ENC_HSP
	ON CLARITY_ADT.PAT_ENC_CSN_ID = PAT_ENC_HSP.PAT_ENC_CSN_ID
	WHERE EVENT_TYPE_C = '1'
	AND EVENT_TIME BETWEEN @StartDate and @EndDate
	AND LEFT(CLARITY_ADT.DEPARTMENT_ID,6) = ('101025')
	AND PAT_ENC_HSP.ADT_PAT_CLASS_C = '101')
	WHEN LEFT(PAT_ENC_HSP.DEPARTMENT_ID,6) = 101028 THEN
	(SELECT DISTINCT
	COUNT (DISTINCT PAT_ENC_HSP.PAT_ENC_CSN_ID) AS [COUNT]
	FROM CLARITY_ADT
	INNER JOIN PAT_ENC_HSP PAT_ENC_HSP
	ON CLARITY_ADT.PAT_ENC_CSN_ID = PAT_ENC_HSP.PAT_ENC_CSN_ID
	WHERE EVENT_TYPE_C = '1'
	AND EVENT_TIME BETWEEN @StartDate and @EndDate
	AND LEFT(CLARITY_ADT.DEPARTMENT_ID,6) = ('101028')
	AND PAT_ENC_HSP.ADT_PAT_CLASS_C = '101')
	WHEN LEFT(PAT_ENC_HSP.DEPARTMENT_ID,6) = 101022 THEN
	(SELECT DISTINCT
	COUNT (DISTINCT PAT_ENC_HSP.PAT_ENC_CSN_ID) AS [COUNT]
	FROM CLARITY_ADT
	INNER JOIN PAT_ENC_HSP PAT_ENC_HSP
	ON CLARITY_ADT.PAT_ENC_CSN_ID = PAT_ENC_HSP.PAT_ENC_CSN_ID
	WHERE EVENT_TYPE_C = '1'
	AND EVENT_TIME BETWEEN @StartDate and @EndDate
	AND LEFT(CLARITY_ADT.DEPARTMENT_ID,6) = ('101022')
	AND PAT_ENC_HSP.ADT_PAT_CLASS_C = '101')
	END AS [ADMISSIONS]

	,'0' AS [REGISTRATIONS]  --ADDED 02.21.15
	,'0'  AS [DISCHARGES]  --ADDED 02.21.15

FROM PAT_ENC_HSP
INNER JOIN CLARITY_DEP CLARITY_DEP ON PAT_ENC_HSP.DEPARTMENT_ID = CLARITY_DEP.DEPARTMENT_ID
INNER JOIN CLARITY_ADT CLARITY_ADT ON PAT_ENC_HSP.PAT_ENC_CSN_ID = CLARITY_ADT.PAT_ENC_CSN_ID

WHERE PAT_ENC_HSP.ADT_PAT_CLASS_C = '101'
AND LEFT(PAT_ENC_HSP.DEPARTMENT_ID,6) IN ('101021')
--,'101022','101023','101024','101028')
--AND CONVERT (DATE,CLARITY_ADT.EVENT_TIME,101) = CONVERT (DATE, (CURRENT_TIMESTAMP-1),101)
AND CLARITY_ADT.EVENT_TIME BETWEEN @StartDate and @EndDate
--AND PAT_ENC_HSP.HOSP_DISCH_TIME IS NULL

2

The query you posted is long and unwieldy for someone to read and understand, especially when they don't have access to your tables. What I would suggest is to construct a simpler query and simpler problem that demonstrates what you want to accomplish.

Without knowing anything much further, what I would guess is that you will need something like this:

-- instead of this
COUNT(DISTINCT PAT_ENC_HSP.PAT_ENC_CSN_ID) AS [COUNT]

-- something like this
SUM(CASE WHEN isAmendment = 0 THEN 1 ELSE -1 END) AS [COUNT]
3

I pointed out the danger of using BETWEEN in this way in your previous question

Your previous question included:

,CASE
		WHEN (adt.EVENT_SUBTYPE_C <> 2) THEN +1
		ELSE -1
	END AS 'Patient Day'

is that the answer to being able to generate a running-total of Patient Days including Amendments?

4

Kristen,
I am not sure what you are asking here?

5

How can I set up the "isAmendment" field?

6

I don't know the answer to that question. It depends on your business logic and the data you have available to you. Look at the data for one row in your table(s) and ask yourself how you (the human being) would determine if that row is an amendment row or not. For example, there might be some charge code, or event code, or some other combination of data that lets you make that determination. You will have to code that logic so the query can figure out whether it is amendment or not.

Another approach you could try is to construct a simplified case that is representative of the problem you want to solve and get that working correctly.

7

Thank you for your patience. Your explanation was very clear and helpful.