0

I have two tables that I want to merge in R based on two conditions, one of them being difference between two days being between -30 and +30 days.

ID  FINAL_AMOUNT    ProcedureDate
10001282    19320   3/22/2017 1:44
10001282    37103   3/23/2017 11:11
10001282    22423   3/22/2017 19:49

ID  ResultDate  Amount 2
10001282    4/3/2017 6:25   0.4
10001282    3/27/2017 5:33  150.99
10001282    3/29/2017 5:50  116.71
10001282    4/5/2017 7:27   150.99
10001282    3/27/2017 5:33  0.4
10001282    3/29/2017 5:50  0.5
10001282    4/3/2017 6:25   150.99
10001282    4/5/2017 7:27   0.4
10001282    4/5/2017 7:27   0.4
10001282    4/5/2017 7:27   150.99
10001282    3/27/2017 5:33  0.4
10001282    3/29/2017 5:50  0.5
10001282    4/3/2017 6:25   150.99
10001282    4/3/2017 6:25   0.4
10001282    3/27/2017 5:33  150.99
10001282    3/29/2017 5:50  116.71

I want a Cartesian product with table 1 being my base table. I am using the below code, but since the day difference is not being calculated correctly I don't get the desired results.

library(sqldf) 

sqldf("SELECT a.*,b.*,(b.ResultDate-a.ProcedureDate)as daydiff from table1 as a left join table2 as b
              on a.ID=b.ID
              and (-30)<=b.ResultDate-a.ProcedureDate<=(30)" )

Thanks

J.Con
  • 4,101
  • 4
  • 36
  • 64
dinesh khemani
  • 23
  • 4
  • If the dates are actually character strings then you can't subtract character strings from each other in R or in SQL. Convert them to Date class first. in R. Also please review [ask] and [mcve]. – G. Grothendieck Jun 23 '17 at 12:01

1 Answers1

0

Have you verified what result you get, when you calculate 

b.ResultDate-a.ProcedureDate

is it only the number of days? First check this, by looking at what's in your daydiff column in the result-set. If not, you might need some casting to get the difference in days.

apart from that you could use the abs() function, to make the resulting difference of b.ResultDate-a.ProcedureDate always positive and have 

abs(b.ResultDate-a.ProcedureDate) <= 30

as argument. Aparat from that it would be easier, if you would have provided a complete example that one could use to check if the given answer is correct described here

Jan
  • 3,825
  • 3
  • 31
  • 51