时间戳

首页 > 解决方案 > SQL：如何检查付款记录中的重叠和漏洞

sql - SQL：如何检查付款记录中的重叠和漏洞

问题描述

我确实有一个PaymentSchedules包含百分比信息的表格，以及这些百分比有效的日期，资源逐个资源：

| auto_numbered | res_id | date_start | date_end   | org   | pct |
|---------------+--------+------------+------------+-------+-----|
|             1 | A      | 2018-01-01 | 2019-06-30 | One   | 100 |
|             2 | A      | 2019-07-01 | (NULL)     | One   |  60 |
|             3 | A      | 2019-07-02 | 2019-12-31 | Two   |  40 |
|             4 | A      | 2020-01-01 | (NULL)     | Two   |  40 |
|             5 | B      |     (NULL) | (NULL)     | Three | 100 |
|             6 | C      | 2018-01-01 | (NULL)     | One   | 100 |
|             7 | C      | 2019-11-01 | (NULL)     | Four  | 100 |

（记录#3 和#4 可以总结为一行，但故意重复，以表明date_start和的组合有很多date_end。）

快速读取数据：

所以，当我们生成 2019 年（从 2019-01-01 到 2019-12-31）的财务报告时，我们会出现计算错误...

所以，问题是：我们如何确保我们没有重叠的资源支付，或者 - 也相反 - 在一段时间内出现“漏洞”？

如何编写SQL 查询来检查既没有少付也不多付的资源？也就是说，表中的所有资源都应在所查看的财务期间的每一天由一个或多个组织以总百分比始终等于100%的方式支付。

我不知道如何进行这样的查询。任何人都可以给出提示，让我走上正轨？

编辑——同时使用 SQL Server 和 Oracle。

编辑——我不拥有数据库，我不能添加触发器或视图。我需要能够“事后”检测事物......需要轻松发现冲突记录或“丢失”记录（以防“周期漏洞”），手动修复它们，然后重新运行财务报告。

编辑——如果我们对 2019 年进行分析，则需要以下报告：

| res_id | pct_sum |       date |
|--------+---------+------------|
| A      |      60 | 2019-07-01 |
| C      |     200 | 2019-11-01 |
| C      |     200 | 2019-11-02 |
| C      |     200 |        ... |
| C      |     200 |        ... |
| C      |     200 |        ... |
| C      |     200 | 2019-12-30 |
| C      |     200 | 2019-12-31 |

或者，当然，更好的版本——肯定无法获得？-- 一种类型的问题会出现一次，以及观察到问题的相关日期范围：

| res_id | pct_sum | date_start |   date_end |
|--------+---------+------------+------------|
| A      |      60 | 2019-07-01 | 2019-07-01 |
| C      |     200 | 2019-11-01 | 2019-12-31 |

编辑——小提琴代码：db<>fiddle here

标签： sqljoinwhile-loopgroup-bydatabase-cursor

解决方案

这是对 Sql Server 的不完整尝试。

基本上，这个想法是使用递归 CTE 为每个 res_id 展开几个月。

然后将“可能是什么”加入现有日期范围。

但我怀疑它可以在适用于 Oracle 和 MS Sql Server 的 sql 中完成。
当然，两者都有窗口函数和 CTE。
但是对于不同的 RDMS，日期时间函数很少相同。

所以我放弃了。
也许其他人会找到更简单的解决方案。 

create table PaymentSchedules
(
  auto_numbered int identity(1,1) primary key,
  res_id varchar(30),
  date_start date,
  date_end date,
  org varchar(30),
  pct decimal(3,0)
)
GO

✓

insert into PaymentSchedules
(res_id, org, pct, date_start, date_end)
values
  ('A', 'One',   100, '2018-01-01', '2018-06-30')
, ('A', 'One',   100, '2019-01-01', '2019-06-30')
, ('A', 'One',    60, '2019-07-01', null)
, ('A', 'Two',    40, '2019-07-02', '2019-12-31')
, ('A', 'Two',    40, '2020-01-01', null)
, ('B', 'Three', 100, null,         null)
, ('C', 'One',   100, '2018-01-01', null)
, ('C', 'Four',  100, '2019-11-01', null)
;
GO

受影响的 8 行

declare @MaxEndDate date;
set @MaxEndDate = (select max(iif(date_start > date_end, date_start, isnull(date_end, date_start))) from PaymentSchedules);

;with rcte as
(
  select res_id
  , datefromparts(year(min(date_start)), month(min(date_start)), 1) as month_start
  , eomonth(coalesce(max(date_end), @MaxEndDate)) as month_end
  , 0 as lvl
  from PaymentSchedules
  group by res_id
  having min(date_start) is not null

  union all

  select res_id
  , dateadd(month, 1, month_start)
  , month_end
  , lvl + 1
  from rcte
  where dateadd(month, 1, month_start) < month_end
)
, cte_gai as
(
select c.res_id, c.month_start, c.month_end
, t.org, t.pct, t.auto_numbered
, sum(isnull(t.pct,0)) over (partition by c.res_id, c.month_start) as res_month_pct
, count(t.auto_numbered) over (partition by c.res_id, c.month_start) as cnt
from rcte c
left join PaymentSchedules t
  on t.res_id = c.res_id
 and c.month_start >= datefromparts(year(t.date_start), month(t.date_start), 1)
 and c.month_start <= coalesce(t.date_end, @MaxEndDate)
)
select *
from cte_gai
where res_month_pct <> 100
order by res_id, month_start

GO

res_id | 月开始 | 月末 | 组织 | 百分比 | 自动编号 | res_month_pct | cnt
:----- | :------------ | :--------- | :--- | :--- | ------------: | :------------ | --:
一个 | 2018-07-01 | 2019-12-31 | 空| 空|          空| 0 | 0
一个 | 2018-08-01 | 2019-12-31 | 空| 空|          空| 0 | 0
一个 | 2018-09-01 | 2019-12-31 | 空| 空|          空| 0 | 0
一个 | 2018-10-01 | 2019-12-31 | 空| 空|          空| 0 | 0
一个 | 2018-11-01 | 2019-12-31 | 空| 空|          空| 0 | 0
一个 | 2018-12-01 | 2019-12-31 | 空| 空|          空| 0 | 0
C | 2019-11-01 | 2020-01-31 | 一 | 100 | 7 | 200 | 2
C | 2019-11-01 | 2020-01-31 | 四 | 100 | 8 | 200 | 2
C | 2019-12-01 | 2020-01-31 | 一 | 100 | 7 | 200 | 2
C | 2019-12-01 | 2020-01-31 | 四 | 100 | 8 | 200 | 2
C | 2020-01-01 | 2020-01-31 | 一 | 100 | 7 | 200 | 2
C | 2020-01-01 | 2020-01-31 | 四 | 100 | 8 | 200 | 2

db<>在这里摆弄

推荐阅读