Successfully got multi-column store procedure work out with group-by. Use me around a week to figure it out and work on. Well, Doing a InDesign with a database plug-in to call store procedure is much complicated than I thought before. Working with price list like a catalogs and flyer is much simpler with InDesign, but Annual Report with multi-column column in different time is a bit more complicated (isn’t it suppose to be a DBA or a database programmer’s job instead of a web developer?? Anyway, I do everything even graphic design).
Anyway it is done and the most important part is that you got to join the inner table with the outer table for a different year’s column. The code is here:
—- Setting the periods for all 5 dates for Annual Report with a fixed date (you can use system date for @date if you want)
SELECT @date = DATEADD(year, 0, @date)
SELECT @date2 = DATEADD(year, -1, @date)
SELECT @date3 = DATEADD(year, -2, @date)
SELECT @date4 = DATEADD(year, -3, @date)
SELECT @date5 = DATEADD(year, -4, @date)
— make a more temp table with customized year with five column such as 2011, 2010, 2009, 2008 and 2007.
INSERT INTO ##tempARFiveColTb
select distinct Combined_Item_ID,
Unique_Fund_Code,
Fund_WKS_Item_Date,
current_value as Current_Value,
(select distinct current_value from ##tempARValueTable
where Fund_WKS_Item_Date like ‘%’ + @date2 + ‘%’ and o.Combined_Item_ID = Combined_Item_ID) as prev_Last_year_end_dec31_value,
(select distinct current_value from ##tempARValueTable
where Fund_WKS_Item_Date like ‘%’ + @date3 + ‘%’ and o.Combined_Item_ID = Combined_Item_ID) as prev_2nd_year_dec31_value,
(select distinct current_value from ##tempARValueTable
where Fund_WKS_Item_Date like ‘%’ + @date4 + ‘%’ and o.Combined_Item_ID = Combined_Item_ID) as prev_3nd_year_dec31_value,
(select distinct current_value from ##tempARValueTable
where Fund_WKS_Item_Date like ‘%’ + @date5 + ‘%’ and o.Combined_Item_ID = Combined_Item_ID) as prev_4th_year_dec31_value
from ##tempARValueTable o
GROUP BY Combined_Item_ID
— list the table
select distinct ##tempARFiveColTb.Combined_Item_ID, ##tempARFiveColTb.Unique_Fund_Code, ##tempARFiveColTb.Fund_WKS_Item_Date, ##tempARFiveColTb.current_year_sept30_value, ##tempARFiveColTb.current_year_mar31_value, ##tempARFiveColTb.previous_1_year_mar31_value, ##tempARFiveColTb.previous_2_year_mar31_value, ##tempARFiveColTb.previous_3_year_mar31_value from ##tempARFiveColTb
where Fund_WKS_Item_Date like ‘%’ + @date +’%’
order by Combined_Item_ID asc, Unique_Fund_Code asc, Fund_WKS_Item_Date desc
BTW, I am using temp table in sybase. If you find this code useful, use it and have fun.
