asked
How to get the value from CURRENT or PREDECESSOR by Joining two tables?
I have 2 tables **Table 1** - This is the main table **Table 2** - This is the lookup table where I need to get the assigned country for several users within a specific month and year. **Basically the rule should goes like this:** 1. FIRST we need to get all matching records from Table1 and Table2 (User, Month, Year) 2. If the combination of MONTH & YEAR from Table1 does not exist from Table2, we then get the values from the CLOSEST MINIMUM combination of MONTH & YEAR from Table2.See Expected Result below. **Table 1** -------- **| USER | MONTH | YEAR |** | RUSSEL | 1 | 2014 | | RUSSEL | 4 | 2014 | | RUSSEL | 6 | 2014 | | RUSSEL | 8 | 2014 | ---------- **Table 2** -------- **| USER | MONTH | YEAR | CTRY** | RUSSEL | 1 | 2014 | PH | RUSSEL | 5 | 2014 | MY | RUSSEL | 8 | 2014 | SG ---------- ***EXCPECTED RESULT*** ---------- **| USER | MONTH | YEAR | CTRY** | RUSSEL | 1 | 2014 | PH | RUSSEL | 4 | 2014 | PH | RUSSEL | 6 | 2014 | MY | RUSSEL | 8 | 2014 | SG
DECLARE @Table1 TABLE ( [USER] VARCHAR(50), [MONTH] TINYINT, [YEAR] SMALLINT ) INSERT INTO @Table1([USER],[MONTH],[YEAR]) VALUES ('RUSSEL',1,2014), ('RUSSEL',4,2014), ('RUSSEL',6,2014), ('RUSSEL',8,2014) DECLARE @Table2 TABLE ( [USER] VARCHAR(50), [MONTH] TINYINT, [YEAR] SMALLINT, [CTRY] VARCHAR(10) ) INSERT INTO @Table2([USER],[MONTH],[YEAR],[CTRY]) VALUES ('RUSSEL',1,2014,'PH'), ('RUSSEL',5,2014,'MY'), ('RUSSEL',8,2014,'SG') SELECT T1.[USER],T1.[MONTH],T1.[YEAR] ,T2.[CTRY] FROM @Table1 AS T1 OUTER APPLY ( SELECT TOP(1) T2.[CTRY] FROM @Table2 AS T2 WHERE T1.[USER] = T2.[USER] AND T1.[YEAR]*100 + T1.[MONTH] >= T2.[YEAR]*100 + T2.[MONTH] ORDER BY T2.[YEAR] DESC,T2.[MONTH] DESC )T2 the result is : USER MONTH YEAR CTRY RUSSEL 1 2014 PH RUSSEL 4 2014 PH RUSSEL 6 2014 MY RUSSEL 8 2014 SG
16 People are following this question.
