如何在DB2中找到本财政年度的季末、半年和年度的最后一天？

Question

我想要在DB2中获得本财政年度的季度、半年和年度的最后一天。我们的财政年度从4月1日开始，到3月31日结束。因此，如果今天的日期是2016年10月2日，那么季度结束日期将是2016年12月31日，半年日期将是2017年3月31日，年末日期将是2017年3月31日。同样，如果输入日期为2016年5月7日，则季度结束日期为2016年6月30日，半年日期为2016年9月30日，年末日期为2017年3月31日。

我可以使用DB2中的last_day函数来获取一个月的最后一个日期，但是对于其他3个日期，我无法执行相同的操作。我将非常感谢在这方面的任何帮助。

Answer 1

计算一个季度内的月份数(即1/2/3)，然后为季度的第一个月添加2个月，为第二季度添加1个月(MOD(2-(MONTH(CURRENT_DATE)-1),3) = 0/1/2)。现在您的月份始终是3/6/9/12，您可以应用last_day。

我没有DB2系统来测试它，但是它的语法似乎是这样的：

LAST_DAY(CURRENT_DATE + (MOD(2-(CURRENT_DATE-1),3) MONTH))

Answer 2

你可以对日期做一些非常灵活的事情，但是在DB2 (据我所知)中没有特定的东西能够找到相关财务季度的日期，所以可能需要一些关于当前月份的case语句。

SELECT MONTH(CURRENT DATE), -- returns 10

       CURRENT DATE - (DAY(CURRENT DATE) - 1) DAYS, --return first day of current month

       CASE WHEN 
               MOD(MONTH(CURRENT DATE - (DAY(CURRENT DATE) - 1) DAYS), 3) = 0
               THEN 3
          ELSE MOD(MONTH(CURRENT DATE - (DAY(CURRENT DATE) - 1) DAYS), 3)
       END, --months into the quarter, so 1 for first month, 2 for second month, 3 for last month


       CURRENT DATE - (DAY(CURRENT DATE) - 1) DAYS +
       (4 - CASE WHEN 
                  MOD(MONTH(CURRENT DATE - (DAY(CURRENT DATE) - 1) DAYS), 3) = 0
                   THEN 3
                 ELSE MOD(MONTH(CURRENT DATE - (DAY(CURRENT DATE) - 1) DAYS), 3)
       END) MONTHS, --returns first day of next quarter


       CURRENT DATE - (DAY(CURRENT DATE) - 1) DAYS +
                      (4 - CASE WHEN 
                                    MOD(MONTH(CURRENT DATE - (DAY(CURRENT DATE) - 1) DAYS), 3) = 0
                                      THEN 3
                                ELSE MOD(MONTH(CURRENT DATE - (DAY(CURRENT DATE) - 1) DAYS), 3)
                       END) MONTHS
       - 1 DAY -- returns last day of current quarter
FROM SYSIBM.SYSDUMMY1

然后，您可以使用当前月份的一些case语句来查找半年和明年的类似信息。

我知道这看起来有点乱七八糟，但它可以做的工作。我建议使用这段代码创建一个标量函数，您可以很容易地重用该函数来查找季度，并且在半年和全年都有类似的内容。

资料来源:每天在DB2担任商业智能分析师

Answer 3

这里给出的查询和例程的验证是在IBMi7.3又名v7r3的DB2上完成的

没有关于如何使用数据的指示，以便知道需要或可以以什么形式提供数据，但是以下用户定义的表函数(UDTF)定义了一些常量，这些常量表示从可选日期的传入月份作为输入的映射/关联；如果没有提供参数，则默认为当前日期，到季度末和半年末的月份，以及到季度；该映射允许一些相当简单的日期算法给出所需的值，而无需创建实际的持久日历表：

create function OUR_FINANCIAL_YEAR_DATES                       
( input_date date default current date )                       
returns table                                                  
( "Last_Day_Of_Quarter"   /* for LD_QTR  */ date               
, "Last_Day_Of_Half_Year" /* for LD_HALF */ date               
, "Last_Day_Of_Year"      /* for LD_YEAR */ date               
) language sql                                                 
return                                                         
 with                                                          
   moMap ( mo, hAddMo, qAddMo, q ) as ( values                 
      ( 12, 3 , 0 , 3 ), ( 11, 4 , 1 , 3 ), ( 10, 5 , 2 , 3 )  
    , (  9, 0 , 0 , 2 ), (  8, 1 , 1 , 2 ), (  7, 2 , 2 , 2 )  
    , (  6, 3 , 0 , 1 ), (  5, 4 , 1 , 1 ), (  4, 5 , 2 , 1 )  
    , (  3, 0 , 0 , 4 ), (  2, 1 , 1 , 4 ), (  1, 2 , 2 , 4 ) )
 , endFYdates ( end_Qtr, end_Hyr, end_Fyr ) as                 
   ( select                                                    
       last_day( input_date   +  qAddMo MONTHS )     as Qtr_end
     , last_day( input_date   +  hAddMo MONTHS )     as Hyr_end
     , last_day( input_date   + (qAddMo + ( 3 * ( 4 - q ) )    
                                )       MONTHS )     as Fyr_end
     from moMap as M                                           
     where mo = month( input_date   )                          
   )                                                           
 select end_Qtr, end_Hyr, end_Fyr                              
 from endFYdates                                               

当然，标量用户定义函数(UDF)可以很容易地使用上面的UDTF来实现获取这些值中的任何一个；也许可以使用第二个参数来标识要返回哪些日期值。

with                                                                
  variousDates ( dv ) as ( values                                   
  ( date'2016-05-01' ), ( date'2016-05-02' ), ( date'2016-06-30' )  
, ( date'2016-07-02' ), ( date'2016-08-02' ), ( date'2016-09-30' )  
, ( date'2016-10-01' ), ( date'2016-11-02' ), ( date'2016-12-30' )  
, ( date'2017-01-02' ), ( date'2017-03-02' ), ( date'2017-03-30' ) )
select vdv.*, fyv.*                                                 
     , dec( quarter( vdv.dv - 3 months ) , 1 ) as qtr               
from variousDates as vdv                                            
, lateral                                                           
  ( select s.* from table(OUR_FINANCIAL_YEAR_DATES(vdv.dv)) as s    
  ) as fyv                                                          
order by 1                                                          
; -- a likeness of a report from the above query:
DV          LAST_00001  LAST_00002  LAST_00003  QTR
2016-05-01  2016-06-30  2016-09-30  2017-03-31   1 
2016-05-02  2016-06-30  2016-09-30  2017-03-31   1 
2016-06-30  2016-06-30  2016-09-30  2017-03-31   1 
2016-07-02  2016-09-30  2016-09-30  2017-03-31   2 
2016-08-02  2016-09-30  2016-09-30  2017-03-31   2 
2016-09-30  2016-09-30  2016-09-30  2017-03-31   2 
2016-10-01  2016-12-31  2017-03-31  2017-03-31   3 
2016-11-02  2016-12-31  2017-03-31  2017-03-31   3 
2016-12-30  2016-12-31  2017-03-31  2017-03-31   3 
2017-01-02  2017-03-31  2017-03-31  2017-03-31   4 
2017-03-02  2017-03-31  2017-03-31  2017-03-31   4 
2017-03-30  2017-03-31  2017-03-31  2017-03-31   4 
********  End of data  ********                    

以下UDTF计算给定日期值的季度末作为CURRENT_DATE的输入(如果没有传递任何参数)，但另外计算并提供该计算日期起每个季度的季度初值和季末值，以及提供给定日期值的半年末、年末和季度的计算结果：

create function THE_FINANCIAL_YEAR_DATES          
( input_date date default current date )          
returns table                                     
( LastOfQtr               /* for LD_QTR  */ date  
, LastOfHalf              /* for LD_HALF */ date  
, LastOfYear              /* for LD_YEAR */ date  
, NumberQtr               /* for NO_QTR  */ dec(1)
, FirstOfQ1               /* for FD_Q1   */ date  
, Last_OfQ1               /* for LD_Q1   */ date  
, FirstOfQ2               /* for FD_Q2   */ date  
, Last_OfQ2               /* for LD_Q2   */ date  
, FirstOfQ3               /* for FD_Q3   */ date  
, Last_OfQ3               /* for LD_Q3   */ date  
, FirstOfQ4               /* for FD_Q4   */ date  
, Last_OfQ4               /* for LD_Q4   */ date  
) language sql                                    
return                                                              
 with                                                               
   endCurQtr  ( ecq , q ) as                                        
     ( values                                                       
        ( LAST_DAY(   input_date                                    
                    + ( MOD( 1 - MONTH( input_date )                
                           , 3 ) + 2                                
                      ) MONTHS                                      
                  )                                                 
        , DEC( QUARTER( input_date - 3 MONTHS ), 1 )                
        )                                                           
     )                                                              
, curFYdates (q, ecq, sQ1, eQ1, sQ2, eQ2, sQ3, eQ3, sQ4, eQ4, eHY) as
  ( select q, ecq,                                                  
      ( ecq + 1 day )   - (   3 * ( q     )  ) months               
    , last_day( ( ecq ) - (   3 * ( q - 1 )  ) months )             
    , ( ecq + 1 day )   - (   3 * ( q - 1 )  ) months               
    , last_day( ( ecq ) - (   3 * ( q - 2 )  ) months )        
    , ( ecq + 1 day )   - (   3 * ( q - 2 )  ) months          
    , last_day( ( ecq ) - (   3 * ( q - 3 )  ) months )        
    , ( ecq + 1 day )   - (   3 * ( q - 3 )  ) months          
    , last_day( ( ecq ) - (   3 * ( q - 4 )  ) months )        
    , last_day( ( ecq ) - (   3 * ( q -                        
           case when q>2 then 4 else 2 end)  ) months )        
    from endCurQtr                                             
  )                                                            
select ecq, eHY, eQ4, q, sQ1, eQ1, sQ2, eQ2, sQ3, eQ3, sQ4, eQ4
from curFYdates