Query to fetch counts for all the dates between the two dates

Question

I am encountering a situation where, I have a data. I need to capture count of the row, project_id, for each date, within a given range of date.

e.g.: user enters date as:

    set @start_date = '2016-01-01';

    set @end_date   = '2016-09-30';

I get the data like this:

 |Project id|    |Tracker id| |Statusid| |Hours| |Hours| |Date Created| |Date Updated|           |Count when created|

'Cyrcle Connect', '5',        '1',       NULL, NULL, '2016-07-12 02:33:18', '2016-09-13 07:32:33', '3'

I have to capture information, where, I can show count for each date within these two range of date.

Desired output would be:

    |Count when Created|  |Count when 2016-02-10|

             3                      5

I know union will not work in this case, as I cannot union data for all the 12 months. User can give any range of date.

I have shown only two, but I have to capture information across all the date. The table does not have entry as it keeps the latest row. The snapshot data is available which I can workaround with to generate my sample data.

I just want to know, any method, where I can capture such information. I am not looking for complete solution, just a direction.

Thanks

Answer 1

Try this:

select DATE(Date_Created) as R_DATE , COUNT(*) as R_COUNT
  from <your table>
 where DATE(Date_Created) >= @Start_Date
   and DATE(Date_Created) <  @End_Date
group by R_DATE
;

You can also use the between for the time window (I prefer the above as it is clearer for me).

Query to fetch counts for all the dates between the two dates

Sign up