Funtion to retrieve Min Max Dates


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?

Who is Participating?
sdstuberConnect With a Mentor Commented:
select   MemberID , ClaimNo ,MIN( DateOfService) over (partition by memberid,claimno) min_dos,
MAX( DateOfService) over (partition by memberid,claimno) max_dos,   Diag_Cd from your_table
so no extra function,  no join

the only indexing you would need is if you have a where clause to apply to the query.
jvoconnellAuthor Commented:
THANK YOU!!!  Works great and FAST!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.