Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everybody,
I am trying to match dates between two different tables.
I have one table with the following fields:
Inv_dates:
Part_Part,
Inv_date
and second table with :
Cost_cal_date:
Part ,
Cost_cal_date
The data in these tables is , as an example :
1:
23 | 1/1/2013 |
78 | 1/2/2013 |
78 | 25/02/2013 |
88 | 3/3/2013 |
88 | 5/3/2013 |
2:
23 | 31/12/2012 |
23 | 31/01/2013 |
23 | 28/02/2013 |
23 | 31/03/2013 |
23 | 30/04/2013 |
23 | 31/05/2013 |
78 | 31/12/2012 |
78 | 31/01/2013 |
78 | 28/02/2013 |
78 | 31/03/2013 |
78 | 30/04/2013 |
78 | 31/05/2013 |
88 | 31/12/2012 |
88 | 31/01/2013 |
88 | 28/02/2013 |
88 | 31/03/2013 |
88 | 30/04/2013 |
88 | 31/05/2013 |
What I am trying to do is to on the first table, for each line, the most close COST_CAL_DATE from the second table but which is earlier than the date on the line. for example
23 | 1/1/2013 | 31/12/2012 |
78 | 1/2/2013 | 31/01/2013 |
78 | 25/02/2013 | 31/01/2013 |
88 | 3/3/2013 | 28/02/2013 |
88 | 5/3/2013 | 28/02/2013 |
I have tried to first join both tables, with left join and than to read from the same table for a new one with
'where max(COST_CAL_DATE) <= INV_DATE' but it didn't work.
I also tried to work with the INTERVALMATCH function but it didn't work.
I will appreciate any help.
Thank You,
Osher Sassoni.
I'm not sure you can do a max() and bring in ID, Date1, and Date2 in the same step so i found i had to break up the steps. With more thought
//load Date1s
Transform1:
LOAD
[@1] AS [ID],
Date#([@2],'D/M/YYYY') AS [Date1]
FROM [lib://Temp/date1.txt]
(txt, codepage is 1252, no labels, delimiter is '\t', msq);
//join date2s to date1s based on ID
outer join (Transform1)
LOAD
[@1] AS [ID],
Date#([@2],'DD/MM/YYYY') AS [Date2]
FROM [lib://Temp/date2.txt]
(txt, codepage is 1252, no labels, delimiter is '\t', msq);
//Filter out records where Date2 >= Date1
noconcatenate
Transform2:
load
ID,
Date1,
Date2
resident Transform1
where Date2 < Date1;
drop table Transform1;
//Now take the maximum Date2s (per ID) remaining from the filter
Dates:
Load
ID,
Max(Date2) as Date2
resident Transform2
group by ID;
drop table Transform2;
//rejoin the Date1s to the remaining Date2s
outer join (Dates)
LOAD
[@1] AS [ID],
Date#([@2],'D/M/YYYY') AS [Date1]
FROM [lib://Temp/date1.txt]
(txt, codepage is 1252, no labels, delimiter is '\t', msq);
.
was going to say with more thought we could probably make this less convoluted.