Pivot with GrandTotal in rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Pivot with GrandTotal in rows

Author

Topic

sindhu sarah
Starting Member

34 Posts

Posted - 2011-11-17 : 03:39:34

I need a find a grand total for the following pivot query

declare @attendance as varchar(5)
set @attendance = 'O'

SELECT @attendance,dates,
ISNULL([01],0) as [Jan],ISNULL([02],0) as [Feb],
ISNULL([03],0) as [Mar],ISNULL([04],0) as [Apr],
ISNULL([05],0) as [May],
ISNULL([06],0) as [Jun],
ISNULL([07],0) as [Jul],ISNULL([08],0) as [Aug],
ISNULL([09],0) as [Sep],ISNULL([10],0) as [Oct],
ISNULL([11],0) as [Nov],ISNULL([12],0)as [Dec] ,
ISNULL([01],0)+ISNULL([02],0)+ISNULL([03],0)+ISNULL([04],0)+
ISNULL([05],0)+ISNULL([06],0)+ISNULL([07],0)+ISNULL([08],0)+ISNULL([09],0)+
ISNULL([10],0)+ ISNULL([11],0)+
ISNULL([12],0) AS Total

FROM
(SELECT distinct right(convert(varchar(8),dates,112),2)as dates,
(WorkHrs+OT+Wkend) as TC,
right(Convert(varchar(8),Period,112),2) as CH
FROM Timesheets WHERE

entity = 'cae' and
Left(Period,4) = '2011' and eid='E003223' and attendance=@attendance) src
PIVOT (sum(TC) FOR CH
IN ([01],[02],[03],[04],[05],[06],
[07],[08],[09],[10],[11],[12]
)) AS Pvt order by dates

The above query returns with the column total.but i need here the row grand total for 31 days.
Sample shown below:
(No column name) dates Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total
O 01 0.00 0.00 0.00 0.00 0.00 9.00 0.00 7.00 9.00 0.00 9.00 0.00 34.00
O 02 0.00 0.00 0.00 0.00 0.00 9.00 0.00 7.00 0.00 9.00 9.00 0.00 34.00
O 03 0.00 0.00 0.00 0.00 0.00 0.00 9.00 7.00 0.00 9.00 9.00 0.00 34.00
O 04 0.00 0.00 0.00 0.00 0.00 0.00 9.00 7.00 9.00 9.00 0.00 0.00 34.00
O 05 0.00 0.00 0.00 0.00 0.00 9.00 9.00 0.00 9.00 9.00 0.00 0.00 36.00
O 06 0.00 0.00 0.00 0.00 0.00 9.00 9.00 0.00 9.00 9.00 0.00 0.00 36.00

But the above need to be return as
(No column name) dates Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total
O 1 0 0 0 0 0 9 0 7 9 0 9 0 34
O 2 0 0 0 0 0 9 0 7 0 9 9 0 34
O 3 0 0 0 0 0 0 9 7 0 9 9 0 34
O 4 0 0 0 0 0 0 9 7 9 9 0 0 34
O 5 0 0 0 0 0 9 9 0 9 9 0 0 36
O 6 0 0 0 0 0 9 9 0 9 9 0 0 36
Total 0 0 0 0 0 36 36 28 36 36 27 0
P 1 0 0 0 0 0 9 0 7 9 0 9 0 34
P 2 0 0 0 0 0 9 0 7 0 9 9 0 34
P 3 0 0 0 0 0 0 9 7 0 9 9 0 34
P 4 0 0 0 0 0 0 9 7 9 9 0 0 34
P 5 0 0 0 0 0 9 9 0 9 9 0 0 36
P 6 0 0 0 0 0 9 9 0 9 9 0 0 36
Total 0 0 0 0 0 36 36 28 36 36 27 0
Grand Total 0 0 0 0 72 72 56 72 72 54 0

Please let me know for the solution Thamks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-17 : 04:39:31

is that weekly totals you show in between?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sindhu sarah
Starting Member

34 Posts

Posted - 2011-11-17 : 06:16:30

Thanks for your kind reply.The 'O' and 'P' displays the attendance type..So it should display the attendance O total in between and P total inbetween and as much as attendance type increases it should display the sub total and grand total as well.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-17 : 11:04:09

quote:
Originally posted by sindhu sarah

Thanks for your kind reply.The 'O' and 'P' displays the attendance type..So it should display the attendance O total in between and P total inbetween and as much as attendance type increases it should display the sub total and grand total as well.

which is your front end? is it some reporting tool?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sindhu sarah
Starting Member

34 Posts

Posted - 2011-11-20 : 07:17:35

no its .net application.but we are using the same in vba.so it would be better if we get it in sql server.

sindhu sarah
Starting Member

34 Posts

Posted - 2011-11-20 : 07:50:32

Hi Visakh.Thanks for your reply.I gave a work around solution for it.This might be helpful for others.
SELECT attendance, dates,
ISNULL([01],0) as [Jan],ISNULL([02],0) as [Feb],
ISNULL([03],0) as [Mar],ISNULL([04],0) as [Apr],
ISNULL([05],0) as [May],
ISNULL([06],0) as [Jun],
ISNULL([07],0) as [Jul],ISNULL([08],0) as [Aug],
ISNULL([09],0) as [Sep],ISNULL([10],0) as [Oct],
ISNULL([11],0) as [Nov],ISNULL([12],0)as [Dec] ,
ISNULL([01],0)+ISNULL([02],0)+ISNULL([03],0)+ISNULL([04],0)+
ISNULL([05],0)+ISNULL([06],0)+ISNULL([07],0)+ISNULL([08],0)+ISNULL([09],0)+
ISNULL([10],0)+ ISNULL([11],0)+
ISNULL([12],0) AS Total
FROM
--Right('000.00' + Cast((-Day(AxEmpl.EndDT)) as varchar(7)),6)

(SELECT distinct attendance, Right('00' + Cast(Day(dates) as varchar(3)),2) as Dates,
Attendance as TC,
right(Convert(varchar(8),Period,112),2) as CH
FROM Timesheets WHERE entity = @Entity and
Left(Period,4) = @Year and eid=@EID and attendance <> '' ) src
PIVOT (count(TC) FOR CH
IN ([01],[02],[03],[04],[05],[06],
[07],[08],[09],[10],[11],[12]
)) AS Pvt
union all

SELECT attendance, 'Total' as dates,
ISNULL([01],0) as [Jan],ISNULL([02],0) as [Feb],
ISNULL([03],0) as [Mar],ISNULL([04],0) as [Apr],
ISNULL([05],0) as [May],ISNULL([06],0) as [Jun],
ISNULL([07],0) as [Jul],ISNULL([08],0) as [Aug],
ISNULL([09],0) as [Sep],ISNULL([10],0) as [Oct],
ISNULL([11],0) as [Nov],ISNULL([12],0)as [Dec] ,
ISNULL([01],0)+ISNULL([02],0)+ISNULL([03],0)+ISNULL([04],0)+
ISNULL([05],0)+ISNULL([06],0)+ISNULL([07],0)+ISNULL([08],0)+
ISNULL([09],0)+ISNULL([10],0)+ ISNULL([11],0)+
ISNULL([12],0) AS Total
FROM
(SELECT Attendance,Attendance as TC, 'Total' as Dates,
right(Convert(varchar(8),Period,112),2) as CH
FROM Timesheets WHERE
Entity = @Entity and
Left(Period,4) = @Year and EID=@EID and attendance <> '') src
PIVOT (count(TC) FOR CH
IN ([01],[02],[03],[04],[05],[06],
[07],[08],[09],[10],[11],[12]
)) AS Pvt Order by Attendance, Dates
END

The above query gives the O/P as
H 06 0 0 0 0 0 0 0 0 0 0 1 0 1
H 07 0 0 0 0 0 0 0 0 0 0 1 0 1
H Total 0 0 0 0 0 0 0 0 0 0 2 0 2
L 07 0 0 0 0 0 0 0 1 0 0 0 0 1
L 08 0 0 0 0 0 0 0 1 0 0 0 0 1
L 09 0 0 0 0 0 0 0 1 0 0 0 0 1
L 10 0 0 0 0 0 0 0 1 0 0 0 0 1
L 11 0 0 0 0 0 0 0 1 0 0 0 0 1
L Total 0 0 0 0 0 0 0 5 0 0 0 0 5

I have taken 2 pivots..one for normal records and other query for subtotal of records.and made two pivots combine with union all.
Hope this helps..Thanks for your support.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources