Alter PROC piGEBonusAnalysis_rpAnalysisByRankCountry 
---EXEC piGEBonusAnalysis_rpAnalysisByRankCountry '01/Jan/2009', '31/Oct/2009', ' N/A', 'USA', 'CDD', 'SDD', 'Malaysia'     


 @fdate VARCHAR(11),   
 @tdate VARCHAR(11),   
 @fcountry VARCHAR(100),   
 @tcountry VARCHAR(100),   
 @frank VARCHAR(10),   
 @trank VARCHAR(10),   
 @branch VARCHAR(100)   
AS   
SET NOCOUNT ON   
DECLARE @rank VARCHAR(50)   
DECLARE @rankcode INT   
DECLARE @shortcode VARCHAR(2)  
DECLARE @tablename NVARCHAR(9)   
DECLARE @ftablename VARCHAR(9)   
DECLARE @ttablename VARCHAR(9)   
DECLARE @qry NVARCHAR(4000)   
  
SET @qry = N'DECLARE rank_cursor CURSOR LOCAL SCROLL STATIC FOR '  
SET @qry = @qry + 'SELECT DISTINCT ConfigValue, ConfigName '  
SET @qry = @qry + 'FROM GeoConfig R '  
SET @qry = @qry + 'WHERE configsource=''Rank'' and configValue BETWEEN '''+@frank+''' AND '''+@trank+''' '   
SET @qry = @qry + 'ORDER BY configValue asc '   

--print @qry
Exec SP_EXECUTESQL @qry                               
  
OPEN rank_cursor  
  
SET @qry = N'SELECT CT.LookupValue AS Country'   
  
FETCH NEXT FROM rank_cursor INTO @rankcode, @rank   
  
WHILE @@FETCH_STATUS = 0  
BEGIN  
 SET @qry = @qry + N', ISNULL(DL.' + @rank + N', 0.0) AS ' + @rank   
  
 FETCH NEXT FROM rank_cursor INTO @rankcode, @rank   
END  
  
SET @qry = @qry + N' FROM GeoLookup CT ' 
 
SET @qry = @qry + N'LEFT OUTER JOIN (SELECT Country'   
  
FETCH FIRST FROM rank_cursor INTO @rankcode, @rank   
  
WHILE @@FETCH_STATUS = 0  
BEGIN   
 SET @qry = @qry + N', SUM(CASE WHEN BS.NRank = ' + CAST(@rankcode AS VARCHAR(5)) + ' THEN BS.TotalBonus ELSE 0.0 END) AS ' + @rank   
  
 FETCH NEXT FROM rank_cursor INTO @rankcode, @rank   
END   
  
SET @shortcode = (    
 SELECT BCC.ShortCode   
 FROM piGEBranchCalculationConfig BCC   
 INNER JOIN GeoLookup BR ON BCC.BranchCountryLookupID = BR.GeoLookupID   
 WHERE BR.LookupValue = @branch   
)  
  
SET @ftablename = 'M' + @shortcode + CAST(YEAR(@fdate) AS VARCHAR(4)) + RIGHT('0' + CAST(MONTH(@fdate) AS VARCHAR(2)), 2)   
  
SET @ttablename = 'M' + @shortcode + CAST(YEAR(@tdate) AS VARCHAR(4)) + RIGHT('0' + CAST(MONTH(@tdate) AS VARCHAR(2)), 2)   
  
DECLARE cursor_table CURSOR LOCAL SCROLL STATIC FOR   
SELECT [Name]   
FROM Sysobjects   
WHERE Type = 'U'   
AND [Name] LIKE 'M' + @shortcode + '______'   
AND [Name] BETWEEN @ftablename AND @ttablename   
ORDER BY [Name]   
  
OPEN cursor_table   
  
SET @qry = @qry + N' FROM ('   
  
FETCH NEXT FROM cursor_table INTO @tablename   
  
WHILE @@FETCH_STATUS = 0   
BEGIN   
 SET @qry = @qry + N'SELECT Country, NRank, TotalBonus = SUM(TotalBonus) FROM ' + @tablename + ' GROUP BY Country, NRank'  
  
 FETCH NEXT FROM cursor_table INTO @tablename   
  
 IF @@FETCH_STATUS = 0  
  SET @qry = @qry + N' UNION '  
END   
  
SET @qry = @qry + N') BS   
  GROUP BY BS.Country '   
SET @qry = @qry + N') DL ON DL.Country = CT.LookupValue '  

  SET @qry = @qry + N' WHERE CT.LookupName = ''COUNTRY''   
  AND CT.LookupValue BETWEEN ''' + @fcountry + ''' AND ''' + @tcountry + '''   
  ORDER BY CT.LookupValue'   
  
--print @qry  
EXEC SP_ExecuteSQL @qry   
SET NOCOUNT OFF