时间戳

首页 > 解决方案 > 最新日期字段

sql - 最新日期字段

问题描述

首先，我是一名程序员，我没有任何 SQL 课程并且是自学的。我想要的所有数据都很好，但是我在日期字段上设置了一个 MAX，它仍然给了我表格中的所有日期。我只想要最新的。我也有多个连接，所以我确定我没有正确设置一些东西。任何帮助将不胜感激。

我使用 Pentaho 作为作家

SELECT DISTINCT
     "RPT_CLIENT_PAYER_PLAN"."PAYER_PLAN_NAME",
     "RPT_CLIENT"."CLIENT_NUMBER",
     "RPT_CLIENT_PROGRAMS"."PROGRAM_ID",
     "RPT_CLIENT_DSM5_DIAG"."DSM5_P1_CODE",
     "RPT_CLIENT_DSM5_DIAG"."DSM5_P1_DESCRIPTION",
     "RPT_CLIENT_DSM5_DIAG"."DSM5_P2_CODE",
     "RPT_CLIENT_DSM5_DIAG"."DSM5_P2_DESCRIPTION",
     "RPT_CLIENT_DSM5_DIAG"."DSM5_P3_CODE",
     "RPT_CLIENT_DSM5_DIAG"."DSM5_P3_DESCRIPTION",
     "RPT_CLIENT_PROGRAMS"."PROGRAM",
     "RPT_CLIENT_PROGRAMS"."BEGIN_DATE",
     "RPT_CLIENT"."AGE",
     "RPT_CLIENT"."GENDER",
     "RPT_CLIENT"."MILITARY_SERVICES",
     "RPT_CLIENT"."MILITARY_STATUS",
     "RPT_SVCDOC_TOBACCO"."SMOKING_STATUS",
     "RPT_SVCDOC_REFERRAL"."REFER_COMMENT",
     "RPT_SVCDOC_REFERRAL"."REASON_FOR_REFERRAL",
     "RPT_CLIENT_PAYER_PLAN"."END_DATE",
     "RPT_CLIENT_PROGRAMS"."END_DATE",
     MAX("RPT_CLIENT_DSM5_DIAG"."EFFECTIVE_DATE") as max_date
FROM
     "RPT_CLIENT_PROGRAMS" INNER JOIN "RPT_CLIENT" ON "RPT_CLIENT_PROGRAMS"."CLIENT_ID" = "RPT_CLIENT"."CLIENT_ID"
     INNER JOIN "RPT_CLIENT_DSM5_DIAG" ON "RPT_CLIENT_PROGRAMS"."CLIENT_ID" = "RPT_CLIENT_DSM5_DIAG"."CLIENT_ID"
     INNER JOIN "RPT_CLIENT_PAYER_PLAN" ON "RPT_CLIENT_DSM5_DIAG"."CLIENT_ID" = "RPT_CLIENT_PAYER_PLAN"."CLIENT_ID"
     INNER JOIN "RPT_SVCDOC_TOBACCO" ON "RPT_CLIENT_PAYER_PLAN"."CLIENT_ID" = "RPT_SVCDOC_TOBACCO"."CLIENT_ID"
     INNER JOIN "RPT_SVCDOC_REFERRAL" ON "RPT_CLIENT"."CLIENT_NUMBER" = "RPT_SVCDOC_REFERRAL"."CLIENT_NUMBER"
WHERE
     "RPT_CLIENT_PROGRAMS"."PROGRAM_ID" IN (1087)
     and "RPT_CLIENT_PROGRAMS"."BEGIN_DATE" >= ${Begin_Date}
     and "RPT_CLIENT_PAYER_PLAN"."END_DATE" IS NULL

GROUP BY
    "RPT_CLIENT_PAYER_PLAN"."PAYER_PLAN_NAME",
     "RPT_CLIENT"."CLIENT_NUMBER",
     "RPT_CLIENT_PROGRAMS"."PROGRAM_ID",
     "RPT_CLIENT_DSM5_DIAG"."DSM5_P1_CODE",
     "RPT_CLIENT_DSM5_DIAG"."DSM5_P1_DESCRIPTION",
     "RPT_CLIENT_DSM5_DIAG"."DSM5_P2_CODE",
     "RPT_CLIENT_DSM5_DIAG"."DSM5_P2_DESCRIPTION",
     "RPT_CLIENT_DSM5_DIAG"."DSM5_P3_CODE",
     "RPT_CLIENT_DSM5_DIAG"."DSM5_P3_DESCRIPTION",
     "RPT_CLIENT_PROGRAMS"."PROGRAM",
     "RPT_CLIENT_PROGRAMS"."BEGIN_DATE",
     "RPT_CLIENT"."AGE",
     "RPT_CLIENT"."GENDER",
     "RPT_CLIENT"."MILITARY_SERVICES",
     "RPT_CLIENT"."MILITARY_STATUS",
     "RPT_SVCDOC_TOBACCO"."SMOKING_STATUS",
     "RPT_SVCDOC_REFERRAL"."REFER_COMMENT",
     "RPT_SVCDOC_REFERRAL"."REASON_FOR_REFERRAL",
     "RPT_CLIENT_PAYER_PLAN"."END_DATE",
     "RPT_CLIENT_PROGRAMS"."END_DATE",
     "RPT_CLIENT_DSM5_DIAG"."EFFECTIVE_DATE"

希望得到最新的 DMS-5 生效日期，但仍然给我一切。

标签： sqldatemax

解决方案

您有maxonEFFECTIVE_DATE列，但它也是group by. 由于它是 group by 的一部分，因此该列的每个唯一值都会单独出现。从中删除它group by，它应该可以工作。

推荐阅读