on 01-18-2016 6:00 PM
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]
Try This
SELECT tt.[state], tt.Jan, tt.Feb, tt.Mar, tt.Apr, tt.May, tt.Jun, tt.Jul, tt.Aug,
tt.Sep, tt.Oct, tt.Nov, tt.Dec, (TT.Jan+ TT.Feb+tt.Mar +tt.Jun+tt.Jul+tt.Aug+tt.Sep+tt.Oct+tt.Oct+tt.Nov+tt.Dec) AS Total
from ( 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]
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
) AS TT
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
thanks for your reply but in Grand Total column is blank sir, addition of Jan to Dec columns values are not happening.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Try this query for grand total. advice formula for grand total percentage:
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],
GrandTotal = 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]
Thanks
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
89 | |
7 | |
6 | |
3 | |
3 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.