时间戳

首页 > 解决方案 > SQL - 计数记录和提取最小日期

sql - SQL - 计数记录和提取最小日期

问题描述

我有两张正在工作的桌子。一个用于合同 (CNT)，包含 ID#、管理员、到期日期等所有相关信息。另一个用于修订 (AMD)，它具有类似信息，但显然只关注合同的修订。它们看起来像这样：

CNTRCT_ID | CNTRCT_ADMIN | CNTRCT_START_DT | .....
12345       J. Bloggs      01-01-2018        .....
54321       A. Nonymous    02-01-2018        .....
12121       B. Atman       03-01-2018        .....

REF_ID    | AMEND_ID     | AMEND_DT        | .....
12345       A00123         06-06-2018        .....
12345       A00265         09-09-2018        .....
54321       A02451         05-04-2018        .....

我想要做的是加入两个表（CNT.Contract_ID = AMD.Ref_Id 是键），这很好，但我遇到的问题是计数和最小日期。加入后，您会得到一个输出，其中合同信息将重复（对于 Amd 1、2 等），因为每个修订都有一个唯一的 Amendment_ID（不是与合同 ID 相关的 Ref_ID）。所以我们得到：

CNTRCT_ID | CNTRCT_ADMIN | CNTRCT_START_DT | REF_ID    | AMEND_ID     | AMEND_DT |
12345       J. Bloggs      01-01-2018        12345       A00123         06-06-2018
12345       J. Bloggs      01-01-2018        12345       A00265         09-09-2018
54321       A. Nonymous    02-01-2018        54321       A02451         05-04-2018

由于不存在 AMD.REF_ID = '12121'，合同 12121 的条目被删除。

基本上，我想提取合同信息，然后将其与修订信息联系起来，如果适用，但是 a) 计算每份合同的修订数量（注意：这里只取最大修订数量是行不通的，因为有些人有一个或多个第 0 次修订为此必须计算在内）。我正在努力实现的是风格的输出：

CNTRCT_ID | AMD_COUNT | FIRST_AMD_DT
12345       2           06-06-2018
54321       1           05-04-2018
12121       0           N/A

我可以写

COUNT(*) over (partition by CNT.CNTRCT_ID) AS AMD_COUNT

这将汇总所有合同条目并在新列中输出计数，我可以将其与 Select 子句中的 DISTINCT(CNT.CNTRCT_ID) 结合起来，这会删除重复项，但我还需要其中最早的修订日期，我我很难在不扔东西的情况下把它放进去。如果我使用

select min(AMEND_DT) from dbo.AMENDMENTS AMD1 where AMD1.REF_ID = AMD.REF_ID

我好像输错了计数。如果我删除了 Min Date 字段和条款，我知道的一份合同有 11 个修正输出 11，但只有 2 个。我正在寻找任何帮助以准确结果获得上述输出的任何帮助。

这是我所拥有的骨架。我确定我缺少一些明显的东西，但任何帮助将不胜感激。

SELECT   DISTINCT(CNT.CNTRCT_ID), CNT.CNTRCT_TYP_CD (& various CNT fields), AMD.AMEND_DT,
         COUNT(*) over (partition by CNT.CNTRCT_ID) AS AMD_COUNT
FROM     dbo.AMENDMENTS AMD, 
         dbo.CONTRACTS CNT 
WHERE    CNT.CNTRCT_ID=AMD.REF_ID 
AND      AMD.AMEND_DT = (select min(AMEND_DT) from dbo.AMENDMENTS AMD1 where AMD1.REF_ID=AMD.REF_ID)

标签： sql

解决方案

也许这对你有用：

 WITH CTE AS (SELECT MIN(AMEND_DT) AS FIRST_AMD_DT,  COUNT(*) AS AMD_COUNT, REF_ID FROM AMENDMENTS GROUP BY REF_ID)
 SELECT CNTRCT_ID, COALESCE(AMD_COUNT,0) AS AMD_COUNT, FIRST_AMD_DT AS  FIRST_AMD_DT FROM CONTRACTS LEFT JOIN CTE ON CNTRCT_ID = REF_ID

希望能帮助到你。

推荐阅读