Fiscal year change

[former_member252592]

Hi Experts

Im using this query to get data of Cost & Over heads of Fiscal year 2014-2015

SELECT

[Acctcode] as Acct#, [Acctname] as AcctName, [ProfitCode] as 'CC Code', [OcrName] as 'CC Name', [Number] as 'Trans ID.',

ISNULL([7],0) as July, ISNULL([8],0) as Aug,

ISNULL([9],0) as Sept, ISNULL([10],0) as Oct,

ISNULL( [11],0) as Nov, ISNULL([12],0) as Dec,

ISNULL([1],0) as Jan, ISNULL([2],0) as Feb,

ISNULL([3],0) as Mar, ISNULL([4],0) as Aprl,

ISNULL([5],0) as May, ISNULL([6],0) as June

from

(SELECT T0.[FormatCode] as Acctcode, T0.[AcctName] as AcctName , T1.[ProfitCode] , T3.[OcrName], T2.[Number],

(sum (T1.[Debit]-T1.[Credit])) as Bal,month(T2.[RefDate]) as month

FROM [dbo].[OACT]  T0

left outer join JDT1 T1 ON T0.AcctCode = T1.Account

INNER JOIN OJDT T2 ON T1.TransId = T2.TransId

left outer join OOCR T3 ON T1.ProfitCode = T3.OcrCode

WHERE

T0.groupmask = '5'

group by

T0.[FormatCode],T0.[AcctName],T2.[RefDate],T1.[Debit],T1.[Credit],T1.ProfitCode,T3.OcrName,T2.[Number])s

Pivot

(sum(Bal) FOR Month IN ([7],[8],[9],[10],[11],[12],[1],[2],[3],[4],[5],[6]))p

But i also need the data for Fiscal year 2015-2016

can anyone guide me

Regards,

Jamil

[KennedyT21 · Accepted solution]

Try this Year field added

SELECT

[Acctcode] as Acct#, [Acctname] as AcctName, [ProfitCode] as 'CC Code', [OcrName] as 'CC Name', [Number] as 'Trans ID.',year,

ISNULL([7],0) as July, ISNULL([8],0) as Aug,

ISNULL([9],0) as Sept, ISNULL([10],0) as Oct,

ISNULL( [11],0) as Nov, ISNULL([12],0) as Dec,

ISNULL([1],0) as Jan, ISNULL([2],0) as Feb,

ISNULL([3],0) as Mar, ISNULL([4],0) as Aprl,

ISNULL([5],0) as May, ISNULL([6],0) as June

from

(SELECT T0.[FormatCode] as Acctcode, T0.[AcctName] as AcctName , T1.[ProfitCode] , T3.[OcrName], T2.[Number],

(sum (T1.[Debit]-T1.[Credit])) as Bal,month(T2.[RefDate]) as month, year (T2.[RefDate]) as year

FROM [dbo].[OACT]  T0

left outer join JDT1 T1 ON T0.AcctCode = T1.Account

INNER JOIN OJDT T2 ON T1.TransId = T2.TransId

left outer join OOCR T3 ON T1.ProfitCode = T3.OcrCode

WHERE

T0.groupmask = '5'

group by

T0.[FormatCode],T0.[AcctName],T2.[RefDate],T1.[Debit],T1.[Credit],T1.ProfitCode,T3.OcrName,T2.[Number])s

[KennedyT21]

Close the Thread with the correct and helpful answers

Regards

Kennedy