Help with summing the data by certain fields

Transact-SQL
1

I have the following code that is working but I need to take it a step further and I dont know how.
I would like to sum the patient day field by ADT Location Name. I would like to end up with only 6 rows.

SELECT *
FROM
(
SELECT
'Patient Days' AS 'TYPE OF REPORT'
,1 AS 'Patient Day'
,ENC.HSP_ACCOUNT_ID 'HAR_ID'
,ADT.EVENT_ID 'ADT Event ID'
,RIGHT(DEP.DEPARTMENT_ID,7) 'ADT Department ID'
,DEP.DEPARTMENT_NAME 'ADT Department Name'
,cl.LOC_NAME 'ADT Location Name'
,ADT.ROOM_ID 'ADT Room ID'
,ADT.BED_ID 'Bed ID'
,ADT.BED_CSN_ID 'Bed CSN ID'
,ADT.PAT_ENC_CSN_ID 'Pat Enc CSN ID'
,FIN.NAME 'Financial Class'
,ZC_ACCT.NAME 'Account Class'
,ZC_PAT_CLASS.NAME 'Patient Class'
,LABOR.NAME 'Labor Status'
,ENC.HOSP_ADMSN_TIME 'Hospital Admit Date'
,ENC.HOSP_DISCH_TIME 'Hospital Discharge Date'
,ADT.EVENT_TIME 'Event Time'
,ADT.EFFECTIVE_TIME 'Effective Time'
FROM
CLARITY_ADT ADT
INNER JOIN PAT_ENC_HSP ENC ON ADT.PAT_ENC_CSN_ID = ENC.PAT_ENC_CSN_ID
LEFT OUTER JOIN ZC_LABOR_STATUS LABOR ON ENC.LABOR_STATUS_C = LABOR.LABOR_STATUS_C
INNER JOIN HSP_ACCOUNT HSP ON ENC.HSP_ACCOUNT_ID = HSP.HSP_ACCOUNT_ID
INNER JOIN ZC_FIN_CLASS FIN ON HSP.ACCT_FIN_CLASS_C = FIN.FIN_CLASS_C
INNER JOIN CLARITY_BED BED ON ADT.BED_CSN_ID = BED.BED_CSN_ID
INNER JOIN CLARITY_DEP DEP ON ADT.DEPARTMENT_ID = DEP.DEPARTMENT_ID
LEFT JOIN CLARITY_LOC cl ON DEP.REV_LOC_ID = cl.LOC_ID
INNER JOIN ZC_ACCT_CLASS_HA ZC_ACCT ON ADT.PAT_CLASS_C = ZC_ACCT.ACCT_CLASS_HA_C
LEFT OUTER JOIN ZC_PAT_CLASS on ENC.ADT_PAT_CLASS_C = ZC_PAT_CLASS.ADT_PAT_CLASS_C
INNER JOIN
(
----------------------Updated by Rohit--------------------------------------------------
select
interp.EVENT_ID
,interp.LINE
,interp.FROM_BASE_CLASS_C
from ADT_INTERPRETATION interp
inner join
(
select
EVENT_ID
,max(LINE) LINE
from ADT_INTERPRETATION
group by EVENT_ID
) tab1 on interp.EVENT_ID = tab1.EVENT_ID and interp.LINE = tab1.LINE
----------------------------------------------------------------------------------------
) adt_int ON adt_int.EVENT_ID = adt.EVENT_ID
WHERE
ADT.EVENT_TYPE_C = 6 --CENSUS
AND (ADT.FROM_BASE_CLASS_C = 1 --INPATIENT; IF EVENT IS CANCELLED, MUST PULL BASE CLASS FROM INTERPRET TABLE.
OR adt_int.FROM_BASE_CLASS_C = 1)
AND ENC.ADT_PAT_CLASS_C <> '107' --EXCLUDE NEWBORN
AND ENC.ADT_PAT_CLASS_C NOT IN ('124','126','117','120')
AND ADT.EFFECTIVE_TIME BETWEEN @fromDate AND @toDate
AND BED.CENSUS_INCLUSN_YN = 'Y' --CENSUS BEDS ONLY
AND ((ADT.EVENT_TIME < @toDate AND ADT.EVENT_SUBTYPE_C <> 2)
OR (ADT.EVENT_SUBTYPE_C = 2 AND ADT.DELETE_TIME > @toDate))--ME+1 DAY; ME
--USE THIS NEXT ROW FOR TESTING----------------------------
--AND ENC.HSP_ACCOUNT_ID = 2100015001

UNION ALL

-- Amendments for previous months
SELECT 
	'Amendments' AS 'TYPE OF REPORT'
	,CASE
		WHEN (adt.EVENT_SUBTYPE_C <> 2) THEN +1
		ELSE -1
	END AS 'Patient Day'
	,ENC.HSP_ACCOUNT_ID 'HAR_ID'
	,ADT.EVENT_ID 'ADT Event ID'
	,RIGHT(DEP.DEPARTMENT_ID,7) 'ADT Department ID'
	,DEP.DEPARTMENT_NAME 'ADT Department Name'
	,cl.LOC_NAME 'ADT Location Name'
	,ADT.ROOM_ID 'ADT Room ID'
	,ADT.BED_ID 'Bed ID'
	,ADT.BED_CSN_ID 'Bed CSN ID'
	,ADT.PAT_ENC_CSN_ID 'Pat Enc CSN ID'
	,FIN.NAME 'Financial Class'
	,ZC_ACCT.NAME 'Account Class'
	,ZC_PAT_CLASS.NAME 'Patient Class'
	,LABOR.NAME 'Labor Status'
	,ENC.HOSP_ADMSN_TIME 'Hospital Admit Date'
	,ENC.HOSP_DISCH_TIME 'Hospital Discharge Date'
	,ADT.EVENT_TIME 'Event Time'
	,ADT.EFFECTIVE_TIME 'Effective Time'
FROM
	CLARITY_ADT ADT
	INNER JOIN PAT_ENC_HSP ENC ON ADT.PAT_ENC_CSN_ID = ENC.PAT_ENC_CSN_ID
	LEFT OUTER JOIN ZC_LABOR_STATUS LABOR ON ENC.LABOR_STATUS_C = LABOR.LABOR_STATUS_C
	INNER JOIN HSP_ACCOUNT HSP ON ENC.HSP_ACCOUNT_ID = HSP.HSP_ACCOUNT_ID
	INNER JOIN ZC_FIN_CLASS FIN ON HSP.ACCT_FIN_CLASS_C = FIN.FIN_CLASS_C
	INNER JOIN CLARITY_BED BED ON ADT.BED_CSN_ID = BED.BED_CSN_ID
	INNER JOIN CLARITY_DEP DEP ON ADT.DEPARTMENT_ID = DEP.DEPARTMENT_ID
	LEFT JOIN CLARITY_LOC cl ON DEP.REV_LOC_ID = cl.LOC_ID
	INNER JOIN ZC_ACCT_CLASS_HA ZC_ACCT ON ADT.PAT_CLASS_C = ZC_ACCT.ACCT_CLASS_HA_C
	LEFT OUTER JOIN ZC_PAT_CLASS on ENC.ADT_PAT_CLASS_C = ZC_PAT_CLASS.ADT_PAT_CLASS_C
	LEFT OUTER JOIN CLARITY_ADT adt_previous ON adt.CANC_EVENT_ID = adt_previous.EVENT_ID
	INNER JOIN
	(
	----------------------Updated by Rohit--------------------------------------------------
		select 
			interp.EVENT_ID
			,interp.LINE
			,interp.FROM_BASE_CLASS_C
		from ADT_INTERPRETATION interp
		inner join
		(
			select
				EVENT_ID
				,max(LINE) LINE
			from ADT_INTERPRETATION
			group by EVENT_ID
		) tab1 on interp.EVENT_ID = tab1.EVENT_ID and interp.LINE = tab1.LINE
	----------------------------------------------------------------------------------------
	) adt_int  ON adt_int.EVENT_ID = adt.EVENT_ID
WHERE
	ADT.EVENT_TYPE_C = 6                                  --CENSUS    
	AND (ADT.FROM_BASE_CLASS_C = 1                        --INPATIENT; IF EVENT IS CANCELLED, MUST PULL BASE CLASS FROM INTERPRET TABLE.
	OR adt_int.FROM_BASE_CLASS_C = 1)
	AND ENC.ADT_PAT_CLASS_C <> '107' 
          --EXCLUDE NEWBORN  
	AND ENC.ADT_PAT_CLASS_C NOT IN ('124','126','117','120') 
	AND ADT.EFFECTIVE_TIME < @fromDate
	AND BED.CENSUS_INCLUSN_YN = 'Y'                       --CENSUS BEDS ONLY
	AND ((adt.EVENT_SUBTYPE_C <> 2 AND adt.EVENT_TIME BETWEEN @fromDate AND @toDate)  --Not canceled and entered during timeframe
		OR (adt.EVENT_SUBTYPE_C = 2 AND adt.DELETE_TIME BETWEEN @fromDate AND @toDate))  --Canceled during timeframe
	--USE THIS NEXT ROW FOR TESTING----------------------------
	--AND ENC.HSP_ACCOUNT_ID = 2100015001

) AS PATIENT_DAYS

2

Note that if EVENT_TIME is a Date and Time datatype, as its name suggests, then any entry with a time on the last day (i.e. @toDate) will be excluded if you use BETWEEN. The normal way around this would be:

AND adt.EVENT_TIME >= @fromDate 
AND adt.EVENT_TIME <  DATEADD(Day, 1, @toDate)

You might be able to change your SELECT * at the top to

SELECT [ADT Location Name], SUM([Patient Day]) AS [Total Patient Days]
FROM
(
... the guts of the original query ...
) AS PATIENT_DAYS
GROUP BY [ADT Location Name]
ORDER BY [ADT Location Name]

Performance will probably be dreadful (because of all the other work the inner query does, which is not needed by the outer query), but if this is a one off type report that may not matter.

3

Thank you!