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.
Author |
Topic |
ashley.sql
Constraint Violating Yak Guru
299 Posts |
Posted - 2006-08-14 : 21:43:43
|
I have some data like thiscreate table sample_data(id int identity (1, 1),transaction_type int,profit_loss int)insert into sample_datavalues (10, 200)insert into sample_datavalues (11, -20)insert into sample_datavalues (12, 100)insert into sample_datavalues (12, -120)insert into sample_datavalues (10, 110)insert into sample_datavalues (11, 120)insert into sample_datavalues (12, -40)insert into sample_datavalues (12, -140)insert into sample_datavalues (11, 40)insert into sample_datavalues (12, -20)insert into sample_datavalues (13, -400)insert into sample_datavalues (13, -10)insert into sample_datavalues (12, -30)insert into sample_datavalues (12, 50)insert into sample_datavalues (13, -100)insert into sample_datavalues (13, 110)insert into sample_datavalues (12, 40)insert into sample_datavalues (13, -50)insert into sample_datavalues (11, 30)select transaction_type, sum(profit_loss)as SUM from sample_datagroup by transaction_typeTHis is the result here:ID10 31011 17012 -18013 -45014 23015 -250Now 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 1310 = 0 and 13 = 310-450 = -120now I get a new group like thisID10 011 17012 -18013 -12014 23015 -250and i need to do this untill I can not make any more opposite sign groups which means there are only positive or only negetive leftor 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 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-08-15 : 02:26:39
|
310 - 450 equals -140, not -120...Peter LarssonHelsingborg, Sweden |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-08-15 : 02:33:17
|
Yikes!-- prepare test datadeclare @sample table (id int identity (1, 1), transaction_type int, profit_loss int)insert @sampleselect 10, 200 union allselect 11, -20 union allselect 12, 100 union allselect 12, -120 union allselect 10, 110 union allselect 11, 120 union allselect 12, -40 union allselect 12, -140 union allselect 11, 40 union allselect 12, -20 union allselect 13, -400 union allselect 13, -10 union allselect 12, -30 union allselect 12, 50 union allselect 13, -100 union allselect 13, 110 union allselect 12, 40 union allselect 13, -50 union allselect 11, 30-- do the workdeclare @stage table (transtype int, sum int)insert @stageselect transaction_type, sum(profit_loss) as SUMfrom @samplegroup by transaction_typeselect * from @stageselect '(' + CONVERT(varchar, w.sum) + ', ' + CONVERT(varchar, z.sum) + ')' semiresultfrom ( select sum from @stage where sum >= 0 ) wcross join ( select sum from @stage where sum < 0 ) zorder by w.sum desc, z.sum descupdate @stageset sum = sum + (select max(sum) from @stage)where sum = (select min(sum) from @stage)update @stageset sum = 0where sum = (select max(sum) from @stage)select * from @stage Peter LarssonHelsingborg, Sweden |
|
|
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 LarssonHelsingborg, Sweden |
|
|
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 |
|
|
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 LarssonHelsingborg, Sweden |
|
|
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 negetiveDo u have any idea on the loop.Ashley Rhodes |
|
|
ashley.sql
Constraint Violating Yak Guru
299 Posts |
Posted - 2006-08-15 : 18:25:42
|
Surprisingly these are real world mathematics queriesAshley Rhodes |
|
|
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 LarssonHelsingborg, Sweden |
|
|
ashley.sql
Constraint Violating Yak Guru
299 Posts |
Posted - 2006-08-16 : 08:25:20
|
I will post the whole data later in the eveningThanks for offering helpNo one has been able to figure this one out so farI will give example of all the steps with 4-5 rows of dataAshley Rhodes |
|
|
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 bottomID ValueA 100B -80C 60D -50and we have another table with correlations among A, B, C and D CorrelationA B .1A C .2A D .1B C .2B D .1C D .2WE make all the possible pairs of negative and positiveSo 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 .1Out 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 0ID ValueA 50B -80C 60D 0Again 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 0so new data is ID ValueA 0B -30C 60D 0Now we do that same with B and C and new C is 30 and B is 0ID ValueA 0B 0C 30D 0I 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_datavalues ('A', 100)insert into sample_datavalues ('B', -80)insert into sample_datavalues ('C', 60)insert into sample_datavalues ('D', -50)create table correlation(id1 char(1), id2 char(1), relation float)insert into correlationvalues ('A','B', 0.1)insert into correlationvalues ('A','C', 0.2)insert into correlationvalues ('A','D', 0.1)insert into correlationvalues ('B','C', 0.2)insert into correlationvalues ('B','D', 0.1)insert into correlationvalues ('C','D', 0.2)Ashley Rhodes |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-08-17 : 02:53:59
|
You mean something like this?-- prepare test datadeclare @sample_data table (id char, profit_loss int)insert @sample_dataselect 'A', 100 union allselect 'B', -80 union allselect 'C', 60 union allselect 'D', -50declare @correlation table (id1 char, id2 char, relation float)insert @correlationselect 'A', 'B', 0.1 union allselect 'A', 'C', 0.2 union allselect 'A', 'D', 0.1 union allselect 'B', 'C', 0.2 union allselect 'B', 'D', 0.1 union allselect 'C', 'D', 0.2-- do the magic!DECLARE @id1 CHAR, @profit_loss1 FLOAT, @id2 CHAR, @profit_loss2 FLOATWHILE (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 outputSELECT *FROM @sample_data Peter LarssonHelsingborg, Sweden |
|
|
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 welllike thisID ValueA 50B -80C 60D 0ID ValueA 0B -30C 60D 0Ashley Rhodes |
|
|
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 LarssonHelsingborg, Sweden |
|
|
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 FLOATWHILE (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 outputSELECT *FROM @sample_data[/code]Peter LarssonHelsingborg, Sweden |
|
|
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 helpSay this is the datadeclare @sample_data table (id char, profit_loss int)insert @sample_dataselect 'A', 60 union allselect 'B', -70 union allselect 'C', 40 union allselect 'D', -70 union allselect 'E', 20declare @correlation table (id1 char, id2 char, relation float)insert @correlationselect 'A', 'B', 0.3 union allselect 'A', 'C', 0.4 union allselect 'A', 'D', 0.1 union allselect 'A', 'E', 1 union allselect 'B', 'C', 0.2 union allselect 'B', 'D', 0.1 union allselect 'B', 'E', 1 union allselect 'C', 'D', 0.2 union allselect 'C', 'E', 0.2 union allselect 'E', 'D', 0.2 Now we make pairs of opposite signsfrom 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 Dso (C D) AND (E D) and among C and E the max value is of Cso 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 60B -70C 0D 30E 20possible 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.2so new data is A 60B -70C 0D -10E 0now possible pairs (A B) and (A D)lowest relation is (A B) .3so new ( A B) = (0, -10)calculate leverage = 2 * min(60, 70) * .3new dataA 0B -10C 0D -10E 0so 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 |
|
|
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 LarssonHelsingborg, Sweden |
|
|
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 datadeclare @correlation table (id1 char, id2 char, relation float)insert @correlationselect 'A', 'B', 0.3 union allselect 'A', 'C', 0.4 union allselect 'A', 'D', 1 union allselect 'A', 'E', 1 union allselect 'B', 'C', 0.2 union allselect 'B', 'D', 0.3 union allselect 'B', 'E', 1 union allselect 'C', 'D', 0.2 union allselect 'C', 'E', 0.3 union allselect '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 pairsFirst 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 |
|
|
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_dataselect 'A', 60 union allselect 'B', -70 union allselect 'C', 40 union allselect 'D', -70 union allselect 'E', 20declare @correlation table (id1 char, id2 char, relation float)insert @correlationselect 'A', 'B', 0.3 union allselect 'A', 'C', 0.4 union allselect 'A', 'D', 1 union allselect 'A', 'E', 1 union allselect 'B', 'C', 0.2 union allselect 'B', 'D', 0.3 union allselect 'B', 'E', 1 union allselect 'C', 'D', 0.2 union allselect 'C', 'E', 0.3 union allselect '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 FLOATWHILE (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 outputSELECT *FROM @sample_data[/code]Peter LarssonHelsingborg, Sweden |
|
|
ashley.sql
Constraint Violating Yak Guru
299 Posts |
Posted - 2006-08-21 : 08:20:21
|
this is absolutely rightbut is there a way to see the pairs formed in every loop and calculate the leverage in each loop as welllook at this postPosted - 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= 16Ashley Rhodes |
|
|
Next Page
|
|
|
|
|