Ordered Analytical Function to get the MIN value

Database
Enthusiast

Ordered Analytical Function to get the MIN value

 
1 REPLY
Enthusiast

Re: Ordered Analytical Function to get the MIN value

I have a very tricky situation that I'll try to explain.  I have a view that has the following data (data is under the text) in it.  Users hitting this data do not have SQL.  They are accessing it via Business Objects.  BO builds a query that allows the user to specify a date range in their query, and here's what they need to be able to do.

If a patient is associated with more than one data source based on the date range (patient 1 is the example below), the patient should only be counted with the first data source.

For example, if they run the query using a fill date between 7/1/2015 and 7/16/2015, patient 1 should show up with data source A, but not B (because it is the second data source).  If they run a query using a fill date between 7/12/2015 and 7/30/2015, patient 1 should show up with data source B, but not C (because it is the second data source).  The results are dynamic based on the date range the user specifies.

We are able to accomplish this using the ROW_NUMBER function, but with the volume of data, millions of rows are returned to the BO server, and then summarized in BO.  Ideally, I want to be able to put something in the view that does a count(distinct...) over (....), but I can't figure out how to get it to work given that we don't know the date range until the user runs the query.

Suggestions would be much appreciated!







Data Source Prescription Patient Fill Date
A 1 1 7/1/2015
A 2 1 7/2/2015
A 3 2 7/2/2015
B 4 1 7/15/2015
B 5 3 7/10/2015
B 6 4 7/12/2015
C 7 1 7/20/2015
C 8 5 7/20/2015
C 9 6

7/21/2015