Dynamic Query View


Dynamic Query View

I know I've asked this one before, but it's coming up again and I need to dig around a bit.  We currently get a feed from an online survey.  A customer can take a survey any number of times throughout the year to update their health information.  Each time they take the survey, it tracks the date and time they took the survey.  That's the easy part.

We then are using Business Objects to do reporting on their responses to the questions of the survey.  Here is the trick.  When the user goes to query the survey data, they can enter any date range they want, and we have to return the value of the first survey completed within the date range they specified.  This allows us to prevent double counting of survey responses within a time window.  I know how to do this if we had some fixed time windows involved (i.e. beginning and ending of each month).  However, I'm not so sure how I would implement this in a view definition given that we are dependent on whatever date range the user wants to enter.  Here's an example of what the query would look like with the dates completed.

select csr_id,srv_rsp

from srv_rsp_tbl

where (csr_id,rsp_dte) in

(select csr_id,min(rsp_dte)

 from   srv_rsp_tbl

 where rsp_dte between '2011-01-01' and '2011-10-15')

Any thoughts would be much appreciated!


Re: Dynamic Query View

You can create macro and try and pass dates as parameters