计算累进值

 

 运用辅助日历表，实现计算资本开支累进值类似的功能

 

辅助日历表名称为dbo.DatePicker ，例子数据如下图：

 

 

 

数据库待处理表名称为dbo.PL_PM_CapitalExpenditure ，数据如下图

 

 

 

处理后的结果图片如下

 

 

 

 

 

具体的sql语句如下

 

WITH    DA
          AS ( SELECT   YEAR(a.DATEVALUE) YEARd ,
                        MONTH(A.DATEVALUE) month ,
                        *
               FROM     dbo.DatePicker A
                        LEFT JOIN ( SELECT  ProjectID ,
                                            Year ,
                                            monthd ,
                                            value
                                    FROM    dbo.PL_PM_CapitalExpenditure
                                            UNPIVOT
                           ( value FOR monthd IN ( [1], [2], [3], [4], [5],
                                                   [6], [7], [8], [9], [10],
                                                   [11], [12] ) ) AS p
                                    WHERE   ProjectID = 3
                                  ) B ON A.DATEVALUE > CAST(CAST(b.Year AS CHAR(4))
                                         + '/' + CAST(b.monthd AS CHAR(2))
                                         + '/1' AS DATETIME)
               WHERE    a.DateValue > '2008-12-31'
                        AND a.DateValue < '2011-1-1'
             )
    SELECT  ProjectID ,
            YEARd ,
            month ,
            MAX(value) value
    FROM    DA
    GROUP BY ProjectID ,
            YEARd ,
            month
    ORDER BY ProjectID ,
            YEARd ,
            month