Query for Month Wise Total
SELECT
[1] as [Jan],[2] as [Feb],[3] as [Mar],
[4] as [Apr],[5] as [May],[6] as [Jun],
[7] as [Jul],[8] as [Aug],[9] as [Sep],
[10] as [Oct],[11] as [Nov],[12] as [Dec]
From (SELECT t0.docentry , month(t0.docdate) as 'Month'
FROM ordr T0 WHERE Year(T0.DocDate)='2012'
) S
Pivot (count(S.docentry) FOR [Month] IN
([4],[5],[6],[7],[8],[9],[10],[11],[12],[1],[2],[3])) P
SELECT
[4] as
[Apr],[5] as
[May],[6] as
[Jun],
[7] as
[Jul],[8] as
[Aug],[9] as
[Sep],
[10] as [Oct],[11] as [Nov],[12] as [Dec],[1] as [Jan],[2] as [Feb],[3] as [Mar]
From (SELECT T0.AC_Bank_Unit,t0.Amount , month(t0.RTGS_Date) as 'Month' FROM
[RTGS__Report_view] T0 WHERE Year(T0.RTGS_Date)='2014' and
AC_Bank_Unit ='canara
bank'
) S
Pivot (sum(S.Amount) FOR [Month] IN ([4],[5],[6],[7],[8],[9],[10],[11],[12],[1],[2],[3])) P
o/p:
Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar
NULL 115000.00 520130.00 NULL NULL NULL NULL NULL NULL NULL NULL NULL
Row wise Total using SQL
SELECT AHName =COALESCE([AHName],'Total'),
Apr=SUM(Apr),May=SUM(May),Jun=SUM(Jun),
Jul=SUM(Jul),Aug=SUM(Aug),Sep=SUM(Sep),
Oct=SUM(Oct), Nov=SUM(Nov),Dec=SUM(Dec),[Jan]=SUM(Jan),Feb=SUM(Feb),Mar=SUM(Mar)
FROM View_Z GROUP BY [AHName] WITH ROLLUP
AHName Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar
Total NULL 4683656.00 520130.00 NULL NULL NULL NULL NULL NULL NULL NULL NULL
No comments:
Post a Comment