user6346661 user6346661 - 3 months ago 8
SQL Question

Get result for all dates within a given range including non-existent dates

(UPDATED)

I have two tables like below

Table

subscribers
:

id | service_id | subscribe_date

1 | 1 | 2016-08-10
2 | 2 | 2016-08-09
3 | 2 | 2016-08-05
4 | 1 | 2016-08-03


Table
services
:

id | service_name

1 | test1
2 | test2
3 | test2


subscribers.service_id
has a foreign key on
services.id


I want to get a data from this tables like below :

service_id | subscribe_date | count

1 | 2016-08-10 | 1
1 | 2016-08-09 | 0
2 | 2016-08-10 | 0
2 | 2016-08-09 | 1
3 | 2016-08-10 | 0
3 | 2016-08-09 | 0


I trying to get this data with below query:

SELECT COUNT(*), subscribe_date, service_id
FROM subscribers
INNER JOIN services ON subscribers.service_id = services.id
WHERE subscribe_date BETWEEN '2016-08-09' AND '2016-08-10'
GROUP BY service_id, subscribe_date;


But I am not successful. I get the below result:

1 | 2016-08-10 | 1
2 | 2016-08-09 | 1

Answer

Here you go:

Since you don't have any calendar table so that we need to created all the dates between your given date range (inclusive) through a query. But like I said you need to agree on the terms & conditions of this query before using it.

SELECT 
dateWiseServices.id AS service_id,
dateWiseServices.`Day` AS subscribed_date,
COALESCE(yourQuery.total,0) AS cnt

FROM 
(
    SELECT 
    S.id,
    dateTable.Day
    FROM 
    (
    SELECT ADDDATE('2016-08-09', INTERVAL @i:=@i+1 DAY) AS DAY
    FROM (
    SELECT a.a
    FROM (SELECT 0 AS a UNION ALL SELECT 1 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) AS a
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 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) AS b
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 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) AS c
    ) a
    JOIN (SELECT @i := -1) r1
    WHERE 
    @i < DATEDIFF('2016-08-10', '2016-08-09')
    ) AS dateTable
    CROSS JOIN Services S
) AS dateWiseServices

LEFT JOIN 
(
    SELECT COUNT(*) AS total, subscribe_date, service_id
    FROM subscribers
            INNER JOIN services ON subscribers.service_id = services.id
    WHERE subscribe_date BETWEEN '2016-08-09' AND '2016-08-10'
    GROUP BY service_id, subscribe_date
) AS yourQuery 
ON dateWiseServices.id = yourQuery.service_id AND dateWiseServices.`Day` = yourQuery.subscribe_date
ORDER BY dateWiseServices.id, dateWiseServices.`Day` DESC