Hi Experts,
I have this query which I running fine and pulling correct info.
/* SELECT FROM [dbo].[OACP] T0 */
DECLARE @Year AS VARCHAR(4)
/* WHERE */
SET @Year = /* T0.YEAR */ '[%0]'
DECLARE @DateStart DATE
DECLARE @DateEnd DATE
SET @DateStart = CAST('01/01/' + @Year AS DATE)
SET @DateEnd = CAST('12/31/' + @Year AS DATE)
SELECT P.[FormatCode],P.[AcctName],
ISNULL([0],0) as 'year',
ISNULL([1],0) as [Jan],
ISNULL([2],0) as [Feb],
ISNULL([3],0) as [Mar],
ISNULL([4],0) as [Apr],
ISNULL([5],0) as [May],
ISNULL([6],0) as [Junr],
ISNULL([7],0) as [Jul],
ISNULL([8],0) as [Aug],
ISNULL([9],0) as [Sep],
ISNULL([10],0) as [Oct],
ISNULL([11],0) as [Nov],
ISNULL([12],0) as [Dec]
From (SELECT
T0.[FormatCode],
T0.[AcctName],
SUM(T1.[Credit]-T1.[Debit]) AS [BAL],
month(T1.[RefDate]) as [Month]
FROM
OACT T0
INNER JOIN JDT1 T1 ON T0.AcctCode = T1.Account
INNER JOIN OJDT T2 ON T1.TransId = T2.TransId
WHERE
( T0.[FormatCode] = '31100' or T0.[FormatCode] ='31200')
AND T1.[Debit] != T1.[Credit] and T1.Refdate >= @DateStart AND T1.Refdate <= @DateEnd
GROUP BY
T0.[FormatCode],
T1.[RefDate],
T0.[AcctName]Having SUM(T1.[Debit]-T1.[Credit]) != 0
Union ALL
SELECT
T0.[FormatCode],
T0.[AcctName],
SUM(T1.[Credit]-T1.[Debit]) AS [BAL],
0 as YEAR
FROM
OACT T0
INNER JOIN JDT1 T1 ON T0.AcctCode = T1.Account
INNER JOIN OJDT T2 ON T1.TransId = T2.TransId
WHERE
( T0.[FormatCode] = '31100' or T0.[FormatCode] ='31200' )
AND T1.[Debit] != T1.[Credit] and T1.Refdate >= @DateStart AND T1.Refdate <= @DateEnd
GROUP BY
T0.[FormatCode],
T1.[RefDate],
T0.[AcctName]Having SUM(T1.[Debit]-T1.[Credit]) != 0
)S
PIVOT (SUM(S.[BAL]) FOR [month] IN ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P
ORDER BY
P.FormatCode
it gives me output as below.

what I am trying to achieve here is to get Financial Year name with year of input of my filter. for example if i give input to select data for year 2022, it should show Financial Year 2022 in column before Jan.
I am not sure how to join my Alias name ISNULL([0],0) as 'year' + '2022'.
Any suggestion that leads me to get that would be appreciated.
thanks in advance.