Stack Exchange
Group by each date in a range having events with StartingTimestamp and EndingTimestamp
I would like to count all the events having in a calendar within January and group them by date. This events got a StartingTimestamp and an EndingTimestamp.
For example (Table rp.Calendar):
StartingTimestamp     EndingTimestamp      Title
24.01.2014 08:00      24.01.2014 10:00     Meeting
25.01.2014 17:00      26.01.2014 08:00     Home time
24.01.2014            26.01.2014           Holiday
26.01.2014 17:00      29.01.2014 08:00     Weekend

Now, the result I need, is:
Date         Counter
24.01.2014   2
25.01.2014   2
26.01.2014   3
27.01.2014   1
28.01.2014   1
29.01.2014   1


Have you tried the solution I proposed? if you need some adjustment let me know.

Do you have any feedback?

This will do for January or any month but it can be tweaked for longer periods if required:
WITH January AS (
SELECT 1 AS n
UNION ALL
SELECT n+1 FROM January WHERE n+1<=31
)

SELECT n,COUNT(*)
FROM January
JOIN yourtable ON n BETWEEN datepart(d,StartingTimestamp)  AND datepart(d,EndingTimestamp)
GROUP BY n


quick explanation: January is just a collection of numbers from 1 to 31 generated through a recursive CTE. This table is joined to your table using the day part of start and end date.

For a more general solution you need a table with just one column for with all the consecutive dates,  then you don't need the CTE; use that table instead of 'January' in the query and also, instead of the day part, you can use the whole date to compare.

This is your answer:
SELECT CONVERT(varchar(10),StartingTimestamp,110) AS Date, Count(*) AS Counter
FROM YourTableName
GROUP BY CONVERT(varchar(10),StartingTimestamp,110)

Change 110 to desire format:
101 mm/dd/yy
102 yy.mm.dd
103 dd/mm/yy
104 dd.mm.yy
105 dd-mm-yy
106 dd mon yy
107 Mon dd, yy
108 hh:mm:ss
110 mm-dd-yy
111 yy/mm/dd
112 yymmdd

see more on http://technet.microsoft.com/en-us/library/aa226054(v=sql.80).aspx

This is similar to my solution I used, when I only had single-date-events.  This gives me as a result, that the weekend-event only applies to one day (26.01.).
