investment.rb

# == Schema Information
#
# Table name: sysdba.INF_ACC_IA_INVESTMENT
#
#  INF_ACC_IA_INVESTMENTID  :string(12)       not null
#  INF_ACC_INVEST_ACCOUNTID :string(12)       not null
#  CREATEUSER               :string(12)
#  CREATEDATE               :datetime
#  MODIFYUSER               :string(12)
#  MODIFYDATE               :datetime
#  AUM                      :float(53)
#  INF_FUNDID               :string(12)
#  FUND                     :string(256)
#  INVESTMENT_CONTACTID     :string(12)
#  LAST_UPDATED             :datetime
#  NAV                      :float(53)
#  NAV_AS_OF                :datetime
#  STATUS                   :string(32)
#  TOTAL_CONTRIBUTIONS      :float(53)
#  TOTAL_WITHDRAWALS        :float(53)
#  ACCRUED_PERFORMANCE_FEE  :decimal(15, 2)
#  CHARGED_PERFORMANCE_FEE  :decimal(15, 2)
#  MANAGEMENT_FEE           :decimal(15, 2)
#  ORIGINAL_INVESTMENT_DATE :datetime
#  SLOTS                    :string(32)
#  USD_VALUE                :decimal(15, 2)
#  CLASS                    :string(255)
#  SERIES                   :string(255)
#  SHARES_HELD              :float(53)
#  CURRENCY_TYPE            :string(5)
#  FINAL_ESTIMATE           :string(32)
#  SPOT_RATE                :float(53)
#  AUM_USD                  :float(53)
#  NAV_USD                  :float(53)
#  TOTAL_PNL_INCEPTION      :float(53)
#  HIGH_WATER_MARK          :float(53)
#  IMPORT_ID                :string(32)
#  JPM_IMPORTID             :string(64)
#

class Investment < ActiveRecord::Base
  # attr_accessible :title, :body

  # Changing the database to our EDB db
  establish_connection "SLX_#{Rails.env}"
  # Manually setting our table
  set_table_name "sysdba.INF_ACC_IA_INVESTMENT" # Whatever you require
  # Manually set our table name
  set_primary_key :INF_ACC_IA_INVESTMENTID

  # Define out aliases to adhere to naming convention
  alias_attribute(:id, :INF_ACC_IA_INVESTMENTID)
  alias_attribute(:created_at, :CREATEDATE)
  alias_attribute(:updated_at, :MODIFYDATE)
  alias_attribute(:aum, :AUM)
  alias_attribute(:fund_id, :INF_FUNDID)
  alias_attribute(:fund, :FUND)
  alias_attribute(:nav, :NAV)
  alias_attribute(:nav_as_of, :NAV_AS_OF)
  alias_attribute(:currency_type, :CURRENCY_TYPE)

  scope :fund_am, select('SYSDBA.INF_FUND.MARKETING_CODE AS marketing_code,
                          SYSDBA.INF_ACC_IA_INVESTMENT.CURRENCY_TYPE AS currency_type,
                          SUM(SYSDBA.INF_ACC_IA_INVESTMENT.AUM) AS aum,
                          MAX(SYSDBA.INF_ACC_IA_INVESTMENT.NAV_AS_OF) AS nav_as_of')
    .joins('INNER JOIN SYSDBA.INF_FUND ON SYSDBA.INF_ACC_IA_INVESTMENT.INF_FUNDID=SYSDBA.INF_FUND.INF_FUNDID')
    .group('marketing_code', 'currency_type')
    .order('nav_as_of')
    .reverse_order
end

query.sql

SELECT B.MARKETING_CODE, A.CURRENCY_TYPE, SUM(A.AUM) AS AUM, MAX(A.NAV_AS_OF) AS NAV_AS_OF
FROM SYSDBA.INF_ACC_IA_INVESTMENT A
INNER JOIN SYSDBA.INF_FUND B ON A.INF_FUNDID=B.INF_FUNDID
GROUP BY B.MARKETING_CODE, A.CURRENCY_TYPE
ORDER BY NAV_AS_OF DESC