complicated loop query

• ashley -358403

  Grasshopper

  Points: 18

  More actions

  August 14, 2006 at 7:52 pm

  #367077

  I have some data like this

  create table sample_data

  (id int identity (1, 1),

  transaction_type int,

  profit_loss int

  )

  insert into sample_data

  values (10, 200)

  insert into sample_data

  values (11, -20)

  insert into sample_data

  values (12, 100)

  insert into sample_data

  values (12, -120)

  insert into sample_data

  values (10, 110)

  insert into sample_data

  values (11, 120)

  insert into sample_data

  values (12, -40)

  insert into sample_data

  values (12, -140)

  insert into sample_data

  values (11, 40)

  insert into sample_data

  values (12, -20)

  insert into sample_data

  values (13, -400)

  insert into sample_data

  values (13, -10)

  insert into sample_data

  values (12, -30)

  insert into sample_data

  values (12, 50)

  insert into sample_data

  values (13, -100)

  insert into sample_data

  values (13, 110)

  insert into sample_data

  values (12, 40)

  insert into sample_data

  values (13, -50)

  insert into sample_data

  values (11, 30)

  select transaction_type, sum(profit_loss)as SUM from sample_data

  group by transaction_type

  THis is the result here:

  ID

  10 310

  11 170

  12 -180

  13 -450

  14 230

  15 -250

  Now we have to form groups here of sums with opposite signs so we have following groups

  (310, -180) (310, -450) (310, -250) (170, -180) (170, -450) (170, -250) (230, -180) (230, -250) (230, -450)

  I need to select groups which has the maximum positive value and maximum absolute postive value of negative part

  (310, -450) and I do this calulation to get new ID 10 and 13

  10 = 0 and 13 = 310-450 = -120

  now I get a new group like this

  ID

  10 0

  11 170

  12 -180

  13 -120

  14 230

  15 -250

  and i need to do this untill I can not make any more opposite sign groups which means there are only positive or only negetive left

  or if only one ID has a value. You can replay ID with A, B, C, D, E and F for convenience as well.

  Any solution to this.

• SwePeso

  SSC-Dedicated

  Points: 39758

  More actions

  August 15, 2006 at 12:38 am

  #655007

  Yikes!

  -- prepare test data

  declare @sample table (id int identity (1, 1), transaction_type int, profit_loss int)

  insert @sample

  select 10, 200 union all

  select 11, -20 union all

  select 12, 100 union all

  select 12, -120 union all

  select 10, 110 union all

  select 11, 120 union all

  select 12, -40 union all

  select 12, -140 union all

  select 11, 40 union all

  select 12, -20 union all

  select 13, -400 union all

  select 13, -10 union all

  select 12, -30 union all

  select 12, 50 union all

  select 13, -100 union all

  select 13, 110 union all

  select 12, 40 union all

  select 13, -50 union all

  select 11, 30

  -- do the work

  declare @stage table (transtype int, sum int)

  insert  @stage

  select  transaction_type,

    sum(profit_loss) as SUM

  from  @sample

  group by transaction_type

  select * from @stage

  select  '(' + CONVERT(varchar, w.sum) + ', ' + CONVERT(varchar, z.sum) + ')' semiresult

  from  (

     select sum

     from @stage

     where sum >= 0

   &nbsp w

  cross join (

     select sum

     from @stage

     where sum < 0

   &nbsp z

  order by w.sum desc,

    z.sum desc

  update @stage

  set sum = sum + (select max(sum) from @stage)

  where sum = (select min(sum) from @stage)

  update @stage

  set sum = 0

  where sum = (select max(sum) from @stage)

  select * from @stage

  ---

  N 56°04'39.16"
  E 12°55'05.25"