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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
THANK YOU!!! Works great and FAST!
the only indexing you would need is if you have a where clause to apply to the query.