SQL Query help using Pivot

I have a following stored procedure which is working perfectly but need the horizontal total at the end of row like for example THE FOLLOWING..it should do on every row..

[1].[2].[3]..........................开发者_运维问答......................[29].[30]..[31]...TOTAL

..please help

CREATE PROCEDURE [dbo].[VTR_Report_DaysOfMonthWise] 
@Month int,
@Year int,
@Branch_ID int
AS
BEGIN

DECLARE @startDate varchar(20)
DECLARE @endDate varchar(20)

SELECT @startDate = Convert(varchar(10),DATEADD(m, @Month - 1, DATEADD(yyyy, @Year - 1900, 0)),105)
 SELECT @endDate = Convert(varchar(10),DATEADD(d, -1, DATEADD(m, @Month, DATEADD(yyyy, @Year - 1900, 0))),105)

  SELECT *
 FROM
    (
       SELECT c.CLName, DATEPART(dd,cd.vtrRespDate) as 'Day', ISNULL(sum(cast(cd.vtrvalue as int)),0) as 'VTRValue'
FROM dbo.VTRCheckList c
LEFT OUTER JOIN VTRCheckListDetails cd ON  cd.CLid = c.CLid
AND Convert(date,cd.vtrRespDate, 105) >= convert(date,@startDate,105) and Convert(date, cd.vtrRespDate, 105) <= convert(date,@endDate,105) 
AND cd.branchid = @Branch_ID
GROUP BY c.CLName, DATEPART(dd,cd.vtrRespDate)
  ) a
  PIVOT
   (
     SUM(VTRValue) FOR Day IN
   (
       [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15],
    [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], 
    [30], [31]
)
 ) b
 END

Add a subquery to your inner query to pull the sum of the month as a separate column, something like this, the subquery col named MonthTotal. (I'm assuming vtrRespDate is a date field.)

SELECT c.CLName, 
DATEPART(dd,cd.vtrRespDate) as 'Day', 
ISNULL(sum(cast(cd.vtrvalue as int)),0) as 'VTRValue',
(SELECT ISNULL(sum(cast(x.vtrvalue as int)),0)
 FROM VTRCheckListDetails x
 WHERE YEAR(x.vtrRespDate) = YEAR(cd.vtrRespDate)
   AND MONTH(x.vtrRespDate) = MONTH(cd.vtrRespDate)
   AND Convert(date,cd.vtrRespDate, 105) >= convert(date,@startDate,105) 
   AND Convert(date, cd.vtrRespDate, 105) <= convert(date,@endDate,105)) as MonthTotal
FROM dbo.VTRCheckList c
LEFT JOIN VTRCheckListDetails cd ON cd.CLid = c.CLid
  AND Convert(date,cd.vtrRespDate, 105) >= convert(date,@startDate,105) 
  AND Convert(date, cd.vtrRespDate, 105) <= convert(date,@endDate,105) 
  AND cd.branchid = @Branch_ID
GROUP BY c.CLName, DATEPART(dd,cd.vtrRespDate)

i just tried this and worked like a charm :)..pasting here so someone else can take benefit of it..it was so easy.

SELECT 
   *
   ,[1] + [2] + [3] + [4] + [5] + [6] + [7] + [8] + [9] + [10] + [11] + [12] + [13] + [14] + [15] +
[16] + [17] + [18] + [19] + [20] + [21] + [22] + [23] + [24] + [25] + [26] + [27] + [28] + [29] + 
[30] + [31] AS Total
  FROM
(
      SELECT c.CLName, DATEPART(dd,cd.vtrRespDate) as 'Day', ISNULL(sum(cast(cd.vtrvalue as int)),0) as 'VTRValue'
FROM dbo.VTRCheckList c
LEFT OUTER JOIN VTRCheckListDetails cd ON  cd.CLid = c.CLid
AND Convert(date,cd.vtrRespDate, 105) >= convert(date,@startDate,105) and Convert(date, cd.vtrRespDate, 105) <= convert(date,@endDate,105) 
AND cd.branchid = @Branch_ID
GROUP BY c.CLName, DATEPART(dd,cd.vtrRespDate)
 ) a
PIVOT
 (
  SUM(VTRValue) FOR Day IN
   (
       [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15],
       [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], 
    [30], [31]
   )
  ) b