3

I need to find the Daily total count of Active Users based on the Start Date and End Date.

REGISTRATION TABLE

id  registration_no  start_date  end_date
1   1000             2014/12/01  2014/12/03
2   1001             2014/12/01  2014/12/03
3   1002             2014/12/02  2014/12/04
4   1003             2014/12/02  2014/12/04
5   1004             2014/12/02  2014/12/04
6   1005             2014/12/03  2014/12/05
7   1006             2014/12/05  2014/12/06
8   1007             2014/12/05  2014/12/09
9   1008             2014/12/06  2014/12/10
10  1009             2014/12/07  2014/12/11

The result should be in the following format. 

Date        Active Users
2014-12-01  2
2014-12-02  5
2014-12-03  6
2014-12-04  4
2014-12-05  3
2014-12-06  3
2014-12-07  3
2014-12-08  3
2014-12-09  3
2014-12-10  2
2014-12-11  1
2014-12-12  0

I know the following query is not working. 

SELECT start_date, count(*) FROM registration
WHERE start_date >= '2014/12/01' AND end_date <='2014/12/12'
GROUP BY start_date

Which is not the desired output :

2014-12-01 2 
2014-12-02 3 
2014-12-03 1 
2014-12-05 2 
2014-12-06 1 
2014-12-07 1

Any help would be much appreciated.

Saaz Rai
  • 262
  • 5
  • 15
  • looks fine to me. try a simple select without group by. Do you get the expected records? – koriander Jan 04 '15 at 01:17
  • Using my Query will fetch the following result: 2014-12-01 2 2014-12-02 3 2014-12-03 1 2014-12-05 2 2014-12-06 1 2014-12-07 1 – Saaz Rai Jan 04 '15 at 01:38
  • The results you received are what I would expect for your query. When you group by start date you are essentially telling it to return one row per unique start date. not sure how to get the answer you are looking for in raw sql. – Sam Jan 04 '15 at 03:26
  • The easy way is to query the database for each date but that might take a performance hit. I am looking for a solution that might utilize subqueries or Unions with just one database query. – Saaz Rai Jan 04 '15 at 09:02

2 Answers2

3

You need to create a "calendar" with all the days you need and then use a query like:

SELECT calDay as `Date`, count(id) as `Active Users`
FROM   (SELECT cast('2014-12-01' + interval `day` day as date) calDay
        FROM   days31
        WHERE  cast('2014-12-01' + interval `day` day as date) < '2014-12-12') calendar
LEFT JOIN registration on (calDay between start_date and end_date)
GROUP BY calDay
ORDER BY calDay;

You can see it working in this fiddle, where days31 is just a view with integers 0-30. This allows the query to work in any calendar up to a period of 31 days. You can add more days to the view or generate them on the fly using cross joins. See http://www.artfulsoftware.com/infotree/qrytip.php?id=95

a paid nerd
  • 30,702
  • 30
  • 134
  • 179
koriander
  • 3,110
  • 2
  • 15
  • 23
1

Try it.... please note on where condition FOR 2014-12-02, as per comment

SELECT DATE_FORMAT(start_date,'%Y-%m-%d')as Date, count(*) as ActiveUser FROM registration
WHERE (start_date >= '2014/12/02' AND end_date <='2014/12/02')
GROUP BY start_date
A l w a y s S u n n y
  • 36,497
  • 8
  • 60
  • 103
  • This query is similar to mine which is not giving the desired result. Please see my updated question. – Saaz Rai Jan 04 '15 at 02:30
  • can you please tell me on what criteria, you expect this result 2014-12-01 2 2014-12-02 5 2014-12-03 6 2014-12-04 4 2014-12-05 3 2014-12-06 3 2014-12-07 3 2014-12-08 3 2014-12-09 3 2014-12-10 2 2014-12-11 1 2014-12-12 0 – A l w a y s S u n n y Jan 04 '15 at 02:43
  • On 2014-12-02 the following users are active : 1000, 1001, 1002, 1003, 1004 as '2014-12-02' >= start_date AND 2014-12-02 <= end_date. – Saaz Rai Jan 04 '15 at 02:58
  • your solution does not provide the required output. – Saaz Rai Jan 04 '15 at 09:03