joining on same variable date n key but different values

[KachiM]

Note questions raised by Patrick.

When ID and Amount is the Key for the search, why are you worried about the difference in Dates as you seem to compare only TWO sources. Please confirm whether it is only two sources.  If there are more than two sources, then add a column to the Sample Data set for the sourceID. Then the solution becomes easy with the Key having ID, Amount and SourceID.

[Patrick]

---

@MV04 wrote:

Thanks for the response Patrik,

Both datasets are from different sources one may be running on weekdays and getting dates as the process day and if it is a weekend or a bank holiday then dates are skipping those  whereas other may be running on from tues- saturday something like that hence they are different so id and amount is same but for some records when ID and amount is same date is same as well whereas for others id and amont is same but date is different 

---

You give a high level description of the problem here. Where you need to be at before even starting to write code is a description of the solution (=a description of the logic which allows to match records from the two sources without any ambiguity).

From your description right now I'm not sure if such a join is possible but there might be other data elements in your tables which could resolve this. So how - just by "eyeballing" the data - would you decide if records with different dates belong to the same "transaction". 

Also: Is "amount" a valid key at all? Are these end-of-day amounts - but then why could they be always the same on different dates if the cut-off date in the two systems is different. ....which gets me to the question: What are these dates you're using right now? Rundates, cutoff dates, something else? And would there be eventually other date columns in your data which match?