0

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

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)

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!

Dhaval
  • 2,801
  • 20
  • 39
Prhem
  • 117
  • 1
  • 2
  • 7
  • this is possible duplcate of this question http://stackoverflow.com/questions/29894263/average-column-wise-by-using-t-sql-and-pivot?noredirect=1#comment47912485_29894263 – mohan111 Apr 28 '15 at 07:48
  • Can you provide some sample data or an SQLFiddle to test this with? I'm pretty confident I can get the result you want, but I don't really have time to sift through the outputs to create some working sample data. – John Bell Apr 28 '15 at 09:58

1 Answers1

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]
paparazzo
  • 44,497
  • 23
  • 105
  • 176