CODEWITHSUNDEEP
sql-servert-sql
Prhem
Prhem

Reputation: 117

Get Previous Column and Duplicate with (Coloumn Row Index) Value if NULL using T-sql

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!

Upvotes: 0

Views: 105

Answers (1)

paparazzo
paparazzo

Reputation: 45096

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]

Upvotes: 1

Related Questions