Forum Discussion
checking the success of a program by comparing date fields for person IDs
Hello,
Looking for help on a large data set. Im trying to assess the success of a program. Essentially I want to look at all records for each person ID, check if a column A string is present in a different field, and if that string is present, take the MIN date corresponding to a date field in column B. THEN, grouping by person ID, look at a different column C to see if a string is present, and if so, take a different column D date. And finally, if column D date is greater than column B date, return something (could just be a tally, or 1).
I know this sounds like a mouthful (and is) so Im hoping this makes sense.
3 Replies
Customer Identifier Rx Number Most Recent Bill Date Rx Completed On Primary Plan Name Primary Plan Paid Amount Copay Amount 1234556 99996661 5/5/2022 5/5/2022 RETAIL 1147.09 15 1234556 99996661 5/30/2022 6/6/2022 RETAIL 1147.09 15 1234556 99996661 7/11/2022 7/11/2022 RETAIL 1229.1 15 1234556 99996661 8/3/2022 8/15/2022 RETAIL 1229.1 15 1234556 99996661 9/13/2022 RETAIL 0 0 1234556 9995577 8/26/2022 RETAIL 0 0 1234556 8754 11/9/2022 QSP 0 0 1234556 8754 12/20/2022 RETAIL 0 0 - I added test data for the purposes of the question. Hopefully this will make things clearer. Looking to group by Customer Identifier (only one shown in data set), check if "QSP" in Primary Plan Name, and if so, check all RxCompletedOn dates that would fall after the Most Recent Bill Date associated with the QSP line.
