Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 complicated loop query

Author  Topic 

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2006-08-14 : 21:43:43
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.

Ashley Rhodes

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-14 : 22:55:18
That makes no sense at all.





CODO ERGO SUM
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-15 : 02:26:39
310 - 450 equals -140, not -120...

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-15 : 02:33:17
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
) w
cross join (
select sum
from @stage
where sum < 0
) 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


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-15 : 03:43:51
quote:
Originally posted by Michael Valentine Jones

That makes no sense at all.
Is it dead, Michael?

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-08-15 : 09:42:54
These are clearly some sort of exercise (another one here from the same person: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70494 ). I doubt that they are abstractions or simplifications of real-world problems; if they were, then the person doing the simplification would probably have the skill to solve the problem since that is the hardest part of solving these. (i.e., as I often say, the question is the hard part; the answer is the easy part).

Are these take home job interview questions for a senior sql developer, I wonder? Maybe homework of some sort .... They seem so academic but definitely more complicated than the simple stuff you usually see.

- Jeff
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-15 : 10:19:10
In the latter case, I then wish ashley.sql good luck for second interview...

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2006-08-15 : 15:30:18
Hi PESO,

THe code works fine but its only one loop and I need to put it into a loop until I am left with one value or with values of similar sign positive or negetive

Do u have any idea on the loop.

Ashley Rhodes
Go to Top of Page

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2006-08-15 : 18:25:42
Surprisingly these are real world mathematics queries

Ashley Rhodes
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-16 : 02:41:48
Yes, I have!

But you have to give better examples and proper expected output based on the given example data. In your first post, ID 14 and 15 does not exist as example data, but are present for the expected output.

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2006-08-16 : 08:25:20
I will post the whole data later in the evening
Thanks for offering help
No one has been able to figure this one out so far
I will give example of all the steps with 4-5 rows of data

Ashley Rhodes
Go to Top of Page

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2006-08-16 : 20:58:29
I am posting the problem again with more explanation. What you did with the code was correct but its only one iteration. I need to continue to do the same process to the final result untill I have all values of one sign left or only one value left. But here is detailed expalanation as there is some other restriction which has to be applied to the code. Suppose we have data like. Data code is at the bottom

ID Value
A 100
B -80
C 60
D -50

and we have another table with correlations among A, B, C and D

Correlation
A B .1
A C .2
A D .1
B C .2
B D .1
C D .2

WE make all the possible pairs of negative and positive

So we get (100, -80), (100, -50), (60, -80), (60, -50)
there are (A,B), (A, D), (C, B) and (C, D)

So I need to pick only that pair which has the lowest correlation value among them.

Here there are 2 such cases (A,B) and (A,D) with .1

Out of these I need to select only one which is in revese alphebetical order. so we pick (A,D)

Now with A and D we do that calculation and new A is 50 and D is 0

ID Value
A 50
B -80
C 60
D 0

Again we make pairs
(50, -80) and (-80, 60) which are (A, B) and (B, C)

Now (A, B) has lowest correlation so we pick them and since B is bigger in this case new B will be -30 and A is 0

so new data is

ID Value
A 0
B -30
C 60
D 0

Now we do that same with B and C and new C is 30 and B is 0


ID Value
A 0
B 0
C 30
D 0


I hope this data is clear. I know its a weird calulation but its to do with some predictions.


create table sample_data
(id char(1),
profit_loss int
)

insert into sample_data
values ('A', 100)


insert into sample_data
values ('B', -80)


insert into sample_data
values ('C', 60)


insert into sample_data
values ('D', -50)



create table correlation
(id1 char(1),
id2 char(1),
relation float)


insert into correlation
values ('A','B', 0.1)

insert into correlation
values ('A','C', 0.2)

insert into correlation
values ('A','D', 0.1)

insert into correlation
values ('B','C', 0.2)

insert into correlation
values ('B','D', 0.1)

insert into correlation
values ('C','D', 0.2)


Ashley Rhodes
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 02:53:59
You mean something like this?
-- prepare test data
declare @sample_data table (id char, profit_loss int)

insert @sample_data
select 'A', 100 union all
select 'B', -80 union all
select 'C', 60 union all
select 'D', -50

declare @correlation table (id1 char, id2 char, relation float)

insert @correlation
select 'A', 'B', 0.1 union all
select 'A', 'C', 0.2 union all
select 'A', 'D', 0.1 union all
select 'B', 'C', 0.2 union all
select 'B', 'D', 0.1 union all
select 'C', 'D', 0.2

-- do the magic!
DECLARE @id1 CHAR,
@profit_loss1 FLOAT,
@id2 CHAR,
@profit_loss2 FLOAT

WHILE (SELECT COUNT(*) FROM @sample_data WHERE profit_loss <> 0) > 1
BEGIN
SELECT TOP 1 @id1 = c.id1,
@profit_loss1 = s1.profit_loss,
@id2 = c.id2,
@profit_loss2 = s2.profit_loss
FROM @correlation c
INNER JOIN @sample_data s1 ON s1.id = c.id1 AND s1.profit_loss <> 0
INNER JOIN @sample_data s2 ON s2.id = c.id2 AND s2.profit_loss <> 0
ORDER BY c.relation,
s1.profit_loss DESC,
s2.profit_loss DESC

UPDATE @sample_data
SET profit_loss = CASE
WHEN ABS(@profit_loss1) < ABS(@profit_loss2) AND id = @id2 THEN profit_loss + @profit_loss1
WHEN ABS(@profit_loss1) > ABS(@profit_loss2) AND id = @id1 THEN profit_loss + @profit_loss2
ELSE 0
END
WHERE id IN (@id1, @id2)
END

-- show the output
SELECT *
FROM @sample_data

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2006-08-17 : 08:28:51
this just gives me the final result and i want to see the intermediate results as well
like this

ID Value
A 50
B -80
C 60
D 0

ID Value
A 0
B -30
C 60
D 0


Ashley Rhodes
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 08:44:32
quote:
Originally posted by ashley.sql

this just gives me the final result
Are you not satisfied with the result?

Put a SELECT * FROM @sample_data before the UPDATE statement in the WHILE LOOP.

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 08:46:11
[code]-- do the magic!
DECLARE @id1 CHAR,
@profit_loss1 FLOAT,
@id2 CHAR,
@profit_loss2 FLOAT

WHILE (SELECT COUNT(*) FROM @sample_data WHERE profit_loss <> 0) > 1
BEGIN
SELECT TOP 1 @id1 = c.id1,
@profit_loss1 = s1.profit_loss,
@id2 = c.id2,
@profit_loss2 = s2.profit_loss
FROM @correlation c
INNER JOIN @sample_data s1 ON s1.id = c.id1 AND s1.profit_loss <> 0
INNER JOIN @sample_data s2 ON s2.id = c.id2 AND s2.profit_loss <> 0
ORDER BY c.relation,
s1.profit_loss DESC,
s2.profit_loss DESC

SELECT *
FROM @sample_data


UPDATE @sample_data
SET profit_loss = CASE
WHEN ABS(@profit_loss1) < ABS(@profit_loss2) AND id = @id2 THEN profit_loss + @profit_loss1
WHEN ABS(@profit_loss1) > ABS(@profit_loss2) AND id = @id1 THEN profit_loss + @profit_loss2
ELSE 0
END
WHERE id IN (@id1, @id2)
END

-- show the output
SELECT *
FROM @sample_data[/code]
Peter Larsson
Helsingborg, Sweden
Go to Top of Page

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2006-08-20 : 12:54:30
HI Peso,

There is a slight change required to the query. I tried to figure it out but still could not get the final result. I have another sample data with resule which might help

Say this is the data

declare @sample_data table (id char, profit_loss int)

insert @sample_data
select 'A', 60 union all
select 'B', -70 union all
select 'C', 40 union all
select 'D', -70 union all
select 'E', 20

declare @correlation table (id1 char, id2 char, relation float)

insert @correlation
select 'A', 'B', 0.3 union all
select 'A', 'C', 0.4 union all
select 'A', 'D', 0.1 union all
select 'A', 'E', 1 union all
select 'B', 'C', 0.2 union all
select 'B', 'D', 0.1 union all
select 'B', 'E', 1 union all
select 'C', 'D', 0.2 union all
select 'C', 'E', 0.2 union all
select 'E', 'D', 0.2


Now we make pairs of opposite signs
from sample data

(A B), (A D) (B C), (C D), (E B), (E D)

NOW we need to consider only these pairs and find the one which has lowest relation in this case it is
(B C), (C D) AND (E D)
(-70 40) , (40, -70) AND (20, 70)

NOW ABS(-70) is the highest value which is B and D using reverse alphabetctical order we pick only pair which have D

so (C D) AND (E D) and among C and E the max value is of C

so we pick (C, D) and new values will be (0, -30)

Now we also need to calculate another thing there which is called
Leverage = 2 * min initial ABS(C, D)* (realation between C and D)
= 2 * min abs(40, 70) *0.2

= 2 * min(40, 70) * 0.2
= 2* 40 * .2
= 16

so the data we have now is

A 60
B -70
C 0
D 30
E 20


possible pairs (A B) (A D) (E B) and (E D)

lowest relation .2 in (E D)

New (E D) = (0, -10)

Calculate Leverage = = 2 * min(20, -30) * 0.2

so new data is

A 60
B -70
C 0
D -10
E 0


now possible pairs (A B) and (A D)

lowest relation is (A B) .3
so new ( A B) = (0, -10)

calculate leverage = 2 * min(60, 70) * .3

new data

A 0
B -10
C 0
D -10
E 0


so we stop since all the negetive and we take sumation of levarage in one variable.

SO making pairs and showing them is also important after each step.




Ashley Rhodes
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-20 : 13:59:34
In the first case, A-D has the lowest correlaton of 0.1 thus you have selected B-D with a correlation of 0.2, why?

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2006-08-20 : 16:49:09
sorry that is suppose to be 1
it was a typo sorry for that.
I double chekced and this is the correct data

declare @correlation table (id1 char, id2 char, relation float)

insert @correlation
select 'A', 'B', 0.3 union all
select 'A', 'C', 0.4 union all
select 'A', 'D', 1 union all
select 'A', 'E', 1 union all
select 'B', 'C', 0.2 union all
select 'B', 'D', 0.3 union all
select 'B', 'E', 1 union all
select 'C', 'D', 0.2 union all
select 'C', 'E', 0.3 union all
select 'E', 'D', 0.2


First I have selected (A B), (A D) (B C), (C D), (E B), (E D) as they are positive and negetive pairs
First we need to make all possible +ve and -ve combination and out of thse we pick the ones with lowest correlations and then the ones which have highest absolute value and then with reverse alphabetical order till we have only one pair left.




Ashley Rhodes
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-21 : 00:54:47
[code]declare @sample_data table (id char, profit_loss int)

insert @sample_data
select 'A', 60 union all
select 'B', -70 union all
select 'C', 40 union all
select 'D', -70 union all
select 'E', 20

declare @correlation table (id1 char, id2 char, relation float)

insert @correlation
select 'A', 'B', 0.3 union all
select 'A', 'C', 0.4 union all
select 'A', 'D', 1 union all
select 'A', 'E', 1 union all
select 'B', 'C', 0.2 union all
select 'B', 'D', 0.3 union all
select 'B', 'E', 1 union all
select 'C', 'D', 0.2 union all
select 'C', 'E', 0.3 union all
select 'E', 'D', 0.2

-- declare @sample_data table (id char, profit_loss int)
--
-- insert @sample_data
-- select 'A', 100 union all
-- select 'B', -80 union all
-- select 'C', 60 union all
-- select 'D', -50
--
-- declare @correlation table (id1 char, id2 char, relation float)
--
-- insert @correlation
-- select 'A', 'B', 0.1 union all
-- select 'A', 'C', 0.2 union all
-- select 'A', 'D', 0.1 union all
-- select 'B', 'C', 0.2 union all
-- select 'B', 'D', 0.1 union all
-- select 'C', 'D', 0.2

-- do the magic!
DECLARE @id1 CHAR,
@profit_loss1 FLOAT,
@id2 CHAR,
@profit_loss2 FLOAT

WHILE (SELECT COUNT(*) FROM @sample_data WHERE profit_loss <> 0) > 1
BEGIN
SELECT TOP 1 @id1 = c.id1,
@profit_loss1 = s1.profit_loss,
@id2 = c.id2,
@profit_loss2 = s2.profit_loss
FROM @correlation c
INNER JOIN @sample_data s1 ON s1.id = c.id1 AND s1.profit_loss <> 0
INNER JOIN @sample_data s2 ON s2.id = c.id2 AND s2.profit_loss <> 0
ORDER BY c.relation,
s1.profit_loss DESC,
s2.profit_loss DESC

-- SELECT *
-- FROM @sample_data

UPDATE @sample_data
SET profit_loss = CASE
WHEN ABS(@profit_loss1) < ABS(@profit_loss2) AND id = @id2 THEN profit_loss + @profit_loss1
WHEN ABS(@profit_loss1) > ABS(@profit_loss2) AND id = @id1 THEN profit_loss + @profit_loss2
ELSE 0
END
WHERE id IN (@id1, @id2)

IF (SELECT COUNT(*) FROM @sample_data WHERE profit_loss > 0) = 0
BREAK

IF (SELECT COUNT(*) FROM @sample_data WHERE profit_loss < 0) = 0
BREAK

END

-- show the output
SELECT *
FROM @sample_data[/code]

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2006-08-21 : 08:20:21
this is absolutely right
but is there a way to see the pairs formed in every loop and calculate the leverage in each loop as well
look at this post
Posted - 08/20/2006 : 12:54:30

Now we also need to calculate another thing there which is called
Leverage = 2 * min initial ABS(C, D)* (realation between C and D)
= 2 * min abs(40, 70) *0.2

= 2 * min(40, 70) * 0.2
= 2* 40 * .2
= 16




Ashley Rhodes
Go to Top of Page
    Next Page

- Advertisement -