如何将mm/dd/yyyy记录分配给另一个表中的相关财政月份

Question

注意:这个问题适用于SQL和ORACLE，我们没有创建临时表或存储过程的权限。

数据库有两个表。

一个表有一个月结束日期字段和一个标识“财政月”标签的文本字段。

第二个表的日期按日计算(mm/dd/yyyy)，并与数值数据相关联。

我们需要检索表1中关联的“财政月”分组的第二个表数据(数字之和)。

在一个查询或使用CTE或更好的解决方案中，如何在表1上执行某种查找以检索表2中的mm/dd/yyy日期所在的财政月份。

表1(财政月结束日期)

2015-05-29 -财政月为“May2015”

2015-06-30 -财政月为“Jun2015”

2015-07-31 -财政月为“Jul2015”

表2 (mm/dd/yyyy)，需要按财政月份进行汇总和分组

2015-05-29应按“May2015”分组。

2015-06-30应按“Jun2015”分组。

Answer 1

因此，我所使用的方法是为一个特定的财政月份创建一个日期范围。由于您有每个会计月的最后一个日期，所以您可以在上一个财政月份结束时得到上一个会计月份的日期。当然，这是一个开始的硬限制，因为在财政月份表中的第一个月将不会有前一个月的日期(我使用的是1月1日)。

然后，当您使用每日数据加入它时，您可以使用这两个日期来确定该数据属于哪个财政月份。

要获得上一个财政月结束日期，我们可以使用LAG解析函数使用月份来排序行。查询的其余部分非常简单。

WITH
    fiscal_end_dates
    AS
        (SELECT TO_DATE ('2015-05-29', 'YYYY-MM-DD') AS last_date FROM DUAL
         UNION ALL
         SELECT TO_DATE ('2015-06-30', 'YYYY-MM-DD') AS last_date FROM DUAL
         UNION ALL
         SELECT TO_DATE ('2015-07-31', 'YYYY-MM-DD') AS last_date FROM DUAL),
    daily_data
    AS
        (SELECT TO_DATE ('2015-05-29', 'YYYY-MM-DD') AS data_date,
                10 AS some_value
         FROM DUAL
         UNION ALL
         SELECT TO_DATE ('2015-05-30', 'YYYY-MM-DD') AS data_date,
                14 AS some_value
         FROM DUAL
         UNION ALL
         SELECT TO_DATE ('2015-06-20', 'YYYY-MM-DD') AS data_date,
                34 AS some_value
         FROM DUAL
         UNION ALL
         SELECT TO_DATE ('2015-07-04', 'YYYY-MM-DD') AS data_date,
                34 AS some_value
         FROM DUAL),
    fiscal_date_range
    AS
        (SELECT last_date,
                NVL (
                    LAG (last_date, 1) OVER (ORDER BY EXTRACT (MONTH FROM last_date)),
                    TO_DATE (EXTRACT (YEAR FROM last_date) || '-01-01', 'YYYY-MM-DD')
                )
                    AS prev_month_fiscal_end_date,
                INITCAP (TO_CHAR (last_date, 'MON')) || EXTRACT (YEAR FROM last_date)
                    AS fiscal_month
         FROM fiscal_end_dates)
SELECT dd.*,
       fdr.fiscal_month
FROM daily_data dd,
     fiscal_date_range fdr
WHERE dd.data_date > fdr.prev_month_fiscal_end_date
      AND dd.data_date <= fdr.last_date;

这就是结果(我冒昧地在日常数据表中添加了几行，以显示查询是否正常工作)

DATA_DATE   SOME_VALUE  FISCAL_MONTH
5/29/2015   10          May2015
5/30/2015   14          Jun2015
6/20/2015   34          Jun2015
7/4/2015    34          Jul2015

现在所需要做的就是使用结果集并执行分组和聚合。