0

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
Sven Kannenberg
  • 859
  • 2
  • 10
  • 20

2 Answers2

0

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

Hadi Sharifi
  • 1,497
  • 5
  • 18
  • 28
  • 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.). – Sven Kannenberg Jan 24 '14 at 11:31
0

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
Jayvee
  • 10,670
  • 3
  • 29
  • 40
  • 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. – Jayvee Jan 24 '14 at 11:49
  • 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. – Jayvee Jan 24 '14 at 15:56