complicated loop query

• 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"