MYSQL - Would like to iterate through dates located in 1 table, and count how many records in another

Question

I have 2 tables. The DateTable is a table with just dates. RecordTable is a table with 40million unique records.

In RecordTable, each record has a StartDate and an EndDate.

I want to iterate through DateTable, and COUNT how many records in RecordTable WHERE the date from DateTable is BETWEEN StartDate and EndDate.

Example - DateTable has the following 2 records: 2014-07-27 2014-07-28

I first want to know the number of records in RecordTable that 2014-07-27 is BETWEEN StartDate and EndDate.

I then want the next line to show me the the number of records in RecordTable that 2014-07-28 is BETWEEN StartDate and EndDate.

Is this a loop? or is their a way to do in a Query?

Answer 1

It's two seperate queries. And you have the right idea for both. You're going to have to run your data sets seperately, then use whatever you'd like on the browser side, to display the results.

Answer 2

If I understand your question correctly, you can join DateTable to RecordTable and simply do a count.

SELECT DateTable.TheDate, COUNT(*)
INNER JOIN RecordTable ON
  DateTable.TheDate BETWEEN RecordTable.StartDate AND RecordTable.EndDate
GROUP BY DateTable.TheDate

Note that this will omit any DateTable rows that don't match up in RecordTable.

If you want to show a zero count when the date in DateTable doesn't match any rows from RecordTable, you'll need to left join and count based on a RecordTable column value (this is hard to explain, but it's based on the fact that COUNT ignores nulls):

SELECT DateTable.TheDate, COUNT(RecordTable.StartDate)
LEFT JOIN RecordTable ON
  DateTable.TheDate BETWEEN RecordTable.StartDate AND RecordTable.EndDate
GROUP BY DateTable.TheDate