need sql

• tabrez.test

  SSC-Addicted

  Points: 459

  More actions

  January 4, 2010 at 5:55 am

  #219003

  Hi

  i have transcationtype ,LOB, transactionGroup columns in a table trans

  transcationtypeLOBTansactionGroup

  --------------------- ---- ------------

  CANABCAN

  CABABCAN

  nEWABNEW

  CPPACCAN

  KARABOthers

  RNSABOthers

  RNS ABOthers

  CBRABCAN

  RNSACOthers

  NewABNEW

  XXXABOthers

  TtRANSACTIONgROUP Column is obtained as follows

  CASE WHEN TransactionType IN ('CAN','CPP','CBR','CAB') THEN 'CAN'

  WHEN TransactionType ='NEW' THEN 'NEW'

  WHEN TransactionType NOT IN ('CAN','CPP','CBR','CAB','NEW') THEN 'Others'

  Now iwant to display the total count of CAN from transactionGroup - total Count of RNS Transactiontype

  without affecting Others and New

  i.e., out put should be 4-2 =2

  (total count of CAN from transactionGroup) 4 - (total Count of RNS Transactiontype)2=2

  OUTPUT column

  --------------

  LOB TansactionGroup Count

  ---- ---------------- ---------

  ABCAN1----------(3-2)

  ABOthers4

  AB NEW2

  ACCAN0-----------(1-1)

  ACOthers1

• tabrez.test

  SSC-Addicted

  Points: 459

  More actions

  January 4, 2010 at 6:00 am

  #1098360

  i have tried the following query but count is also substracting from others & new (but it should not)

  select LineOfBusiness,TransactionGroup,count(TransactionGroup )-

  (select count(TransactionType) from dbo.Test a where a.LineOfBusiness=b.LineOfBusiness and a.TransactionType='RNS'

  )

  from dbo.Test b where b.TransactionGroup='CAN'

  group by LineOfBusiness,TransactionGroup

• Nabha

  SSCrazy Eights

  Points: 8534

  More actions

  January 4, 2010 at 6:40 am

  #1098378

  I guess u were close ( if i am not wrong about your requirement).

  (Please read this for your future posts, (u might get quicker and varied responses)

  http://www.sqlservercentral.com/articles/Best+Practices/61537/)

  DOes this help?

  Create table #temp(transcationtype varchar(10), LOB varchar

  (10),TansactionGroup varchar(10))

  INSERT INTO #temp

  SELECT 'CAN', 'AB' ,'CAN'

  UNION ALL

  SELECT 'CAB', 'AB', 'CAN'

  UNION ALL

  SELECT 'nEW', 'AB', 'NEW'

  UNION ALL

  SELECT

  'CPP', 'AC' ,'CAN'

  UNION ALL

  SELECT

  'KAR', 'AB' ,'Others'

  UNION ALL

  SELECT

  'RNS', 'AB' ,'Others'

  UNION ALL

  SELECT

  'RNS', 'AB' ,'Others'

  UNION ALL

  SELECT

  'CBR', 'AB' ,'CAN'

  UNION ALL

  SELECT

  'RNS', 'AC' ,'Others'

  UNION ALL

  SELECT

  'New', 'AB' ,'NEW'

  UNION ALL

  SELECT

  'XXX', 'AB', 'Others'

  Select * from #temp

  Select LOB, TansactionGroup ,

  Count(*) - (Select Count(*) from #temp t2 where t1.LOB = t2.LOB AND

  transcationtype ='RNS' AND t1.TansactionGroup = 'CAN')

  from #temp t1

  Group BY LOB, TansactionGroup

  ---------------------------------------------------------------------------------