MySQL Counting Active Records Each Day in Date Range

Question

I have a MySQL table for products with these columns of importance.

-id

-arrived

-released

Using the arrived and released columns, I would like to loop through the days of the month, say '2017-01-01' through '2017-01-30' and check how many products were "active". Meaning it would in essence loop each day for the date range and check how many products were active for '2017-01-01' and so on for each day in the date range.

The code I use to check for an individual day such as '2017-01-01' is as follows.

SELECT COUNT(id)
FROM products_booking
WHERE (released IS NULL OR released >= '2017-01-01') AND (arrived <= '2017-01-01')

This will return the number of "active" products on this day.

I would like however to perform this on a specified date range for a report that will be able to count the number of "active" products for every day in the specified date range.

I have tried the following to no avail and it seems as if I have gone down the wrong path with this approach.

SELECT MonthDate.Date, COALESCE(COUNT(IB.id), 0) AS Total
FROM (
    SELECT 1 AS Date UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5 UNION ALL
    SELECT 6 UNION ALL
    SELECT 7 UNION ALL
    SELECT 8 UNION ALL
    SELECT 9 UNION ALL
    SELECT 10 UNION ALL
    SELECT 11 UNION ALL
    SELECT 12 UNION ALL
    SELECT 13 UNION ALL
    SELECT 14 UNION ALL
    SELECT 15 UNION ALL
    SELECT 16 UNION ALL
    SELECT 17 UNION ALL
    SELECT 18 UNION ALL
    SELECT 19 UNION ALL
    SELECT 20 UNION ALL
    SELECT 21 UNION ALL
    SELECT 22 UNION ALL
    SELECT 23 UNION ALL
    SELECT 24 UNION ALL
    SELECT 25 UNION ALL
    SELECT 26 UNION ALL
    SELECT 27 UNION ALL
    SELECT 28 UNION ALL
    SELECT 29 UNION ALL
    SELECT 30 UNION ALL
    SELECT 31) AS MonthDate
LEFT JOIN products AS IB
ON MonthDate.Date >= DAY(IB.released) AND MonthDate.Date <= DAY(IB.arrived)
WHERE MonthDate.Date <= DAY(LAST_DAY('2017-01-30'))
GROUP BY MonthDate.Date

I also tried this, but it is returning '1' for each day.

SELECT COUNT(*) cnt, MonthDate.Date
FROM  (
    SELECT 1 AS Date UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5 UNION ALL
    SELECT 6 UNION ALL
    SELECT 7 UNION ALL
    SELECT 8 UNION ALL
    SELECT 9 UNION ALL
    SELECT 10 UNION ALL
    SELECT 11 UNION ALL
    SELECT 12 UNION ALL
    SELECT 13 UNION ALL
    SELECT 14 UNION ALL
    SELECT 15 UNION ALL
    SELECT 16 UNION ALL
    SELECT 17 UNION ALL
    SELECT 18 UNION ALL
    SELECT 19 UNION ALL
    SELECT 20 UNION ALL
    SELECT 21 UNION ALL
    SELECT 22 UNION ALL
    SELECT 23 UNION ALL
    SELECT 24 UNION ALL
    SELECT 25 UNION ALL
    SELECT 26 UNION ALL
    SELECT 27 UNION ALL
    SELECT 28 UNION ALL
    SELECT 29 UNION ALL
    SELECT 30 UNION ALL
    SELECT 31) AS MonthDate
LEFT OUTER JOIN products IB 
ON ( MonthDate.date BETWEEN IB.arrived AND IB.released)
GROUP BY MonthDate.Date;

I would hope to see output as follows, with total being the number of active products, however the date column is not a necessity.

date total

2017-01-01 3024

2017-01-02 3029

....

2017-01-30 2987

Any other suggestions?

I added the expected output to the edit. Hopefully that helps. — Ethan, Feb 05 '17 at 05:47
One other question: What is your current output? Your query looks OK to me. — Tim Biegeleisen, Feb 05 '17 at 05:47
The first query is returning much lower numbers than expected such as "1,5,7,12,13". Not sure where it is coming up with those numbers. The second query simply returns "1" for each day. — Ethan, Feb 05 '17 at 05:53
Maybe create a Fiddle and show us the current query with actual data [SQLFiddle](http://www.sqlfiddle.com). — Tim Biegeleisen, Feb 05 '17 at 06:02
How are you storing your dates? Did you wisely use `datetime` or did you store them as text? — Tim Biegeleisen, Feb 05 '17 at 06:03
Yes, the dates are stored as datetime. I will look into creating a Fiddle with data but the answer below seems to have solved my problem perfectly. Thank you for your time though! — Ethan, Feb 05 '17 at 06:07

score 1 · Accepted Answer · edited May 23 '17 at 10:29

See this question for info on generating date ranges; the same approach should work for you. I can't test at the moment, but it would look something like:

SET @date_min = '2017-01-01';
SET @date_max = '2017-01-31';

SELECT
   dg.date,
   COUNT(pb.id) as daily_count
from (
   select DATE_ADD(@date_min, INTERVAL (@i:=@i+1)-1 DAY) as `date`
   from information_schema.columns,(SELECT @i:=0) gen_sub 
   where DATE_ADD(@date_min,INTERVAL @i DAY) BETWEEN @date_min AND @date_max
) date_generator dg
left join products_booking pb on (pb.released IS NULL OR pb.released >= dg.date) AND (pb.arrived <= dg.date)
GROUP BY dg.date
ORDER BY dg.date
;

MySQL Counting Active Records Each Day in Date Range

1 Answers1