select h.something5,h.RecordType,td.dump_nbr,h.something,h.something4,h.Type,h.CheckDigit,h.ADPNumber,h.TransactionType,h.TransHistSeq,h.TransactionDate,h.CurrencyCode,td.prc_sec_amt price,td.blttr_mrkt_cd+td.blttr_cpcty_cd BlotterCode
 ,case when td.desc_trans_sec_txt='ZZZZZZZZZ' then 'No ShortName' else td.desc_trans_sec_txt end ShortName,'orbit',getdate(),h.ParentID  from      
   orbit..CS_Transactions h,
    (
    select trd.something2,trd.something_cd,trd.chck_brch_acct_nbr,trd.dump_nbr,trd.currency_cd,trd.debit_credit_cd,trd.trans_hist_seq_nbr,
trd.security_adp_nbr,trd.trans_acct_hist_cd,trd.transaction_dt,trd.type_something2,trd.prc_sec_amt,trd.blttr_mrkt_cd,trd.blttr_cpcty_cd,
trd.cusip_nbr,trd.tran_total_amt,trd.share_trans_qty,trd.processing_dt
,min(case when tds.desc_trans_sec_txt like '%RUBY%' then tds.desc_trans_sec_txt else 'ZZZZZZZZZ' END) desc_trans_sec_txt
         from XXXX trd
         ,YYYYY,zzzzzz c 
         WHERE trd.dump_nbr ='0235'        
          AND trd.something_cd=c.something
         AND  trd.something2 between c.something4StartRange and c.something4EndRange
         AND trd.trans_acct_hist_cd='A'
         AND tds.dump_nbr=trd.dump_nbr
         AND tds.something2=trd.something2
         AND tds.something_cd=trd.something_cd
         AND tds.chck_brch_acct_nbr=trd.chck_brch_acct_nbr
         AND tds.currency_cd=trd.currency_cd
         AND tds.debit_credit_cd=trd.debit_credit_cd
         AND tds.trans_hist_seq_nbr=trd.trans_hist_seq_nbr
         AND tds.security_adp_nbr=trd.security_adp_nbr
         AND tds.trans_acct_hist_cd=trd.trans_acct_hist_cd
         AND tds.transaction_dt=trd.transaction_dt
         AND tds.processing_dt=trd.processing_dt
         AND tds.type_something2=trd.type_something2
         AND tds.language_cd=trd.language_cd
         GROUP BY trd.something2,trd.something_cd,trd.chck_brch_acct_nbr,trd.dump_nbr,trd.currency_cd,trd.debit_credit_cd,trd.trans_hist_seq_nbr,
trd.security_adp_nbr,trd.trans_acct_hist_cd,trd.transaction_dt,trd.type_something2,trd.prc_sec_amt,trd.blttr_mrkt_cd,trd.blttr_cpcty_cd,
trd.cusip_nbr,trd.tran_total_amt,trd.share_trans_qty,trd.processing_dt
    )td
    WHERE td.dump_nbr ='PPPP'  
 AND exists(select 1 from zzzzzz c where td.something_cd=c.something and td.something2 between c.something4StartRange and c.something4EndRange)       
  AND exists(select 1 from zzzzzz c where h.something=c.something and 
  h.something4 between c.something4StartRange and c.something4EndRange)    
    AND h.TransactionDate*=td.processing_dt   
    AND h.CurrencyCode*=td.currency_cd
    AND h.ADPNumber*=td.security_adp_nbr
    AND h.something*=td.something_cd
    AND h.something4*=td.something2
    AND h.Type*=td.type_something2
    AND h.CheckDigit*=td.chck_brch_acct_nbr
    AND h.TransHistSeq*=td.trans_hist_seq_nbr-1
    AND td.trans_acct_hist_cd='A'
    AND h.RecStatus in ('NEW', 'RPL')
    AND h.RecordType = 99
    AND NOT EXISTS(select 1 from dbo.something3 cst where h.something5=cst.something5)