CODEWITHSUNDEEP
sqljoinunpivot
Anu
Anu

Reputation: 176

Join and unpivot on multiple tables

I have two tables:

Correction table:

C_CORRECTIONS
CorrAmount  |HalfYear1  |HalfYear2  |Type   |REFNUM |
-----------------------------------------------------
1000        |50         |50         |HYINT  |R123   |

Collected Table:

P_COLLECTED
Amount      |Type   |Account    |REFNUM |
-----------------------------------------
1500        |TOTAL  |ACCT1      |R123   |
50          |HYINT  |ACCT2      |R123   |
50          |HYINT  |ACCT3      |R123   |

What I need is: 

CorrAmount  |Type   |Account    |REFNUM |
-----------------------------------------
1000        |TOTAL  |ACCT1      |R123   |
50          |HYINT  |ACCT2      |R123   |
50          |HYINT  |ACCT3      |R123   |

I have tried below: 

SELECT DISTINCT C.AMT, P.ACCOUNT,C.REFNUM FROM (
SELECT U.AMT, U.Type, REFNUM
FROM C_CORRECTIONS
UNPIVOT
(
  AMT
  FOR INT_AMT IN (CorrAmount,HalfYear1,HalfYear2)
) U
) C
LEFT JOIN P_COLLECTED P
ON C.REFNUM = P.REFNUM AND C.AMT = P.Amount;

Result:

AMT     |ACCOUNT    |REFNUM |
--------|-----------|-------|
50      |ACCT2      |R123   |
50      |ACCT3      |R123   |
1000    |           |R123   |

Edit: Okay. So i have used left join instead of join, and put in distinct and i get main amount, but i cannot seem to get the account for it yet.

Upvotes: 2

Views: 3091

Answers (2)

LukStorms
LukStorms

Reputation: 29667

You unpivot C_CORRECTIONS and then join P_COLLECTED to it.

But perhaps you could start from P_COLLECTED and join C_CORRECTIONS to it.
Since P_COLLECTED already has the multiple rows.

Test example: 

declare @P_COLLECTED table (Amount int, Type varchar(8), Account varchar(8), REFNUM varchar(8));
declare @C_CORRECTIONS table (CorrAmount int, HalfYear1 int, HalfYear2 int, Type varchar(8), REFNUM varchar(8));

insert into @P_COLLECTED (Amount, Type, Account, REFNUM) values
(1500,'TOTAL','ACCT1','R123'),
(50  ,'HYINT','ACCT2','R123'),
(50  ,'HYINT','ACCT3','R123');

insert into @C_CORRECTIONS (CorrAmount, HalfYear1, HalfYear2, Type, REFNUM) values
(1000,24,26,'HYINT','R123');

select 
(case when t.Type = 'TOTAL' then c.CorrAmount else t.Amount end) as Amount,
t.Type, t.Account, t.REFNUM
-- , c.*
from @P_COLLECTED t
left join @C_CORRECTIONS c 
      -- on (t.REFNUM = c.REFNUM and t.Type in ('TOTAL',c.Type));
      on (t.REFNUM = c.REFNUM and t.Type = 'TOTAL');

Upvotes: 1

mohan111
mohan111

Reputation: 8865

Hi I have modified in your script please verify 

Select  Amount, 
        Type,
        'Acct' + CAST(ROW_NUMBER()OVER(PARTITION BY  Refnum  ORDER BY (Select  NULL))AS VARCHAR(20))Account,REFNUM 
                from  
            (Select Amount, Type, REFNUM from  @Table2 where type = 'Total'
                    UNION ALL
            SELECT C.AMT, C.Type,C.REFNUM FROM (
            SELECT U.AMT, U.Type, REFNUM
            FROM @Table1
            UNPIVOT
            (
              AMT
              FOR INT_AMT IN (CorrAmount,HalfYear1,HalfYear2)
            ) U
            ) C,@Table2 P
            WHERE C.REFNUM = P.REFNUM OR  C.AMT = P.Amount
            GROUP BY C.AMT, C.Type,C.REFNUM)T

Upvotes: 1

Related Questions