Need HELP on query

• sqlbaby2

  Ten Centuries

  Points: 1351

  More actions

  February 21, 2014 at 10:38 am

  #282779

  Need a help on below questions, tq

  DIVERSE value is '(sum of all MARKET_VALUE except ALL) - ALL value' group by CUT_DESC and PROCESS.

  RISK_FACTOR MARKET_VALUE CUT_DESC PROCESS

  DIVERSE NULL BOND M1

  FX 100 BOND M1

  EQUITY 300 BOND M1

  ALL 1000 BOND M1

  DIVERSE NULL MTNN M1

  FX 600 MTNN M1

  EQUITY 800 MTNN M1

  ALL 1200 MTNN M1

  DIVERSE NULL MTNN M2

  FX 800 MTNN M2

  EQUITY 200 MTNN M2

  ALL 9200 MTNN M2

  So the Market value for DIVERSE = -600 for CUT_DESC = BOND and PROCESS = M1;

  Market value for DIVERSE = 200 for CUT_DESC = MTNN and PROCESS = M1;

  Market value for DIVERSE = -8200 for CUT_DESC = MTNN and PROCESS = M2;

  Here the ddl

  DECLARE @emp TABLE (RISK_FACTOR VARCHAR(30), MARKET_VALUE INT, CUT_DESC VARCHAR(30), PROCESS VARCHAR(30) )

  INSERT INTO @emp(RISK_FACTOR,MARKET_VALUE,CUT_DESC,PROCESS) SELECT 'DIVERSE',NULL,'BOND','M1'

  INSERT INTO @emp(RISK_FACTOR,MARKET_VALUE,CUT_DESC,PROCESS) SELECT 'FX',100,'BOND','M1'

  INSERT INTO @emp(RISK_FACTOR,MARKET_VALUE,CUT_DESC,PROCESS) SELECT 'EQUITY',300,'BOND','M1'

  INSERT INTO @emp(RISK_FACTOR,MARKET_VALUE,CUT_DESC,PROCESS) SELECT 'ALL',1000,'BOND','M1'

  INSERT INTO @emp(RISK_FACTOR,MARKET_VALUE,CUT_DESC,PROCESS) SELECT 'DIVERSE',NULL,'MTNN','M1'

  INSERT INTO @emp(RISK_FACTOR,MARKET_VALUE,CUT_DESC,PROCESS) SELECT 'FX',600,'MTNN','M1'

  INSERT INTO @emp(RISK_FACTOR,MARKET_VALUE,CUT_DESC,PROCESS) SELECT 'EQUITY',800,'MTNN','M1'

  INSERT INTO @emp(RISK_FACTOR,MARKET_VALUE,CUT_DESC,PROCESS) SELECT 'ALL',1200,'MTNN','M1'

  INSERT INTO @emp(RISK_FACTOR,MARKET_VALUE,CUT_DESC,PROCESS) SELECT 'DIVERSE',NULL,'MTNN','M2'

  INSERT INTO @emp(RISK_FACTOR,MARKET_VALUE,CUT_DESC,PROCESS) SELECT 'FX',800,'MTNN','M2'

  INSERT INTO @emp(RISK_FACTOR,MARKET_VALUE,CUT_DESC,PROCESS) SELECT 'EQUITY',200,'MTNN','M2'

  INSERT INTO @emp(RISK_FACTOR,MARKET_VALUE,CUT_DESC,PROCESS) SELECT 'ALL',9200,'MTNN','M2'

• KS

  Hall of Fame

  Points: 3323

  More actions

  February 21, 2014 at 11:02 am

  #1691242

  Try the below...

  UPDATE A

  SET A.MARKET_VALUE = B.MARKET_VALUE

  FROM @Emp AS A INNER JOIN

  (

  SELECT 'DIVERSE' AS RISK_FACTOR,

  SUM(CASE RISK_FACTOR WHEN 'ALL' THEN MARKET_VALUE*-1

  ELSE MARKET_VALUE END) MARKET_VALUE,

  CUT_DESC,PROCESS

  FROM @Emp

  WHERE RISK_FACTOR <> 'DIVERSE'

  GROUP BY CUT_DESC,PROCESS

  ) AS B ON A.CUT_DESC = B.CUT_DESC

  AND A.PROCESS = B.PROCESS

  AND A.RISK_FACTOR = 'DIVERSE'

  SELECT * FROM @EMP

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  February 21, 2014 at 11:03 am

  #1691243

  Here's an option that you could use. Be sure to understand what it does before implementing it. If you have any questions, feel free to ask.

  SELECT e1.RISK_FACTOR,

  CASE WHEN RISK_FACTOR = 'DIVERSE' THEN sumed.DIVERSE_MARKET_VALUE ELSE e1.MARKET_VALUE END MARKET_VALUE,

  e1.CUT_DESC,

  e1.PROCESS

  FROM @emp e1

  CROSS APPLY(

  SELECT SUM( CASE WHEN RISK_FACTOR NOT IN('DIVERSE', 'ALL') THEN MARKET_VALUE END)

  - SUM( CASE WHEN RISK_FACTOR = 'ALL' THEN MARKET_VALUE END) DIVERSE_MARKET_VALUE

  FROM @emp e2

  WHERE e1.CUT_DESC = e2.CUT_DESC

  AND e1.PROCESS = e2.PROCESS) sumed

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• sqlbaby2

  Ten Centuries

  Points: 1351

  More actions

  February 21, 2014 at 7:53 pm

  #1691322

  Thanks dude !!