• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 503
  • Last Modified:

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?

  • 2
1 Solution
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!

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now