Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

can't figure the logic, could use help

Status
Not open for further replies.

KDFischer

Programmer
Jul 26, 2011
3
US
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
 
What version of the SQL Server database engine are you using. If you are not sure, please run this is a query engine and post the output here:

Code:
Select @@Version


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Microsoft SQL Server 2005 - 9.00.4317.00 (X64) Jan 28 2011 13:37:57 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top