Funtion to retrieve Min Max Dates

Posted on 2011-10-27
Last Modified: 2013-12-07

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?

Question by:jvoconnell
    LVL 73

    Accepted 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
    LVL 73

    Expert Comment

    so no extra function,  no join

    the only indexing you would need is if you have a where clause to apply to the query.
    LVL 1

    Author Closing Comment

    THANK YOU!!!  Works great and FAST!

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now