question

janrusselcalachan avatar image
janrusselcalachan 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
sqljoin
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
sabinweb avatar image
sabinweb answered
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
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Works perfectly! Thank you!
0 Likes 0 ·
Great! You're welcome
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.