Skip to Content
0
Jan 18, 2023 at 06:19 PM

Filter year by user

67 Views

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