Hi everyone,
I have this query running fine but what i am trying to achieve here is year as my filter instead of hardcoded year like this Year(T1.Docdate) = 2023.
how can I do make this query work in a way that user gets an option to insert a year of his/her choice and get the result of that year.?
SELECT P.[CardCode],P.[CardName],
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 [Jun],
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.CARDCODE, T0.CARDNAME, ((((T1.[DocTotal]+T1.[DiscSum])-T1.[VatSum]-T1.[TotalExpns]-T1.[DiscSum])+T1.[DiscSum]) - T1.[DiscSum]) AS [BAL],MONTH(T1.Docdate) as [month] FROM dbo.OCRD T0
LEFT JOIN dbo.OINV T1 ON T1.CardCode = T0.CardCode AND Year(T1.Docdate) = 2023 where T0.[CardType] = 'c' and T1.[CANCELED] = 'N'
UNION all
SELECT T0.CARDCODE, T0.CARDNAME, -((((T1.[DocTotal]+T1.[DiscSum])-T1.[VatSum]-T1.[TotalExpns]-T1.[DiscSum])+T1.[DiscSum]) - T1.[DiscSum]) AS [BAL],MONTH(T1.Docdate) as [month] FROM dbo.OCRD T0
LEFT JOIN dbo.ORIN T1 ON T1.CardCode = T0.CardCode AND Year(T1.Docdate) = 2023 where T0.[CardType] = 'c' and T1.[CANCELED] = 'N' ) S
PIVOT (SUM(S.[BAL]) FOR [month] IN
([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P
Order By P.CardCode