Need to Join two tables based on Nearest Date

Question

Need to Join two tables based on Nearest Date

shannon.proctor

Say Hey Kid

Points: 707
More actions
September 24, 2014 at 3:22 pm

#296829

I have two tables where the key of one doesn't not perfectly match the primary table. I have three fields Material, Plant and Date in a Costing table. The date in the costing table does not match a date in my primary transaction table. I need to create a join based on a transaction date >= the nearest costing date. Any help is appreciated.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Any solution would be slow, you should really fix this issue.

To solve your problem as it is, you could try with a correlated subquery using CROSS APPLY. For better assistance, read the article linked in my signature.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ScottPletcher SSC Guru Points: 101105 More actions · Answer 2

About the best you can do is to cluster the Costing table by date. That could help significantly when there are a limited number of lookups. For large numbers of lookups, it's only a minor benefit, but you can't do much more anyway.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

sqld-_-ba SSCarpal Tunnel Points: 4365 More actions · Answer 3

shannon, you need to define 'nearest' zone - is it seconds or hours or days ? does near mean both later and earlier in time ?

then, change the 0.001 in below code to your suitable zone:

select * from primary p

join costing c on p.dt >= c.dt - 0.001 -- or '+'

shannon.proctor Say Hey Kid Points: 707 More actions · Answer 4

shannon.proctor

Say Hey Kid

Points: 707

September 25, 2014 at 7:21 am

#1748147

No time. Date format is yyyymmdd.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

shannon.proctor (9/24/2014)
I have two tables where the key of one doesn't not perfectly match the primary table. I have three fields Material, Plant and Date in a Costing table. The date in the costing table does not match a date in my primary transaction table. I need to create a join based on a transaction date >= the nearest costing date. Any help is appreciated.

You could do it with SELECT TOP... in a CROSS APPLY block. If you can post up a sample data script for these two tables, someone will show you how it's done.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

shannon.proctor Say Hey Kid Points: 707 More actions · Answer 6

Data from Cost Table - tbl_Stag_Cost

I_MatlI_PlantI_Cal_YrM_Totl_COGSM_Fixed_COGSM_Var_COGSI_CurrD_Costing

95583014120111261.19128.231132.96USD20110101

95583014120111276.85135.161141.69USD20110201

95583014120111305.64135.161170.48USD20111101

95583014120121196.07106.901089.17USD20120101

95583014120131153.2680.241073.02USD20130101

95583014120131153.2680.241073.02USD20130701

95583014120141162.6379.131083.50USD20140101

Data from Primary Transaction Table - tbl_Stag_Trans

I_Bill_NbrI_Bill_ItemD_BillI_MatlI_PlantI_Doc_CurrM_Spec_PriceI_Curr

0091032577420120104955830141USD4096.51USD

0091032565320120104955830141USD13971.27USD

0091017091120111021955830141USD4236.72USD

0091017399420111024955830141USD4096.51USD

0090966415420110228955830141USD2048.26USD

0090967913420110307955830141USD4096.51USD

0090967915320110307955830141USD4096.51USD

0090968213420110308955830141USD4126.99USD

0090968715420110310955830141USD3762.76USD

0090969390420110315955830141USD2048.26USD

0090969446420110315955830141USD4096.51USD

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 7

shannon.proctor (9/25/2014)
No time. Date format is yyyymmdd.

If the most granular you can get is day, what happens when there are multiple possible matches?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

shannon.proctor (9/25/2014)
Data from Cost Table - tbl_Stag_Cost
I_MatlI_PlantI_Cal_YrM_Totl_COGSM_Fixed_COGSM_Var_COGSI_CurrD_Costing
95583014120111261.19128.231132.96USD20110101
95583014120111276.85135.161141.69USD20110201
95583014120111305.64135.161170.48USD20111101
95583014120121196.07106.901089.17USD20120101
95583014120131153.2680.241073.02USD20130101
95583014120131153.2680.241073.02USD20130701
95583014120141162.6379.131083.50USD20140101
Data from Primary Transaction Table - tbl_Stag_Trans
I_Bill_NbrI_Bill_ItemD_BillI_MatlI_PlantI_Doc_CurrM_Spec_PriceI_Curr
0091032577420120104955830141USD4096.51USD
0091032565320120104955830141USD13971.27USD
0091017091120111021955830141USD4236.72USD
0091017399420111024955830141USD4096.51USD
0090966415420110228955830141USD2048.26USD
0090967913420110307955830141USD4096.51USD
0090967915320110307955830141USD4096.51USD
0090968213420110308955830141USD4126.99USD
0090968715420110310955830141USD3762.76USD
0090969390420110315955830141USD2048.26USD
0090969446420110315955830141USD4096.51USD

Thanks, but...you're looking for someone to turn this into a CREATE TABLE... plus INSERTs so they can code against it? Why don't you do it?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

shannon.proctor Say Hey Kid Points: 707 More actions · Answer 9

Sorry. I thought the request was to post the sample data. I can do that.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Thanks. There are some excellent hints to make sample data generation quick and easy in this article[/url].

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

shannon.proctor Say Hey Kid Points: 707 More actions · Answer 11

-- SAMPLE COST DATA

DECLARE @COST TABLE (I_Matl varchar(5), I_Plant varchar(4), D_Costing int, M_Totl_Cost float)

INSERT INTO @COST VALUES ('95583','0141', 20110101,123.29)

INSERT INTO @COST VALUES ('95583','0141', 20110201,191.33)

INSERT INTO @COST VALUES ('95583','0141', 20111101,178.45)

INSERT INTO @COST VALUES ('95583','0141', 20120101,125.26)

INSERT INTO @COST VALUES ('95583','0141', 20130101,111.13)

INSERT INTO @COST VALUES ('95583','0141', 20130201,123.22)

INSERT INTO @COST VALUES ('95583','0141', 20130701,129.33)

INSERT INTO @COST VALUES ('95583','0141', 20140101,188.34)

-- SAMPLE TRANSACTION DATA

DECLARE @TRANS TABLE (I_Matl varchar(5), I_Plant varchar(4), D_Bill int, Net_Sales float)

INSERT INTO @TRANS VALUES ('95583','0141', 20110105,4096.51)

INSERT INTO @TRANS VALUES ('95583','0141', 20110311,3971.27)

INSERT INTO @TRANS VALUES ('95583','0141', 20111112,4236.72)

INSERT INTO @TRANS VALUES ('95583','0141', 20120310,2048.26)

INSERT INTO @TRANS VALUES ('95583','0141', 20130401,3562.99)

INSERT INTO @TRANS VALUES ('95583','0141', 20130623,2223.22)

INSERT INTO @TRANS VALUES ('95583','0141', 20130809,4569.33)

INSERT INTO @TRANS VALUES ('95583','0141', 20140515,5188.34)

INSERT INTO @TRANS VALUES ('95583','0141', 20140525,8188.34)

shannon.proctor Say Hey Kid Points: 707 More actions · Answer 12

shannon.proctor

Say Hey Kid

Points: 707

September 25, 2014 at 8:52 am

#1748210

Accidentally posted twice

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

What output would you like to see, Shannon?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 14

I'm guessing something like this:

SELECT t.*, x.*

FROM @TRANS t

CROSS APPLY (

SELECT TOP 1 c.D_Costing, c.M_Totl_Cost

FROM @COST c

WHERE c.I_Matl = t.I_Matl

AND c.I_Plant = t.I_Plant

AND c.D_Costing < t.D_Bill

ORDER BY c.D_Costing DESC

) x

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden