CODEWITHSUNDEEP
sqlgroup-by
Regional Med
Regional Med

Reputation: 31

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

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

Upvotes: 3

Views: 12603

Answers (2)

MatBailie
MatBailie

Reputation: 86706

You don't need DISTINCT and GROUP BY at the same time. By definition the records will be distinct, because all similar records are in the same group...

Also, you don't need to GROUP BY a constant.

This give...

SELECT    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, 
          TSM180_CAT.cod_dtl_ds

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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

Upvotes: 6

Related Questions