>
>>Is that would you want the 2nd result to look like? One row per week, plus a grand total?
>
>Absolutely, it's right.

OK, I modified the procedure one more time.

See attached image for the result set. Now, I generated one result set, with a column for the ResultSetNum (1 means summarized by day, 2 means summarized by week, 3 means grand totals). You can use this, and in the application layer you can strip out the necessary rows to build the 2nd grid (or just filter the second grid where the resultsetnum is not equal to 1)

here's the code. I simply did a 2nd UNION ALL that summarized by the Saturday Date (by using the pretty common date logic of adding X number of days, where X is 7-the day of week of the date). As Naomi stated, you could use GROUPING SETS as well. There are multiple ways to do it.

Here's the modified procedure. If you truly need multiple result sets, I'd have to modify this and can't do it right now, so someone else can take a crack if they want to - I wouldn't be able to get to it until tonight.

CREATE  PROCEDURE dbo.DynamicPivotExampleWithMultipleTotals
as
begin

 DECLARE @ActionSelectString nvarchar(4000) , @SQLPivotQuery nvarchar(4000)
 
 ;WITH ListTempCTE as  
    (SELECT 'Quotes' AS ActionType , 1 as OrderNum
			                   UNION
							      SELECT DISTINCT Problem as Actiontype, 2 as OrderNum FROM CancellationMaster )
  							 
 SELECT @ActionSelectString =  stuff (  (  select ',[' +  
           cast(ActionType as varchar(100))  + 
		    ']'                from  ListTempCTE
                      	ORDER BY OrderNum, ActionType
						  for xml path('') ), 1, 1, '')

DECLARE @SumString nvarchar(4000)
SET @SumString = REPLACE( @ActionSelectString, '[', 'SUM([')
SET @SumString = REPLACE( @SumString, ']', '])' )
 
SET @SQLPivotQuery = ';WITH PIVOTCTE as 
 (select QuoteDate as ActivityDate, Client, ''Quotes'' as ActionType,  NumQuotes as Qty  FROM dbo.QuoteMaster
UNION
 SELECT CancelDate as ActivityDate, Client, Problem as ActionType,  Qty from dbo.CancellationMaster) ,  '

 set @SQLPivotQuery = @SqlPivotQuery + 
       ' FinalPivotCTE as  (SELECT     ActivityDate, Client, ' + @ActionSelectString +
     ' FROM PIVOTCTE 
            pivot ( SUM(Qty) for ActionType in ('
            +  @ActionSelectString + ')) TEMPLIST ) ' + char(13)  + char(10)

set @SQLPivotQuery = @SQLPivotQuery + 
'select 1 as ResultSetNum, * from FinalPivotCTE UNION ALL
   SELECT 2 as ResultSetNum, DATEADD( D, 7-DATEPART(DW,ActivityDate), ActivityDate) as WeekEndDate, NULL as Client, ' + @SumString  + ' FROM FinalPIvotCTE GROUP BY DATEADD( D, 7-DATEPART(DW,ActivityDate), ActivityDate)
   union all
   SELECT 3 as ResultSetNum,  NULL as ActivityDate, NULL AS Client, ' + @SumString + ' FROM FinalPivotCTE'
 
			 
 EXEC sp_executesql @SqlPivotQuery

end