Skip to Content
0
Jan 21, 2023 at 09:55 PM

join alias name with year of input in filter

83 Views Last edit Jan 22, 2023 at 09:40 PM 2 rev

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.

image.png

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.

Attachments

image.png (9.9 kB)