Problem solved...
This was the most complicated matter I ever dealt with.
The solution is ...
Forget about this ugly complicated Java code that builds SQL dynamically in multiple loops.
This is a very bad idea.
I just worked with the front end, generated reports using different combinations
and found out what data they need.
The best trick that helped me is STUFF() and dynamic columns.
USE COIN
IF OBJECT_ID(N'tempdb..#tblComm', N'U') IS NOT NULL
DROP table #tblComm
GO
IF OBJECT_ID(N'tempdb..#tblRank', N'U') IS NOT NULL
DROP table #tblRank
GO
DECLARE @excludeRRCodes varchar(8000)
DECLARE @includeRRCodes varchar(8000)
DECLARE @yearMonths varchar(8000)
SET @excludeRRCodes = 'CSMK, YGKK, YBZZ'
SET @includeRRCodes = 'CA ,CS ,DC ,YG ,YH ,YT ,YW ,11'
--SET @yearMonths = '2011-1,2011-2,2011-3,2011-4,2011-5,2011-6,2011-7,2011-8,2011-9,2011-10,2011-11,2011-12'
--SET @yearMonths = @yearMonths+'2012-1,2012-2,2012-3,2012-4,2012-5,2012-6,2012-7,2012-8,2012-9,2012-10,2012-11,2012-12'
--SET @yearMonths = @yearMonths+'2013-1,2013-2,2013-3,2013-4'
SET @yearMonths = '2012-01,2012-02,2012-03,2012-04,2012-05,2012-06,2012-07,2012-08,2012-09,2012-10,2012-11,2012-12'
-----------------------------------------------------
CREATE TABLE #tblComm
(
group_namevarchar(40)
, type_codevarchar(4)
, commfloat
, soft_dollar_ratefloat
, year_numberint
, month_numberint
, rank_total_GROSSfloat
, rank_total_NET float
)
INSERT INTO #tblComm (
group_name
, type_code
, comm
, soft_dollar_rate
, year_number
, month_number
)
SELECT
group_name,
type_code ,
ISNULL(SUM(ISNULL(comm, 0)), 0) AS comm,
soft_dollar_rate,
year_number,
month_number
FROM
coin_ni_monthly_comm_booking
WHERE
--group_name = 'Middlefield Compass'
--and
ni_flag = 0
AND
CAST(year_number AS varchar(5)) + '-' + RIGHT('00'+CAST(month_number AS varchar(5)),2) IN (SELECT value FROM Common..getValuesAsTable(@yearMonths, ','))
AND rr_code NOT IN ( SELECT value FROM Common..getValuesAsTable(@excludeRRCodes, ',') )
AND LEFT (rr_code, 2) IN (SELECT value FROM Common..getValuesAsTable(@includeRRCodes, ',') )
AND (group_name IS NOT NULL OR group_name <> '')
GROUP BY
type_code,
soft_dollar_rate,
year_number,
month_number,
group_name
ORDER BY
year_number,
month_number
--select * from #tblComm
--return
----------------------------------------------------------------
CREATE TABLE #tblRank
(
group_name varchar(40)
, rank_total_GROSS float
, rank_total_NET float
)
INSERT INTO #tblRank ( group_name
, rank_total_GROSS
, rank_total_NET
)
SELECT
group_name,
SUM(ISNULL(comm, 0)) AS rank_total_GROSS,
SUM(ISNULL(comm*soft_dollar_rate, 0)) AS rank_total_NET
FROM #tblComm
GROUP BY group_name
--------------------------------------------
UPDATE A
SET
rank_total_GROSS = B.rank_total_GROSS,
rank_total_NET = B.rank_total_NET
FROM
#tblComm A
INNER JOIN #tblRank B ON A.group_name = B.group_name
----------------------------------------------------
DECLARE @cols NVARCHAR(2000)
SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
'],[' + CAST(t2.year_number AS VARCHAR(20)) + '-' + RIGHT('00'+CAST(t2.month_number AS VARCHAR(5)),2)
FROM
#tblComm AS t2
ORDER BY '],[' + CAST(t2.year_number AS VARCHAR(20))+ '-' + RIGHT('00'+CAST(t2.month_number AS VARCHAR(5)),2)
FOR XML PATH('')
), 1, 2, '') + ']'
----------------------------------------------------
DECLARE @sumcols NVARCHAR(2000)
SELECT @sumcols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
'],0) + ISNULL([' + CAST(t2.year_number AS VARCHAR(5)) + '-' + RIGHT('00'+CAST(t2.month_number AS VARCHAR(5)), 2)
FROM
#tblComm AS t2
ORDER BY '],0) + ISNULL([' + CAST(t2.year_number AS VARCHAR(5))+ '-' + RIGHT('00'+CAST(t2.month_number AS VARCHAR(5)), 2)
FOR XML PATH('')
), 1, 5, '') + '],0)'
-----------------------------------------------------
DECLARE @query NVARCHAR(4000)
SET @query =
N'SELECT group_name,
type_code,
soft_dollar_rate * 100 AS soft_dollar_rate, '
+ @cols +
+', '
+ @sumcols + ' AS TotalForTheRow_GROSS ' +', '
+ '(' + @sumcols + ')' + '*soft_dollar_rate' + ' AS TotalForTheRow_NET '
+ ',
rank_total_GROSS,
rank_total_NET
FROM
(SELECT group_name, type_code, soft_dollar_rate, rank_total_GROSS, rank_total_NET,
CAST(t2.year_number AS VARCHAR(5)) + ''-'' + RIGHT(''00''+CAST(t2.month_number AS VARCHAR(5)),2) AS ym
, t2.comm
FROM #tblComm AS t2) p
PIVOT
(
MAX([comm])
FOR ym IN
( '+
@cols +' )
) AS pvt
ORDER BY
rank_total_GROSS,
group_name '
--PRINT @query
EXECUTE(@query)