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|