Complex logic to do the joining of 2 tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Complex logic to do the joining of 2 tables

Author

Topic

selva.v.kumar
Starting Member

1 Post

Posted - 2014-07-22 : 23:54:08

Hi,
There are two tables. One is the source table and the other is lookup(crosswalk) table. We need to do the join between source table column1 with lookup table column1 and display/take the column2 of lookup table.

My concern is, there is some complex logic involved to do the join(lookup) between these 2 columns.
Both are date columns. Source column has some 40 date values but lookup table has some 4 date values only. We need to join 40 date columns with 4 date columns based on few conditions.

Here is the table structure and data in source table (#Employee).

Create table #Employee
(
EligibleDate date)
insert into #Employee
Select '2011/07/01' UNION
Select '2011/08/01' UNION
Select '2011/08/17' UNION
Select '2011/09/01' UNION
Select '2011/10/01' UNION
Select '2011/11/01' UNION
Select '2011/12/01' UNION
Select '2012/01/01' UNION
Select '2012/02/01' UNION
Select '2012/03/01' UNION
Select '2012/03/19' UNION
Select '2012/04/01' UNION
Select '2012/05/01' UNION
Select '2012/05/25' UNION
Select '2012/06/26' UNION
Select '2012/07/01' UNION
Select '2012/08/01' UNION
Select '2012/09/01' UNION
Select '2012/09/30' UNION
Select '2012/10/01' UNION
Select '2012/11/01' UNION
Select '2012/12/01' UNION
Select '2013/01/01' UNION
Select '2013/02/01' UNION
Select '2013/03/01' UNION
Select '2013/04/01' UNION
Select '2013/05/01' UNION
Select '2013/06/01' UNION
Select '2013/07/01' UNION
Select '2013/08/01' UNION
Select '2013/09/01' UNION
Select '2013/10/01' UNION
Select '2013/11/01' UNION
Select '2013/12/01' UNION
Select '2014/01/01' UNION
Select '2014/02/01' UNION
Select '2014/03/01' UNION
Select '2014/04/01' UNION
Select '2014/05/01' UNION
Select '2014/06/01'

Here is the table structure and data in lookup table (#lookupEmployee)
Create table #lookupEmployee
(
Periodstartdate date,
Plandesc varchar(3))
insert into #lookupEmployee
select'2011-07-01','ABC' union
select'2012-05-25','DEF' union
select'2012-06-26','GHI' union
select'2012-09-30','JKL'
Basically what it means is column 'Plandesc' value starts from that particular date to till the next date value. Eg:'ABC' value start from 2011-07-01 to till '2012-05-24' because from '2012-05-25', new plan DEF' starts. Similarly this DEF value starts from 2012-05-25' to '2012-06-25' as from '2012-06-26', the new plan 'GHI' starts. This goes till 2012-09-29'. From 2012-09-30, plan 'JKL' starts and any date after this date has 'JKL' only.

Here is the output we want after the lookup or joining is done
SELECT * FROM #Finaloutput
---DROP TABLE #Finaloutput
Create table #Finaloutput
(
EligibleDate date,
Plandesc varchar(3))
insert into #Finaloutput
Select '2011/07/01' ,'ABC' union
Select '2011/08/01' ,'ABC' union
Select '2011/08/17' ,'ABC' union
Select '2011/09/01' ,'ABC' union
Select '2011/10/01' ,'ABC' union
Select '2011/11/01' ,'ABC' union
Select '2011/12/01' ,'ABC' union
Select '2012/01/01' ,'ABC' union
Select '2012/02/01' ,'ABC' union
Select '2012/03/01' ,'ABC' union
Select '2012/03/19' ,'ABC' union
Select '2012/04/01' ,'ABC' union
Select '2012/05/01' ,'ABC' union
Select '2012/05/25' ,'DEF' union
Select '2012/06/26' ,'GHI' union
Select '2012/07/01' ,'GHI' union
Select '2012/08/01' ,'GHI' union
Select '2012/09/01' ,'GHI' union
Select '2012/09/30' ,'JKL' union
Select '2012/10/01' ,'JKL' union
Select '2012/11/01' ,'JKL' union
Select '2012/12/01' ,'JKL' union
Select '2013/01/01' ,'JKL' union
Select '2013/02/01' ,'JKL' union
Select '2013/03/01' ,'JKL' union
Select '2013/04/01' ,'JKL' union
Select '2013/05/01' ,'JKL' union
Select '2013/06/01' ,'JKL' union
Select '2013/07/01' ,'JKL' union
Select '2013/08/01' ,'JKL' union
Select '2013/09/01' ,'JKL' union
Select '2013/10/01' ,'JKL' union
Select '2013/11/01' ,'JKL' union
Select '2013/12/01' ,'JKL' union
Select '2014/01/01' ,'JKL' union
Select '2014/02/01' ,'JKL' union
Select '2014/03/01' ,'JKL' union
Select '2014/04/01' ,'JKL' union
Select '2014/05/01' ,'JKL' union
Select '2014/06/01' ,'JKL'
Can you please help me to get the code for getting the output like this ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-23 : 03:40:48

[code]SELECT e.EligibleDate,
f.PlanDesc
FROM #Employee AS e
OUTER APPLY (
SELECT TOP(1) l.PlanDesc
FROM #LookupEmployee AS l
WHERE l.PeriodStartDate <= e.EligibleDate
ORDER BY l.PeriodStartDate DESC
) AS f;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources