I have an issue with efficiency. I have a database that stores all monthly transactions in a separate table. The table structure is identical but the table name ends in the month number i.e. Reports1 for January’s information and Reports12 for December’s.

I want to be able to select data from a range of dates that may span several months. I have successfully created a report that does a union all for all twelve tables but the system then needs to read all the data from all twelve tables. If I want a report for a one-week period and the week spans 2 months, I would be reading 12 months of data. I am selecting all data from the table where the date is between the variables I have setup. But I want a variable that only selects the desired table…

Help

Connecting to a SQL database. Most of my customers are satisfied with a monthly summary and that was not hard to create but one wants to allow this report to pick a starting date and an ending date and that information can be in any one of those 12 tables that were picked. currently i have this script:

SELECT * FROM REPORTSQL1 where Reportdate between {?startdate} and {?enddate}

UNION ALL SELECT * FROM REPORTSQL2 where Reportdate between {?startdate} and {?enddate}

UNION ALL SELECT * FROM REPORTSQL3 where Reportdate between {?startdate} and {?enddate}

UNION ALL SELECT * FROM REPORTSQL4 where Reportdate between {?startdate} and {?enddate}

UNION ALL SELECT * FROM REPORTSQL5 where Reportdate between {?startdate} and {?enddate}

UNION ALL SELECT * FROM REPORTSQL6 where Reportdate between {?startdate} and {?enddate}

UNION ALL SELECT * FROM REPORTSQL7 where Reportdate between {?startdate} and {?enddate}

UNION ALL SELECT * FROM REPORTSQL8 where Reportdate between {?startdate} and {?enddate}

UNION ALL SELECT * FROM REPORTSQL9 where Reportdate between {?startdate} and {?enddate}

UNION ALL SELECT * FROM REPORTSQL10 where Reportdate between {?startdate} and {?enddate}

UNION ALL SELECT * FROM REPORTSQL11 where Reportdate between {?startdate} and {?enddate}

UNION ALL SELECT * FROM REPORTSQL12 where Reportdate between {?startdate} and {?enddate}