your code only catches the last date with ER ='Y' I want the output in the exact same format like the original table but adding a column counting the previous 6 month ER visit ... View more

i want the ER_count on the encounters level, not ID ... View more

any inputs? ... View more

ncounter Num ID Date ER 1327819 1001 1/2/2017 Y 4390843 1001 2/8/2017 Y 239480 1001 5/4/2017 N 43958345 1001 1/7/2018 N 390485903 1001 5/4/2018 N 323465354 1002 2/5/2017 Y 54332432 1002 6/8/2017 Y 7657657567 1002 9/8/2017 N 768698 1003 6/8/2017 Y 7567465 1003 8/9/2017 Y 23465758 1003 9/5/2017 N 35134346674 1003 10/9/2018 N 34575697 1003 11/8/2018 Y 3524341 1003 12/8/2018 N   In this table, the desire output would be (ER_Count not counting current one): Encounter Num ID Date ER ER_Count 1327819 1001 1/2/2017 Y 0 4390843 1001 2/8/2017 Y 1 239480 1001 5/4/2017 N 2 43958345 1001 1/7/2018 N 0 390485903 1001 5/4/2018 N 0 323465354 1002 2/5/2017 Y 0 54332432 1002 6/8/2017 Y 1 7657657567 1002 9/8/2017 N 1 768698 1003 6/8/2017 Y 0 7567465 1003 8/9/2017 Y 1 23465758 1003 9/5/2017 N 2 35134346674 1003 10/9/2018 N 0 34575697 1003 11/8/2018 Y 0 3524341 1003 12/8/2018 N 1   ... View more

This is not what I wanted.  What I wanted is for every encounter Number, there is a date. Check back 6 month from that date by ID and see if there is ER visit which is Y. ... View more

Encounter Num ID Date ER 1327819 1001 1/2/2017 Y 4390843 1001 2/8/2017 Y 239480 1001 5/4/2017 N 43958345 1001 1/7/2018 N 390485903 1001 5/4/2018 N 323465354 1002 2/5/2017 Y 54332432 1002 6/8/2017 Y 7657657567 1002 9/8/2017 N 768698 1003 6/8/2017 Y 7567465 1003 8/9/2017 Y 23465758 1003 9/5/2017 N 35134346674 1003 10/9/2018 N 34575697 1003 11/8/2018 Y 3524341 1003 12/8/2018 N ... View more