Link to home
Start Free TrialLog in
Avatar of jvoconnell
jvoconnell

asked on

Funtion to retrieve Min Max Dates

Experts,

I will overly simplify the structure of the table I’m working with.
Lets say I have a table that looks like….

MemberID                    ClaimNo                     DateOfService                      Diag_Cd

123            XYZ            8/1/11            750      
123            XYZ            8/1/11            528      
123            XYZ            8/4/11            775      
123            XYZ            8/2/11            776
456            ABC            7/11/11                         951
456            ABC            7/19/11                          958      
789            DEF            6/30/11                          852


I need to return the MemberID, ClaimNo, Diag_Cd and the Min/Max DateOfService that exists for a claim number

So, the desired output is…


MemberID    ClaimNo   Min_DOS      Max_DOS            Diag_Cd

123            XYZ      8/1/11      8/4/11      750      
123            XYZ      8/1/11      8/4/11      528      
123            XYZ      8/1/11      8/4/11      775      
123            XYZ      8/1/11      8/4/11      776
456            ABC      7/11/11      7/19/11      951
456            ABC      7/11/11      7/19/11      958      
789            DEF      6/30/11      6/30/11      852


To ensure that I get the correct Min and Max date for a claim, I need to use both the MemberID AND the Claim Number.

What is the best approach here?

Should I create a function that I pass MemberID and ClaimNo into so I can get the Min/Max DOS?

Should I extract all other data and use a temp table where I pull out MemID, ClamNo, Min/Max DOS and then join back to update those two fields?

The table has @50 million rows. Any indexing suggestions?

ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sean Stuber
Sean Stuber

so no extra function,  no join

the only indexing you would need is if you have a where clause to apply to the query.
Avatar of jvoconnell

ASKER

THANK YOU!!!  Works great and FAST!