Running Oracle SQL query over several dates

Within Crystal Reports, I’m using the following query (against an Oracle database) to generate data for a single field in a report:

SELECT SUM(e1.ENT_LOCAL_AMOUNT+e1.ENT_DISCRETIONARY_AMOUNT) AS "Entitlement"
FROM CLAIM_PERIODS cp1
JOIN ENTITLEMENTS e1
  ON cp1.CPE_REFNO=e1.ENT_CPE_REFNO
WHERE e1.ENT_REFNO=(SELECT MAX(to_number(e2.ENT_REFNO)) 
  FROM ENTITLEMENTS e2
  WHERE e1.ENT_CPE_REFNO=e2.ENT_CPE_REFNO
  AND (e2.ENT_START_DATE <= {?HB_As_At_Date} 
  AND e2.ENT_END_DATE > {?HB_As_At_Date})
  AND e2.ENT_CREATED_DATE<={?HB_As_At_Date})
  AND cp1.CPE_CPA_CPY_CODE='HB'

This works fine and returns a single integer value, based on the {?HB_As_At_Date} supplied (The {?} syntax is Crystal’s way of embedding parameter values into SQL). The content of the above query isn’t my issue though – what I want to do is run it repeatedly for several different dates, and have that output be what gets fed through to Crystal for use in the report.

So say I want this query run for every Monday in September, I’d currently run the Crystal report once with a parameter of 07/09/2015, then again for 14/09/2015, etc.

I’d instead like to use my SELECT statement in conjunction with a query that tabulates this as needed – running the above once each per date required. With the output being something like:

Date        Entitlement
07/09/2015  450,000.00
14/09/2015  460,123.00
21/09/2015  465,456.00
28/09/2015  468,789.00

Could someone point me in the right direction in terms of which keywords I should be reading up on here? I’d imagine it’s quite straight-forward to generate a set of dates and run my SQL as a subquery using them, but I’m not sure where to start.

Answer

The only way I can think of without using a stored procedure is by repeating (i.e. copy/paste) your query for each date parameter and then combining them as sub-queries using UNION. Something like this:

SELECT SUM(e1.ENT_LOCAL_AMOUNT+e1.ENT_DISCRETIONARY_AMOUNT) AS "Entitlement"
FROM CLAIM_PERIODS cp1
JOIN ENTITLEMENTS e1
  ON cp1.CPE_REFNO=e1.ENT_CPE_REFNO
WHERE e1.ENT_REFNO=(SELECT MAX(to_number(e2.ENT_REFNO)) 
  FROM ENTITLEMENTS e2
  WHERE e1.ENT_CPE_REFNO=e2.ENT_CPE_REFNO
  AND (e2.ENT_START_DATE <= {?HB_As_At_Date_1} 
  AND e2.ENT_END_DATE > {?HB_As_At_Date_1})
  AND e2.ENT_CREATED_DATE<={?HB_As_At_Date_1})
  AND cp1.CPE_CPA_CPY_CODE='HB'

UNION

SELECT SUM(e1.ENT_LOCAL_AMOUNT+e1.ENT_DISCRETIONARY_AMOUNT) AS "Entitlement"
FROM CLAIM_PERIODS cp1
JOIN ENTITLEMENTS e1
  ON cp1.CPE_REFNO=e1.ENT_CPE_REFNO
WHERE e1.ENT_REFNO=(SELECT MAX(to_number(e2.ENT_REFNO)) 
  FROM ENTITLEMENTS e2
  WHERE e1.ENT_CPE_REFNO=e2.ENT_CPE_REFNO
  AND (e2.ENT_START_DATE <= {?HB_As_At_Date_2} 
  AND e2.ENT_END_DATE > {?HB_As_At_Date_2})
  AND e2.ENT_CREATED_DATE<={?HB_As_At_Date_2})
  AND cp1.CPE_CPA_CPY_CODE='HB'

As for your comment about writing a script for that, I don’t know how you are running your report. But if you have an app/website running it, then you can generate the SQL in the app/website’s language and assign it to the report object before you run it. Or even better, you can generate the SQL, run it, and assign the results to the report object. I do this all the time as I prefer my code to run the queries rather than the report itself, because I follow the layered design pattern in my app. The report will be located in the presentation layer which cannot communicate with the database directly, instead it calls the business/data layer with generates/runs the query and returns the results to the business/presentation layer.