select top 100 m.RecordEntryNumber                                                            as External_ID,
               m.TaxCode,
               m.BankNumber + m.AccountNumber                                                 as Portfolio,
               COALESCE(b.TradeDate, b.SettlementDate, m.PostingDate)                         as Effective_Date,
               b.TradeDate,
               b.SettlementDate,
               m.PostingDate,
               cast(replace(replace(b.CommissionAmount, '+', ''), '-', '') as DECIMAL(18, 5)) as CommissionAmount,
               cast(replace(replace(b.OtherCosts, '+', ''), '-', '') as DECIMAL(18, 5))       as SEC_Fee,
               case
                 when m.UnitsHeld like '%-' then '-' + cast(cast(replace(replace(m.UnitsHeld, '+', ''), '-', '') as DECIMAL(18, 3)) as VARCHAR)
                 else '+' + cast(cast(replace(replace(m.UnitsHeld, '+', ''), '-', '') as DECIMAL(18, 3)) as VARCHAR)
               end                                                                            as Quantity,
               case
                 when m.UnitsHeld like '%-' then '-' + cast(cast(replace(replace(m.PrincipalCashAmount, '+', ''), '-', '') as DECIMAL(18, 3)) as VARCHAR)
                 else '+' + cast(cast(replace(replace(m.PrincipalCashAmount, '+', ''), '-', '') as DECIMAL(18, 3)) as VARCHAR)
               end                                                                            as Total_Amount,
               m.PrincipalCashAmount,
               m.IncomeCashAmount,
               --cast(replace(replace(m.PrincipalCashAmount, '+', ''), '-', '') as DECIMAL(18, 2)) as Total_Amount,
               'USD'                                                                          as Cash_Balance,
               m.SecurityNumber                                                               as [Security],
               'USD'                                                                          as Currency_ID,
               ''                                                                             as Code,
               ''                                                                             as Reversal,
               ''                                                                             as [Status],
               ''                                                                             as Backdated
from   MV_XIP_Transaction_MoneyDataRecord m
       left join MV_XIP_Transaction_BrokerDataRecord b
         on b.ID = m.ID
where  m.AccountNumber in (select distinct account_number
                           from   IMCWharehouse..account_detail
                           where  ( portfolio_manager = 'AWS'
                                     or administrator in ( 'SMM', 'DJG' ) )
                                  and DD1 is not null) 

quote:

---

Originally posted by Bustaz Kool

I'm not sure what you are asking for...

=======================================
A couple of months in the laboratory can save a couple of hours in the library. -Frank H. Westheimer, chemistry professor (1912-2007)

---