Running total error

• pwalter83

  SSChampion

  Points: 14575

  More actions

  July 3, 2014 at 5:15 am

  #1726741

  J Livingston SQL (7/2/2014)

  ---

  try this....based on your sample data (I think)

  WITH rt_cte as (

  SELECT a.[WK NO],

  AC= MAX(AC),

  comp= MAX(COMP),

  AUSD= SUM(CASE WHEN SOA_TYPE = 'Agent' THEN USD_AMOUNT ELSE 0 END),

  FUSD= SUM(CASE WHEN SOA_TYPE = 'Freight' THEN USD_AMOUNT ELSE 0 END),

  CUST= SUM(CASE WHEN SOA_TYPE = 'Cost' THEN USD_AMOUNT ELSE 0 END),

  [AGENT not in deal] = SUM(CASE WHEN (SOA_TYPE = 'Remittance to Agent' OR SOA_TYPE = 'Fund to Agent') THEN USD_AMOUNT ELSE 0 END),

  AGENT_notindeal_WKBALANCE = SUM(CASE WHEN AC = '16208' THEN USD_AMOUNT ELSE 0 END),

  [WK BALANCE]= SUM(CASE WHEN SOA_TYPE IN ('Agent','Freight','Cost') or AC = '16208' THEN USD_AMOUNT ELSE 0 END),

  [Running Total] = (

  SELECT SUM(YourSum)

  FROM ( -- t

  SELECT SUM(CASE WHEN SOA_TYPE IN ('Agent','Freight','Cost') OR AC = '16208' THEN USD_AMOUNT ELSE 0 END) YourSum

  FROM #GLT b WHERE b.[WK NO] < = a.[WK NO] and a.COMP = b.COMP

  ) t

  ), -- [Running Total]

  rn = row_number() over (order by MAX(COMP),a.[WK NO])

  FROM #GLT a

  GROUP BY [COMP], a.[WK NO] )

  SELECT r1.[WK NO]

  , r1.AC

  , r1.comp

  , r1.AUSD

  , r1.FUSD

  , r1.CUST

  , r1.[AGENT not in deal]

  , r1.AGENT_notindeal_WKBALANCE

  , r1.[WK BALANCE]

  , r1.[Running Total]

  , CASE WHEN r1.AUSD <> 0 THEN CAST( (r1.AUSD / r2.[Running Total]) * 100 as decimal (10,2)) ELSE 0 END as remitratio

  FROM rt_cte AS r1

  left outer JOIN rt_cte AS r2

  ON r1.rn-1 = r2.rn

  Thanks a lot, your solution worked perfectly. Cheers !!