Solved

# Funtion to retrieve Min Max Dates

Posted on 2011-10-27
491 Views
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?

0
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
0

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.
0

LVL 1

Author Closing Comment

THANK YOU!!!  Works great and FAST!
0

## Featured Post

### Suggested Solutions

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