I have two tables, which i need to match the records.
Table1 fields are client, ref1, ref2, amount
Table 2 fields are client,ref2,amount
- Eventhough ref2 field available in both tables in table 1 it is not available in some cases.
So i have to look for matches based on client & amount for those items that does not have ref2. - Also in table 1 records are in more detailed level. So the amounts has to sum up to each ref1 level under each client before match with table 2.
but i want to first look for ref2 & value matches & then matche the remain items solely on values.
I'm not sure how code for this.It is great If someone can help on this.
--Table1
CREATE TABLE [TEST].[dbo].[tb1]
(Client FLOAT, Ref1 FLOAT, Ref2 FLOAT, Value FLOAT)
INSERT INTO [TEST].[dbo].[tb1] VALUES
(100,1001,null,2000),
(100,1001,null,2000),
(100,1002,20001,5000),
(200,2001,null,1500),
(200,2001,null,2000),
(200,2002,null,4000),
(300,3001,10001,4000)
CREATE TABLE [TEST].[dbo].[tb2]
(Ref nvarchar(50), Client FLOAT, Ref2 FLOAT, Value FLOAT)
INSERT INTO [TEST].[dbo].[tb2] VALUES
('A',100,20000,4000),
('B',100,20001,4000),
('C',200,10001,3500),
('D',200,10002,4000),
('E',300,null,4000)
--My code i tries is below. but it does not check the ref2 column at all. This methode is taking a long time as my real tables are big. Hope someone can tell me a better way.
SELECT
distinct Set2.Ref1,
Set1.Client,
Set1.TotalValue,
Set2.ref2,
Set2.Value,
set3.ref 't2 ref',
Set3.Client 't2 client',
Set3.Ref2 't2 ref2',
Set3.Value 't2 value'
FROM
(SELECT t1.Client, t1.ref1, Sum(t1.Value) as TotalValue
FROM [TEST].[dbo].[tb1] t1
GROUP BY t1.Client, t1.ref1
) as Set1
INNER JOIN
(SELECT t11.Client, t11.ref1, t11.ref2, t11.Value
FROM [TEST].[dbo].[tb1] t11
) as Set2
ON Set2.ref1 = Set1.ref1
INNER JOIN
(SELECT t2.Ref,t2.Client, t2.ref2, t2.Value
FROM [TEST].[dbo].[tb2] t2
) as Set3
ON Set1.TotalValue = Set3.Value
WHERE Set1.Client = Set3.Client and
Set1.Client = Set2.Client
My Result i'm getting
Ref1 Client TotalValue ref2 Value t2 ref t2 client t2 ref2 t2 value
1001 100 4000 NULL 2000 A 100 20000 4000
1001 100 4000 NULL 2000 B 100 20001 4000
1002 100 4000 20001 4000 A 100 20000 4000
1002 100 4000 20001 4000 B 100 20001 4000
2001 200 3500 NULL 1500 C 200 10001 3500
2001 200 3500 NULL 2000 C 200 10001 3500
2002 200 4000 NULL 4000 D 200 10002 4000
3001 300 4000 10001 4000 E 300 NULL 4000