获取上一列并使用（Coloumn行索引）复制值，如果使用T-sql为NULL

I have developed Financial Year Data Fiscal year wise, But When a new Fiscal year is started Say April of 2015-2016 financial year is entered , remaining rows are NUll in that particular year , i dont want that, i need previous year values to be shown instead of NULL

我明智地制定了财政年度财政年度数据,但是当新的财政年度开始时说进入2015-2016财政年度4月,剩下的行在那个特定的年份是NUll,我不想要那个,我需要前一年的价值是显示而不是NULL

MY Query

CREATE TABLE [dbo].[tblact] (
    [Id]                 INT             NOT NULL,
    [years]              NVARCHAR (MAX)  NULL,
    [months]             NVARCHAR (MAX)  NULL,
    [expenses]           DECIMAL (18, 2) NULL,
    [closingbal]         DECIMAL (18, 2) NULL,
    [monthorder]         INT             NULL

My T-sql

CREATE PROCEDURE fiscalyear 
AS
DECLARE @qstr           NVARCHAR(MAX),
        @ColumnName     NVARCHAR(MAX),
        @AvgColumnName  NVARCHAR(MAX)

SELECT  @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(years),
        @AvgColumnName = COALESCE(@AvgColumnName + ',','') + 'AVG(' + QUOTENAME(years) + ')'
FROM tblact 
GROUP BY years;

SET @qstr ='
WITH CTE
AS
(
    SELECT months, ' + @ColumnName + ',total,average,monthorder  FROM 
    (SELECT months, years, expenses,avg(expenses) over(partition by months) average,sum(expenses) over (partition by months) total ,monthorder FROM tblact ) AS p 
    PIVOT(SUM(expenses) FOR  years  IN (' + @ColumnName + '))  AS PVTTable
)

SELECT months, ' + @ColumnName +',total,average,monthorder
FROM CTE
UNION ALL
SELECT ''Average'',' + @AvgColumnName + ',AVG(total),AVG(average),13
FROM CTE
ORDER BY monthorder;'

EXEC sp_executesql  @qstr  

My Output

Months | 2012-2013 | 2013 - 2014 | 2014-2015 |2015-2016 | Total | Average
-------------------------------------------------------------------------
April  | 2000      | 3000        | 4000      | 4500     | 9000  |  3000
MAY    | 2000      | 3000        | 4000      | NULL     | 9000  |  3000
------------------------------------------------------------------------
Average| 2000      | 3000        | 4000      |  4500    | 9000  |  3000

My Expected Output To get Previous Fiscal Year (i.e, 2014-2015 May Value in 2015-2016 May row if NULL)

我的预期产出要获得上一财年(即2014-2015 2015年5月的价值,如果为NULL,则为5月的行)

Months | 2012-2013 | 2013 - 2014 | 2014-2015 |2015-2016 | Total | Average
-------------------------------------------------------------------------
April  | 2000      | 3000        | 4000      | 4500     | 9000  |  3000
MAY    | 2000      | 3000        | 4000      | *4000*     | 9000  |  3000
------------------------------------------------------------------------
Average| 2000      | 3000        | 4000      | 4250    | 9000  |  3000

Thanks in Advance!

提前致谢!

1 个解决方案

#1

without the pivot

没有枢轴

select [yearThis].[years], [yearThis].[months]
     , isnull([yearThis].[expenses], [yearLast].[expenses]) 
  from [tblact] as [yearThis] 
  left join [tblact] as [yearLast] 
    on [yearLast].[years]  = [yearThis].[yearThis] - 1 
   and [yearLast].[months] = [yearThis].[months]