Skip to content
Henry's Web Development Blog
0
  • Home
  • About me
    • My Resume
  • Sites I have worked on
  • Tutorial / Class Notes
    • Web Design Basic
    • CLBC Photoshop Class
  • Shop
    • My Account
    • Cart
    • Checkout

Stored Procedure for InDesign Database Plugin

January 10, 2012

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.

Recent Posts

  • Ministry of Government and Consumer Services | Ontario.ca
  • Working with Marvellous Homes
  • Working in Intel Corp for their Project Management System
  • Working with Kimberley System
  • Work at Bell Media ….

Archives

  • May 2019
  • July 2018
  • January 2018
  • July 2017
  • July 2016
  • May 2015
  • April 2014
  • October 2013
  • February 2013
  • November 2012
  • April 2012
  • March 2012
  • February 2012
  • January 2012
  • October 2011

Categories

  • Development
  • Uncategorized
  • Web Design

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

Copyright Henry's Web Development Blog 2021 | Theme by ThemeinProgress | Proudly powered by WordPress