mmahinay mmahinay - 16 days ago 5
SQL Question

How to display range of date and reference from another Subquery?

i have a list of activities table..now i want to get the count of activities created per day

eg. result would be:

Date Count
01/10/2016 100
02/10/2016 20
03/10/2016 3000


Hours of searching from the net, this is the working code:

DECLARE @start_date DATE = '2016-10-01';
DECLARE @end_date DATE= '2016-10-10';

WITH AllDays
AS ( SELECT @start_date AS [Date]
UNION ALL
SELECT DATEADD(DAY, 1, [Date])
FROM AllDays
WHERE [Date] < @end_date ),
PartitionCount
AS (select count(*) AS [Count]
from Activities act
where CAST(act.CreatedDate AS DATE) = '2016-10-01')
SELECT distinct [AllDays].[Date], [PartitionCount].[Count]
FROM AllDays, PartitionCount, Activities
ORDER BY [AllDays].[Date]
OPTION (MAXRECURSION 0)


This will give this result:

Date Count
01/10/2016 100
02/10/2016 100
03/10/2016 100


However, this one only get the count for the day 2016-10-01.

So I tried to change this part of the code so that the activity created date will be equal to the Date column of AllDays and will get the count of activities for that specific date.

AS (select count(*) AS [Count]
from Activities act
where CAST(act.CreatedDate AS DATE) = [AllDays].[Date])


But, this will give the error


"The multi-part identifier could not be bound"


I would really appreciate your help on how I can proceed with this and get the desired result:

Date Count
01/10/2016 100
02/10/2016 20
03/10/2016 3000


Example table looks like this

Table: Activities

ID Title CreatedDate
1 Activity1 2016-10-03 10:00:00.000
2 Activity2 2016-10-03 11:30:00.000
3 Activity3 2016-10-03 14:00:00.000
4 Activity4 2016-10-03 14:30:00.000
5 Activity5 2016-10-04 10:00:00.000
6 Activity6 2016-10-05 11:00:00.000
7 Activity7 2016-10-06 10:00:00.000
8 Activity8 2016-10-07 11:00:00.000

Answer

As per my understanding you want Count for rows for each day. For that you can do Group By on each day between given range and then also COUNT to get what yo want as below,

SELECT DATEADD(DAY,0, datediff(day,0, date)) As Date, COUNT(*) As Cont 
FROM #temp
WHERE date BETWEEN @startDate AND @endDate
GROUP By DATEADD(DAY,0, DATEDIFF(DAY,0, date))