radha krishna radha krishna - 17 days ago 6
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.

Thanks

Answer

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

Comments