StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><strong>Test Data</strong></p> <pre><code>create table #LoanTable ( ID int not null, RegionCode nvarchar(50) not null, LoanDate datetime not null ); insert into #LoanTable values (1,'5','10/01/2014'), (2,'5','10/18/2014'), (3,'5','10/02/2014'), (4,'3','04/11/2014'), (5,'3','04/05/2014'), (6,'4A','01/09/2014'), (7,'4A','01/05/2014') create table #LoanDetailsTable ( ID int not null, LoanAmount INT not null, LoanDate datetime not null ); insert into #LoanDetailsTable values (1,5000,'10/15/2014'), (2,1000,'10/11/2014'), (3,2000,'10/09/2014'), (4,1500,'04/13/2014'), (5,5000,'04/17/2014'), (6,500,'01/19/2014'), (7,2500,'01/15/2014') </code></pre> <p><strong>Query</strong></p> <pre><code>;With RegCode AS ( SELECT RegionCode, MAX(MONTH(LoanDate)) [Month] FROM #LoanTable GROUP BY RegionCode ) SELECT LDT.* , RC.RegionCode FROM #LoanDetailsTable LDT INNER JOIN RegCode RC ON MONTH(LDT.LoanDate) = RC.[Month] </code></pre> <p><strong>Results</strong></p> <pre><code>ID LoanAmount LoanDate RegionCode 1 5000 2014-10-15 00:00:00.000 5 2 1000 2014-10-11 00:00:00.000 5 3 2000 2014-10-09 00:00:00.000 5 4 1500 2014-04-13 00:00:00.000 3 5 5000 2014-04-17 00:00:00.000 3 6 500 2014-01-19 00:00:00.000 4A 7 2500 2014-01-15 00:00:00.000 4A </code></pre> <p>Using CTE extract the Month part of the date along with Region Code associated with it, then join it with you data table on Month of the loan date and extracted month in cte and get the Region code whatever it is at that time. happy days :)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload