Skip to Content
0
Jan 18, 2016 at 06:00 PM

Unable to give additional column

18 Views

Hi everyone,

can anyone give 2 additional coumns for this query like Grand total and Percentage of Grand total

SELECT p.state,

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],

p.[1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12]

FROM

(SELECT T2.state, sum(T1.[Doctotal]) AS [BAL],MONTH(T1.Docdate) as [month]

FROM

[dbo].[OCRD] T0 INNER JOIN OINV T1 ON T0.[CardCode] = T1.[CardCode]

INNER JOIN CRD1 T2 ON T0.[CardCode] = T2.[CardCode]

Where T0.[CardType] ='C' AND Year(T1.Docdate) = 2012

Group by

T1.DocDate,

t2.State)S

PIVOT

(SUM(S.[BAL]) FOR [month] IN

([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

Order By

P.State,

p.[1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12]