一个关于对日会计年月的SQL问题

题目

有这么一张表

表中，code对应的名称被频繁修改，造成了使用的不便，使用者想要找到表中每条数据在enddate所属会计年度的最后一次修改的名称，并形成一个视图。

ps：日本一个会计年度从当年4月1号到次年的3月31号。

思路

会计年的计算

既然是和会计年相关的问题，那么首先要确定会计年。根据会计年的数据特点，判定当前月是否在四月以前，是，则属于上一个会计年，不是，则属于当前年序号对应的会计年：

如：20240301，在4月以前，则会计年是2023年，又如20240501，在4月以后，会计年则是2024年。对应的公式为：

 CASE 
	WHEN ((DATE_PART('MONTH'::"VARCHAR", STARTDATE) >= 4) 
		AND (DATE_PART('MONTH'::"VARCHAR", STARTDATE) <= 12)) 
	THEN TO_CHAR(STARTDATE, 'YYYY'::"VARCHAR") 
	ELSE TO_CHAR(ADD_MONTHS(STARTDATE, -12), 'YYYY'::"VARCHAR") 
	END AS COUNTX 

会计年还有一种更简单的算法，观察数据特点，会计年度从当年4月1号到次年的3月31号，那如果在这个日期的基础上往前推3个月，是不是刚好是当年1月1号到次年的12月31号？对应的公式如下：

to_char(ADD_MONTHS(t.SHUURYOUDATE, -3),'YYYY') AS COUNTX 

有效的日期范围

注意上面这组数据，前两条数据，每条对应一个会计年，第三条数据，虽然跨了多个会计年，但开始日期是一个会计年的第一个值，且是改组数据的最后一条。这组数据，每一条都能唯一确定一个名称。

这组数据的期待结果比较规律

id	code	name	start	end	countingYear	countingName
4	00002	name1	2019/04/01	2020/03/31	2019	name1
5	00002	name2	2020/04/01	2021/03/31	2020	name2
6	00002	name3	2021/04/01	2022/03/31	2021	name3

再来看一种情况：

这一组数据则不一样，前两条每条数据都横跨两个会计年，其中的第一个会计年，包含该会计年的最后一个日期，可以唯一确定会计年的有效名称，但是其中的第二个会计年，只包含会计年的起始时间但不包含终了时间。

这时候有两种情况：

1. 第一种：如果这个会计年的后续日期在其他数据中没有出现，那么，当前数据对应的名称就是该会计年的有效名称；
2. 第二种：如果这个会计年的后续日期在其他数据中出现了，那么，当前数据对应的名称就是无效数据，有效名称在后面的数据中。上图中就是这种情况。上一条数据第二个会计年的结束日期在第二条数据中。这组数据的期待结果就不这么规律了
   |id|code| name | start | end | countingYear | countingName
   |–|–|–|–|–|–|–|
   | 1 | 00001 | name1 | 2019/01/01 | 2019/12/31 | 2018 | name1
   | 1 | 00001 | name1 | 2019/01/01 | 2019/12/31 | 2019 | name2
   | 2 | 00001 | name2 | 2020/01/01 | 2020/12/31 | 2019| name2
   | 2 | 00001 | name2 | 2020/01/01 | 2020/12/31 | 2020| name3
   | 3| 00001 | name3 | 2021/01/01 | 9999/12/31 | 2020 | name3
   | 3| 00001 | name3 | 2021/01/01 | 9999/12/31 | 2021 | name3
   | 3| 00001 | name3 | 2021/01/01 | 9999/12/31 | 2022 | name3
   | 3| 00001 | name3 | 2021/01/01 | 9999/12/31 | … | name3
   | 3| 00001 | name3 | 2021/01/01 | 9999/12/31 | 9999 | name3

上面的第二种情况还有一个更复杂的表现，如下图：

第一条数据所在会计年的结束日期在第四条数据中出现，第一、第二、第三，这三条数据都是无效数据。期待的结果如下：

id	code	name	start	end	countingYear	countingName
20	00007	name1	2019/01/01	2019/01/20	2018	name1
20	00007	name2	2019/01/21	2019/01/31	2018	name2
20	00007	name3	2019/02/01	2019/02/20	2018	name2
20	00007	name4	2019/02/21	9999/12/31	2018	name3
20	00007	name4	2019/02/21	9999/12/31	2019	name3
20	00007	name4	2019/02/21	9999/12/31	2020	name3
20	00007	name4	2019/02/21	9999/12/31	…	name3
20	00007	name4	2019/02/21	9999/12/31	2019	name3

面对这些情况，面对这些数据，想要整理出会计年，第一种思路是表和表之间的自关联，通过开始日期和终止日期、开始日期和开始日期、终止日期和终止日期、开始日期和会计年、终止日期和会计年等各种日期的复杂判断。实际上我也确实做了这个事情，但判断逻辑太多，最终Sql代码的深度达四层。这并不是我想要的。

既然都是日期之间的比较，那么我尝试着把开始日期和终止日期合并到一个列做排序，选出同一个会计年的最大日期，这样应该能确定唯一一个有效名称。于是有了下面的代码。

方法

   select max(distinct ttt.STARTDATE) as STARTDATE , max(distinct ttt.ENDDATE) as ENDDATE ,  ttt.code, ttt.countX, ttt.countY,max(distinct ttt.name)
    from
    (
        (
            select 
                t.ID,t.CODE,t.NAME,t.STARTDATE,t.ENDDATE
                ,date_format(t.STARTDATE,'%Y%m') as startYM
                ,date_format(t.ENDDATE,'%Y%m') as endYM
                ,date_format(DATE_ADD(t.STARTDATE, INTERVAL -3 MONTH),'%Y') as countX
                ,date_format(DATE_ADD(t.STARTDATE, INTERVAL -3 MONTH),'%Y') as countY
            from sqltest t
            order by t.CODE
        )
        union all
        (
            select 
                t.ID,t.CODE,t.NAME,t.STARTDATE,t.ENDDATE
                ,date_format(t.STARTDATE,'%Y%m') as startYM
                ,date_format(t.ENDDATE,'%Y%m') as endYM
                ,date_format(DATE_ADD(t.STARTDATE, INTERVAL 9 MONTH),'%Y') as countX
                ,date_format(DATE_ADD(t.ENDDATE, INTERVAL -3 MONTH),'%Y') as countY
            from sqltest t
            order by t.CODE
        )
    ) ttt
    group by ttt.CODE,ttt.countX,ttt.countY
    order by ttt.CODE,ttt.countX,ttt.countY

代码的结果如下：

结尾

日本会计年

会计年度从当年4月1号到次年的3月31号

计算会计年的推荐方法

to_char(ADD_MONTHS(t.SHUURYOUDATE, -3),'YYYY') AS COUNTX