GROUP BY expression must contain at least one column that is not an outer reference

Question

There are several posts on this same topic, and I've read through them...but try as I might, I can't apply them to my issue. I'm sure it's something dumb I'm overlooking.

What I'm trying to do is to show patients that were in a bed at a specific date and time. Each patient has an admit and discharge date, so I'm using those to determine if they were in a bed on that particular date & time. If I'm listing everything out, it works great. Now I'm trying to do a summary so it just shows 3 inpatients and 1 outpatient on a given day in this location, etc. But I'm getting the error message in the title.

SELECT DISTINCT TSM950_STATION.loc_ext_id AS STATION, 
convert(varchar(10),DATEADD(DAY,-7,GETDATE()),101) AS CENSUS_DATE,
                  TSM180_CAT.cod_dtl_ds,
                  COUNT(TPM300_PAT_VISIT.vst_ext_id)
FROM         TPM300_PAT_VISIT INNER JOIN
                  TSM040_PERSON_HDR ON TPM300_PAT_VISIT.psn_int_id = TSM040_PERSON_HDR.psn_int_id INNER JOIN
                  TSM950_LOCATION_REF AS TSM950_ROOM ON TPM300_PAT_VISIT.loc_lvl_4_id = TSM950_ROOM.loc_int_id INNER JOIN
                  TSM950_LOCATION_REF AS TSM950_BED ON TPM300_PAT_VISIT.loc_lvl_5_id = TSM950_BED.loc_int_id  INNER JOIN
                  TSM950_LOCATION_REF AS TSM950_STATION ON TPM300_PAT_VISIT.loc_lvl_3_id = TSM950_STATION.loc_int_id INNER JOIN
                  TSM180_MST_COD_DTL AS TSM180_CAT ON TPM300_PAT_VISIT.pat_cat_cd = TSM180_CAT.cod_dtl_int_id
WHERE     (TSM950_STATION.loc_ext_id IN ('MS', 'OB', 'SCU', 'NURS')) AND 
        (convert(datetime,convert(varchar(10),TPM300_PAT_VISIT.adm_ts,101) +' 00:00:00'))<=convert(datetime,convert(varchar(10),DATEADD(DAY,-7,GETDATE()),101) +' 23:58:00') AND
        (convert(datetime,convert(varchar(10),TPM300_PAT_VISIT.dschrg_ts,101) +' 00:00:00'))>=convert(datetime,convert(varchar(10),DATEADD(DAY,-7,GETDATE()),101) +' 23:59:00')
GROUP BY        TSM950_STATION.loc_ext_id, 
                 convert(varchar(10),DATEADD(DAY,-7,GETDATE()),101),
                  TSM180_CAT.cod_dtl_ds 

Answer 1

The problem is that the following expression is a constant:

convert(varchar(10),DATEADD(DAY,-7,GETDATE()),101), 

Simply remove this from your group by, so it looks like:

GROUP BY TSM950_STATION.loc_ext_id, TSM180_CAT.cod_dtl_ds