I'm trying to find the best way to accomplish this in a stored procedure. Best I'm figuring is using a cursor and/or temp tables. Seeking ideas.
Here's the problem. I have a very large table. For simplicity, the table has company, member number, an RX number, a date, an original code and current code columns. Such as:
Co Date Mem RX Orig Current
ABC 01/01/2011 1234 897654 R
ABC 01/02/2011 1234 897654 H
ABC 01/03/2011 1234 897654 A H
ABC 01/04/2011 1234 897654 N H
ABC 01/05/2011 1234 897654 R H
ABC 01/06/2011 1234 897654 A
I need to measure the time between the dates of an R in Orig column and an A in ither Orig or Current. In this case, I have two time spans for this single member, and of course, a member could have multiple RX and there are definitely multiple members.
Ideally I want two rows returned:
ABC 2 days
ABC 1 day
Actually, what i really want is a summary count of 0-30 days, 30-60 days, 61-90 days, 90 - 120 days, and 121+ Days. In this case, just for this one member, it'd be:
CO 0-30 31-60 61-90 91-120 120+
ABC 2 0 0 0 0
DEF etc
Any thoughts? Right now all I've got is to query for R's in original column, cursor through querying each RX and all transactions and sequentially processing.
thanks
Here's the problem. I have a very large table. For simplicity, the table has company, member number, an RX number, a date, an original code and current code columns. Such as:
Co Date Mem RX Orig Current
ABC 01/01/2011 1234 897654 R
ABC 01/02/2011 1234 897654 H
ABC 01/03/2011 1234 897654 A H
ABC 01/04/2011 1234 897654 N H
ABC 01/05/2011 1234 897654 R H
ABC 01/06/2011 1234 897654 A
I need to measure the time between the dates of an R in Orig column and an A in ither Orig or Current. In this case, I have two time spans for this single member, and of course, a member could have multiple RX and there are definitely multiple members.
Ideally I want two rows returned:
ABC 2 days
ABC 1 day
Actually, what i really want is a summary count of 0-30 days, 30-60 days, 61-90 days, 90 - 120 days, and 121+ Days. In this case, just for this one member, it'd be:
CO 0-30 31-60 61-90 91-120 120+
ABC 2 0 0 0 0
DEF etc
Any thoughts? Right now all I've got is to query for R's in original column, cursor through querying each RX and all transactions and sequentially processing.
thanks