Mapping Two Tables Based on Date logic

[anuradhaa]

I'm Using Two Sals to get Data from Two different data sources,

One Table i have,

DateStarted, Name , Rate  ( Names are repeating.)

Another Table i have,

Name,Cost, Date (Here also Names are repeating.)

I need to get ,

Name, Rate , and Cost and Group It by DateStartedMonth in Pivot Table.

Note-

Logic is Date of second table need to map with Latest Started Date Passed,

Say Name1 has below started Dates,

2015/05/26

2015/08/30

2015/11/23

And Table two Dates for Name1 as

2015/09/30

Then That record has to map with 2015/08/30 Date.

Thanks,

[anuradhaa]

Most of the things are correct. but It is wrong when dates are like below

  Table1

DATE_Started	Name	LEASE_RATE	Customer
01/01/16	Name1	1025	Cus A
01/11/16	Name1	1000	Cus A
02/19/16	Name1	1551	Cus A
04/11/16	Name1	789	Cus A

Table 2

Nmae	year	month	COST
Name1	2013	10	263.88

Out put

DATE_Started	Name	EndDate	Date	LEASE_RATE	COST	month	year
01/01/16	Name1	4/15/2016	10/1/2013	1025	263.88	10	2013
01/11/16	Name1	4/15/2016	10/1/2013	1000	263.88	10	2013
02/19/16	Name1	4/15/2016	10/1/2013	1551	263.88	10	2013
04/11/16	Name1	4/15/2016	10/1/2013	789	263.88	10	2013

[sunny_talwar]

In this case, I would do just a straight join between the two tables. But this seems like a different requirement now. How would this change if you have two entries in Table 2?

Name, year, month, COST

Name1, 2013, 10, 263.88

Name1, 2014, 02, 270.21 -> Randomly adding something

What is going to be the output now?

[anuradhaa]

Then,

we don't need to join that record, Since those dates doesn't inside date range in SQL1.

Output

SHIP_DATE	Name	EndDate	cleaning_cost	Date	LEASE_RATE	repair_amount	repair_month	repair_year
01/01/16	Name1	4/15/2016	0		1025
01/11/16	Name1	4/15/2016	0		1025
02/19/16	Name1	4/15/2016	0		1025
04/11/16	Name1	4/15/2016	0		1025

[sunny_talwar]

So I am assuming that a single database will have different Name1 and based on different Names you are going to decide which type of join is needed? I guess it would be helpful to look at a more comprehensive sample which include both these cases combined with the expected output.

[anuradhaa]

Hi Sunny,

I think Most of the Things are correct , but i feel interval match doesn't apply when i'm loading data. I have get excel out put and load that excel to my script and it works fine, but the interval match doesn't work when it is loading from DataBase.

See my Script from second table,

r:

LOAD *,

Date(MakeDate(r_year, r_month)) as Date;

select

  r.C + ' ' + cast(r.CNum as varchar(6)) as 'RR',

  month(h.rdate) as 'r_month',

  year(h.rdate) as 'r_year'

from Rr r

Left Join (r)

IntervalMatch (Date, RR)

LOAD *

Resident Ss;

Right Join (r)

LOAD *

Resident Ss;

DROP Table Ss;

[sunny_talwar]

I don't really follow your script here. Where exactly is the interval match taking place? Interval Match is used when you are linking a date range to a particular date. Where is your date range?

Left Join (r)

IntervalMatch (Date, RR)

LOAD StartDate,

          EndDate,

          RR

Resident Ss;