Running totals

Question

Running totals

Scottye McClain

SSC-Addicted

Points: 455
More actions
October 19, 2005 at 10:30 am

#91581

I’m hitting a brick wall or something. I’m trying to take a monthly running total of commissions and a yearly running total of commission by employee and I’m having issues getting the code to generate this properly. I’ve looked at this for so long; I’m forgetting what I’ve been looking at. I’ve tried creating a temp table and then referencing the temp table. And I even tried declaring values and creating a cursor, etc. I’m going ‘cross-eyed’ here. Here's what I originally started out doing:
select
  empnumber,
  sub_no,
  firm_no,
  split_rep,
  tag_exch_group,
  tag_prod_code,
  acct_no,
  buy_sell_code,
  sec_no,
  adj_trade_ymd,
  settle_ymd,
  new_qty,
  adj_price,
  commission,
  trading_int,
  principal,
  sec_fee,
  postage_chg,
  handling,
  mark_up_down,
  discount_pct,
  net_amt,
  offset_acct,
  tag_lmt_mkt,
  cash_si,
  margin_si,
  inst_code,
  acct_class,
  ivan_usr_fld,
  tag_spec_type,
  syndicate_ind,
  dscr_used_sw,
  pay_period_sw,
  key1,
  class,
  discr_trading_code,
  bond_type,
  coupon_rate,
  non_listed_code,
  oats_otc_exch,
  underlying_sec,
  sort_group,
  sec_type,
  new_sec_type,
  symbol,
  cusip,
  sec_desc1,
  dd_sec_type,
  primary_rep_code,
  rep_qty,
  rep_comm,
  rep_principal,
  rep_sec_fee,
  rep_postage_chg,
  rep_handling,
  rep_mark_up_down,
  rep_net_amt,
  rep_cps_qty,
  rep_cps,
  CASE
       When tag_prod_code < 625
       and substring(update_dtm,3,2) = month_ind
       then SUM(rep_comm)
       ELSE rep_comm
  END as rep_acr_comm_MO,
  CASE
       When tag_prod_code < 625
       and left(adj_trade_ymd,2) = year_ind
       then SUM(rep_comm)
       ELSE rep_comm
  END as rep_acr_comm_YR,
  month_ind,
  year_ind,
  update_dtm
from PS_COD_split
group by
empnumber,
sub_no,
firm_no,
split_rep,
tag_exch_group,
tag_prod_code,
acct_no,
buy_sell_code,
sec_no,
adj_trade_ymd,
settle_ymd,
new_qty,
adj_price,
commission,
trading_int,
principal,
sec_fee,
postage_chg,
handling,
mark_up_down,
discount_pct,
net_amt,
offset_acct,
tag_lmt_mkt,
cash_si,
margin_si,
inst_code,
acct_class,
ivan_usr_fld,
tag_spec_type,
syndicate_ind,
dscr_used_sw,
pay_period_sw,
key1,
class,
discr_trading_code,
bond_type,
coupon_rate,
non_listed_code,
oats_otc_exch,
underlying_sec,
sort_group,
sec_type,
new_sec_type,
symbol,
cusip,
sec_desc1,
dd_sec_type,
primary_rep_code,
rep_qty,
rep_comm,
rep_principal,
rep_sec_fee,
rep_postage_chg,
rep_handling,
rep_mark_up_down,
rep_net_amt,
rep_cps_qty,
rep_cps,
month_ind,
year_ind,
update_dtm

order by
  adj_trade_ymd,
  empNumber
GO

Somebody please save my eyesight, as well as my sanity.
Thanks - Scottye

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

I see at least one thing. You are summing rep_comm, but you are also selecting and grouping on this field. This will throw off your summation as it will treat each value as unique....

I wasn't born stupid - I had to study.

Scottye McClain SSC-Addicted Points: 455 More actions · Answer 2

Thanks... you were right. Not to mention the fact that I had a whole plethora of other issues with trying to sum that one copmun based on spedific criteria. I would up creating the procedure and allowing it to throw select commission data into one temp table based upon month. I then threw the same data into a second temp table based upon year. I then inserted ALL the data into the FINAL table and joined with the two temp tables where the employee number was equal across all three tables.

Thanks for your help.

Scottye