Following an account for 18 months in a different performance table

[podarum]

Hi,  I have 300 applications that applied within a 12 month window.  Eg. Aug'2015 t Jul'2016 (call these APP_Dates). These are in TABLE A.  I also have TABLE B with a whole bunch of performance data for 3 years starting Sep'2015 all the way up to Jan'2018 (call these PERF_Dates).  I want to find the performance for each application for exactly 18 months from the APP_Date + 1 .  So an application from Aug'2015 I would have their performance info from Sep'2015 to Feb'2017.. and so on for each account.   It is possible that some applications do not have any performance data or some may only have a smaller limited amount (eg. less then 18 months).

  I tried the below but that still gave me everyone.

where intck('month',a.CreationDate,b.Proc_Dt) <= 19

Thanks.

[PGStats · Accepted solution]

You should invert the two dates

where intck('month', b.Proc_Dt, a.CreationDate) <= 19

[SuryaKiran]

Please provide some example data you have and the output you want. We never no how you data looks like unless you show us.

[ballardw]

So, which of a.creationdate and b.Proc_dt is your APP_date? Your PERF_date? Discussing date in one term and the showing code using different variable names doesn't help.

If your all of you b.Proc_dt is before a.creationdate then all of the intck values are negative and you would get the behavior you describe (assumes the data is joined on some other variables that make sense).

You might have to post some example data in the form of data steps and the actual code you ran for a good diagnostic.

Were there any interesting notes in the log?

[podarum]

Thanks everyone for your help.    I used this and it works fine:

where intnx ('month',app_date,1) <=Perf_date <=intnx('month',app_date,19)