Loop Query Through Date Range

Question

Hi all,

I'm currently running a SQL script within Excel VBA.

VBA loops through this script for every date that appears in a table. The dates are contiguous, but the start and end dates change based on other variables in the spreadsheet.

This works great but is however very slow and it might query the database 1000 times to pull back a fairly simple table.

What I'm trying to do is move this loop into the SQL script to speed things up a bit but I don't really know where to start. Ideally the only variables would be the start date and the end date. SQL would then build a table with every date in between and run the existing query against every date therein.

Could someone give me a starting point please? Even if its just some ideas of keywords to search for.

Thanks,

Dan.

Answer 1

Thats a good move, but need to think about processing data sets rather to procedural.

For quick help , post DDL of the tables and sample data and inputs and expected output. 

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 2

Here are my two Cents, please see this link:

How to import data from Excel to SQL Server

and

Exporting SQL Server Data to Excel

---

The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd


My blog