如何在SQL Server 2014中获取具有顶级日期的记录?

Question

我有一个帐户表和一个语句摘要表。

"对账单摘要"表包含每个账户每月发送的对账单的数据。但有时，出于某种原因，报表不会发送给帐户持有人。

我需要一份帐户列表，其中：

1. 该声明至少6个月没有发出
2. 未结余额至少为 1000 美元

我的代码：

SELECT A.AccountNumber
,StatementDate
--,MAX(StatementDate)
,AmountDue
,InvoiceNumber

FROM [StatementSummary] S
INNER JOIN Account A ON S.AccountID = A.AccountId
WHERE AmountDue >= 1000
AND StatementDate <= '2017-02-10'  --Should be (sysdate-180)
--GROUP BY A.AccountNumber, StatementDate
ORDER BY StatementDate DESC

此代码为我提供了 6 个月前对账单期间的帐户及其数据列表。但这些账户也有数据供以后的陈述使用。

我需要至少自 6 个月前以来未收到任何对账单的帐户。

---

---

编辑：****客户
表中使用的唯一字段是
1。账户编号
2( 帐号

语句摘要表中使用的字段为
1。帐户
ID 2.发票编号
3。声明日期
4.新收费
5.到期金额

每个月都会生成一个对账单，每个月都会在该帐户上产生新的费用。

我需要 LAST 语句中的详细信息，其中：

1( 最后结单日期早于6个月前及
2(到期金额至少为$1000

示例数据：

AccountNumber   StatementDate AmountDue InvoiceNumber
32563696        2017-07-16      1259.05 2279250276
32563696        2017-06-16      1043.00 2273976792
32563696        2017-05-16       974.00 2273976651
32067247        2017-07-01      5385.84 2277258801
32067247        2017-06-01      4218.71 2271971177
32067247        2017-05-01      2977.56 2276955130
32067247        2017-04-01      1518.85 2274063149
31279191        2017-06-01    214746.49 2271930486
31279191        2017-05-01    184178.38 2276913639
31279191        2017-04-01    141984.13 2274025518
31279191        2017-03-01    110914.52 2270228069
31279191        2017-02-01     76083.25 2257406893
31279191        2017-01-01     45997.75 2253462824

我真正需要的：

AccountNumber     StatementDate AmountDue     InvoiceNumber
11201057          2017-02-01    9114.29       2255223280
11201147          2017-02-01    1189.52       2255223235
11203824          2017-02-01    8984.36       2255223819
11206052          2017-01-01    2274.54       2255223381
11206298          2017-01-01    5792.11       2255223358
11208852          2016-12-01    2175.62       2255223987
11209202          2016-12-01    1199.58       2255223976
11209246          2016-12-01    1017.12       2255256003
11209268          2016-11-01    1775.32       2255256025

---

---

所以，简而言之：我只需要一个帐户列表及其数据，只有在 LAST 对账单发送超过 6 个月的地方，并且只有在到期金额为 1000 美元或更多的情况下。

Answer 1

我认为这应该这样做，基于您发布的内容。虽然样本数据会有所帮助。

WITH CTE AS(
SELECT A.AccountNumber
,StatementDate
--,MAX(StatementDate)
,AmountDue
,InvoiceNumber
,row_number() over (partition by A.AccountNumber order by StatementDate desc) as RN
FROM [StatementSummary] S
INNER JOIN Account A ON S.AccountID = A.AccountId
WHERE
s.AccountID in (select AccountID from StatementSummary group by AccountID having Max(StatementDate) < dateadd(day,-180,getdate()))
and s.AmountDue >= 1000)
select
AccountNumber
,StatementDate
,AmountDue
,InvoiceNumber
from CTE
where
RN = 1

Answer 2

SELECT A.AccountNumber
,StatementDate
--,MAX(StatementDate)
,AmountDue
,InvoiceNumber
FROM [StatementSummary] S
INNER JOIN Account A ON S.AccountID = A.AccountId
WHERE A.AmountDue >= 1000
AND S.StatementDate <= '2017-02-10'

--也许两个表具有相同的列名称？

Answer 3

使用此查询获取 6 个月的案例记录：

Select * From [StatementSummary]
Where datediff(DAY, GETDATE(), StatementDate) Between -180 AND -1

Answer 4

使用not exists()：

SELECT A.AccountNumber
,S.StatementDate
,AmountDue
,InvoiceNumber
FROM [StatementSummary] S
INNER JOIN Account A ON S.AccountID = A.AccountId
WHERE AmountDue >= 1000
and not exists (
select 1 
from [StatementSummary] i
where i.AccountId = a.AccountId
and i.StatementDate > dateadd(month,-6,getdate())
/* other option based on comment */  
and i.StatementDate > dateadd(day,-180,getdate())
)
ORDER BY StatementDate DESC

---

inner join

选项，用于在过去 6 个月内未发送任何对账单时获取帐户的最新对账单：

SELECT A.AccountNumber
,S.StatementDate
,AmountDue
,InvoiceNumber
FROM [StatementSummary] S
INNER JOIN Account A 
ON S.AccountID = A.AccountId
inner join (
select 
AccountId
, MaxStatementDate = max(StatementDate)
from StatementSummary i
group by i.AccountId
having max(StatementDate) <= dateadd(month,-6,getdate())
) x on s.AccountId = x.AccountId 
and s.StatementDate = x.MaxStatementDate
WHERE AmountDue >= 1000
ORDER BY StatementDate DESC