August 26, 2011 at 12:48 am
Hi,
I am having two tables with below data:
--------------------------------------------------------------------------------
Name,id,value
--------------------------------------------------------------------------------
A1,1,100
A2,1,200
A3,1,300
B1,2,100
B2,2,200
table2
--------------------------------------------------------------------------------
id,value1
--------------------------------------------------------------------------------
1,50
1,25
1,25
1,100
1,100
2,50
2,50
2,150
2,50
My result should be:
Name,id,value
--------------------------------------------------------------------------------
A1,1,50
A2,1,25
A3,1,25
A1,1,100
A2,1,100
B1,2,50
B2,2,50
B1,2,150
B2,2,50
The join key is id field.
In simple terms, if table 1 contains A1,A2 and A3 and my table 2 contains 6 records then each should be distributed 2 times.
If table 2 contains 7 records, it should contain additional A1.so 3times A1,2times A2, 2 times A3.
Please help me in this.
thanks
Thanks,
Pandeeswaran
August 26, 2011 at 1:32 am
How about this?
DECLARE @FirstTable TABLE
(
Name VARCHAR(10)
,id INT
,value VARCHAR(10)
)
INSERT INTO @FirstTable
SELECT 'A1',1,100
UNION ALL SELECT 'A2',1,200
UNION ALL SELECT 'A3',1,300
UNION ALL SELECT 'B1',2,100
UNION ALL SELECT 'B2',2,200
DECLARE @SecondTable TABLE
(
id INT
,value VARCHAR(10)
)
INSERT INTO @SecondTable
SELECT 1,50
UNION ALL SELECT 1,25
UNION ALL SELECT 1,25
UNION ALL SELECT 1,100
UNION ALL SELECT 1,100
UNION ALL SELECT 2,50
UNION ALL SELECT 2,50
UNION ALL SELECT 2,150
UNION ALL SELECT 2,50
; WITH CTE_1 AS
(
SELECT Name , id , Value ,
RN = ROW_NUMBER() OVER(PARTITION BY id ORDER BY name) ,
CountofMembersInGroup = COUNT(*) OVER(PARTITION BY id )
FROM
@FirstTable
),
CTE_2 AS
(
SELECT id , value,
RN = ROW_NUMBER() OVER(PARTITION BY id ORDER BY (SELECT NULL))
FROM
@SecondTable
)
SELECT CrsApp.Name , OuterTable.id , OuterTable.value
FROM CTE_2 OuterTable
CROSS APPLY
(
SELECT InnerTable.Name , InnerTable.id, InnerTable.value
FROM CTE_1 InnerTable
WHERE InnerTable.id = OuterTable.id
AND InnerTable.RN = CASE WHEN OuterTable.RN - InnerTable.CountofMembersInGroup <= 0 THEN OuterTable.RN
ELSE OuterTable.RN - InnerTable.CountofMembersInGroup
END
) CrsApp
This may not be the best performant code, but this certainly gets you going. Its 12.30 midnight here and this is all i came up with with drowsy eyes :-D. I will see if i could cook up some neat logic to do this, i know there are far better of doing this.
Cheers..
August 26, 2011 at 1:32 am
Why would the following be invalid? (still trying to figure out the business rules...)
--------------------------------------------------------------------------------
A1,1,25
A2,1,100
A3,1,25
A1,1,50
A2,1,100
B1,2,50
B1,2,50
B2,2,150
B2,2,50
Keep in mind, there is no special order of rows unless you can specify it using T-SQL.
So, please provide the ORDER BY statement you'd like to use to sort your value1 results.
From my point of view, at least table2 is lacking an ID column to identify a row.
Assuming, a row can be identified, the following might work:
DECLARE @tbl1 TABLE
(
NAME CHAR(2),id int,VALUE int
)
INSERT INTO @tbl1
VALUES ('A1',1,100),
('A2',1,200),
('A3',1,300),
('B1',2,100),
('B2',2,200)
DECLARE @tbl2 TABLE
(
id INT IDENTITY(1,1), ref_id int,value1 int
)
INSERT INTO @tbl2
VALUES(1,50),
(1,25),
(1,25),
(1,100),
(1,100),
(2,50),
(2,50),
(2,150),
(2,50)
; WITH cte_cnt1 AS
(
SELECT id, Name,ROW_NUMBER() OVER(PARTITION BY id ORDER BY name) AS pos
FROM @tbl1
), cte_t2 as
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY ref_id ORDER BY t2.id) AS pos
FROM @tbl2 t2
)
SELECT cte_cnt1.name,cte_t2.ref_id,cte_t2.value1
FROM cte_t2
INNER JOIN cte_cnt1 ON cte_cnt1.id=cte_t2.ref_id
CROSS APPLY
(
SELECT COUNT(*) AS cnt
FROM @tbl1 t1
WHERE t1.id=cte_t2.ref_id
) x
WHERE cte_cnt1.pos = CASE WHEN cte_t2.pos % cnt = 0 THEN cnt ELSE cte_t2.pos % cnt END
August 26, 2011 at 1:36 am
LutzM (8/26/2011)
Assuming, a row can be identified, the following might work:
Oh Mr.Lutz :w00t: i posted a code which was fairly similar to yours 🙂 i was tempted to solve this puzzle 🙂
August 28, 2011 at 11:34 pm
Now there's a slight change in my requirement:
--------------------------------------------------------------------------------
Name,id,value
--------------------------------------------------------------------------------
A1,1,100
A2,1,200
B1,2,100
B2,2,200
table2
--------------------------------------------------------------------------------
id,value1
--------------------------------------------------------------------------------
1,50
1,25
1,25
1,100
1,100
2,50
2,50
2,150
2,50
My result should be:
Name,id,value
--------------------------------------------------------------------------------
A1,1,50
A1,1,25
A1,1,25
A2,1,100
A2,1,100
B1,2,50
B1,2,50
B2,2,150
B2,2,50
if you take the value of A1 in table1=100
so the sum(values of A1 in result)=50+25+25=100.
But the order in which the distribution happens doesn't matter.
So, there may be different results also.
This may be also the result:
Name,id,value
--------------------------------------------------------------------------------
A1,1,100
A2,1,50
A2,1,25
A2,1,25
A2,1,100
B1,2,50
B1,2,50
B2,2,150
B2,2,50
But the sum should be equal.
Please help me in this.
Thanks
Thanks,
Pandeeswaran
August 29, 2011 at 10:30 am
Nope, no (easy) way to solve it from my point of view.
How would you sort the following:
A1,1,100
A2,1,200
1,60
1,30
1,30
1,20
1,55
1,20
1,15
1,30
1,25
1,5
1,20
There are multiple possible solution and it would require a minimum of three steps to find the first 100 match. It can get way more complicated though. From my point of view we're talking about a permutation problem here.
August 29, 2011 at 10:40 am
I second Lutz here, it is pure Permutation problem. Unless we have predefined rules on what to be chosen first when a Permutation Sum is matched, it is a painful task to assign/order values. Even with the rules set it is going to be tough, IMHO.
August 29, 2011 at 10:48 am
As a rule, we can have first matching combination to be selected:
for Mr.Lutz example,we can put A1 for first occuring combination(30,30,20,20).
Please let me know if you need any more information.
Thanks
Thanks,
Pandeeswaran
August 29, 2011 at 10:52 am
pandeesh (8/29/2011)
As a rule, we can have first matching combination to be selected:for Mr.Lutz example,we can put A1 for first occuring combination(30,30,20,20).
Please let me know if you need any more information.
Thanks
And now please tell me the logic (math) how you found those values.
I can't see any indicator of being the "first occuring combination". What rule did apply?
August 29, 2011 at 10:58 am
A1,1,100
A2,1,200
1,60
1,30
1,30
1,20
1,55
1,20
1,15
1,30
1,25
1,5
1,20
For the above data,
A1 value is 100
in table2 ,the first value is 60
So add the next value=60+30=90
then add the next value=90+30=120>100 so omit the 3rd valueand add 90 with the 4th value and check the value is >100.if it matches we can take that combination else skip that
and proceed with the next combination.
Like that we need to iterate..
Thanks
Thanks,
Pandeeswaran
August 29, 2011 at 11:07 am
The solution is going to be very very tough and even if it does, it wont scale up at all..
August 29, 2011 at 11:52 am
Did you try to iterate through the example I posted?
You might find that the concept you described won't lead to a valid solution since we would need a value of 10 to make it a valid solution. Unfortunately, there isn't one. Neither two values of 5. The logic failed.
Another example (A1:100, A2:100, A3:100:
30
30
20
10
10
50
20
40
20
50
20
Applying the logic you described, we won't be able to divide the three groups accordingly.
Like I said, a permutation problem...
What would be the business reason behind it? Would you please elaborate?
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply