radha krishna radha krishna - 1 year ago 100
SQL Question

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

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.


Answer Source

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).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download