Near match join - SQL Server Forums

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)

Near match join

Author

Topic

SQLServantMCW
Starting Member

2 Posts

Posted - 2015-01-12 : 11:27:03

I’m building a data mart in SQL Server 2012 Enterprise Edition. The mart holds performance data for a group of workers for each quarter over a number of years. There are many different types of tasks the workers perform and I compute the median hours to complete each task type over every quarter and store the results in a reference table. The idea is to compare each worker’s actual task completion time to the median time for the corresponding quarter and show the difference. Including a specific quarter in the comparison is important because these workflows change significantly over time.

However, in certain scenarios, a reference will not exist for every quarter leaving me with nothing to use for comparison. In this case, I need to use the reference value for the nearest available quarter.

My strategy for the near match involves two separate set operations, one to find the closest match before the missing quarter and another to find the closest match after the missing quarter. However, the outer apply I wrote to find the closest earlier quarter doesn't work and I’m stuck. Any insight you can share is greatly appreciated.

The first step in my proposed solution (finding the closest earlier quarter) is illustrated in the simplified sample code below. Temp table #Perf holds performance data and #Ref holds the reference data.

Create Table #Perf (
BKey Char(1)
,QtrKey int
,Val float
)

Insert into #Perf Values
('A',1,10)
,('B',2,20)
,('C',3,20)
,('D',4,20)
,('E',5,30)
,('F',6,80)
,('G',7,100)

Create Table #Ref (
BKey Char(1)
,QtrKey int
,Val float
)

Insert into #Ref Values
('A',1,10)
--,('B',2,20) These represent missing ref data
--,('C',3,20)
--,('D',4,20)
,('E',5,30)
,('F',6,80)
,('G',7,100)

/* Failed attempt to find closest earlier qtr */
Select
p.*
,oa1.CLTQtr
from #Perf p
outer apply (
Select
BKey
,Max(QtrKey) CEQtr ---- closest earlier qtr
from #Ref r
where 1=1
and r.BKey = p.BKey
and r.QtrKey < p.QtrKey
group by
BKey ) oa1

Drop table #Perf
Drop table #Ref

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-01-12 : 12:11:33

Try getting rid of BKey in the APPLY:


SELECT *
FROM #Perf P
OUTER APPLY
(
	SELECT TOP (1) Val AS RefVal
	FROM #Ref R
	WHERE R.QtrKey <= P.QtrKey
	ORDER BY R.QtrKey DESC
) D;

SQLServantMCW
Starting Member

2 Posts

Posted - 2015-01-12 : 12:50:56

The BKey column defines the type of task... you can't lookup up the proper reference data without it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources