Question

同一议题上有许多文章, 我读过这些文章, 但尽量尝试, 我无法将这些文章应用到议题上。我相信我忽略了这些愚蠢的事。

我想做的是让在某个特定日期和时间在床上的病人看一看。每个病人都有入院和出院日期,所以我用这些方法来确定他们是否在那个特定日期和印章;时间的床上。如果我把一切都列出来,效果会很好。现在我想做一个摘要,这样就可以在这个地点每天显示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

Answer 1

问题是以下表达式是一个常数:

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

简单地把这个从你的团体中移除, 所以它看起来像:

GROUP BY TSM950_STATION.loc_ext_id, TSM180_CAT.cod_dtl_ds

Answer 2

您不需要同时使用 < code> DISTINCT 和 < code> gROUP by 。根据定义,记录是不同的, 因为所有类似的记录都在同一组中...

另外,您不需要使用 GROUP 的常数 。

这给...

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

友情链接