有几篇关于同一主题的帖子,我已经通读了它们......但是尽我所能尝试,我无法将它们应用于我的问题。 我敢肯定这是我忽略的愚蠢的东西。
我想做的是向在特定日期和时间躺在床上的病人展示。 每个患者都有一个入院和出院日期,所以我用这些日期来确定他们是否在那个特定的日期和时间躺在床上。 如果我列出所有内容,效果很好。 现在我正在尝试做一个总结,所以它只显示这个位置一天的 3 名住院病人和 1 名门诊病人,等等。 但是我在标题中收到错误消息。
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
问题是以下表达式是一个常量:
convert(varchar(10),DATEADD(DAY,-7,GETDATE()),101),
只需将其从您的组中删除,使其看起来像:
GROUP BY TSM950_STATION.loc_ext_id, TSM180_CAT.cod_dtl_ds
您不需要同时DISTINCT
和GROUP BY
。 根据定义,记录将是不同的,因为所有相似的记录都在同一组中......
此外,您无需GROUP BY
常量。
这给...
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