Skip to main content
Database Administrators

Return to Answer

Post Undeleted by thofle
added 471 characters in body
Source Link
thofle
thofle
  • 41
  • 5

Something like this might work

SELECT 
  count(1)
  ,DATEADD(MINUTE,(DATEPART(MINUTE, date_recordedcl_StartDate) / 15)*15,(DATEADD(HOUR, DATEDIFF(HOUR, 0, date_recordedcl_StartDate), 0)))
FROM raw_bulbstbl_Calls
WHERE 
  cl_StartTime BETWEEN @StartDate AND @EndDate
GROUP BY 
  DATEADD(MINUTE,(DATEPART(MINUTE, date_recordedcl_StartDate) / 15)*15,(DATEADD(HOUR, DATEDIFF(HOUR, 0, date_recordedcl_StartDate), 0)))

To get the quarter this will give you a value of 0-3. Multiply by 15 to get 0,15,30,45. 
DATEPART(MINUTE, date_recorded) / 15

To round down cl_StartTime to a full hour. 
DATEADD(HOUR, DATEDIFF(HOUR, 0, date_recorded), 0)

Then you just add it together like in the query to get the correct timestamp and group by that. 
SELECT count(1)
,DATEADD(MINUTE,(DATEPART(MINUTE, date_recorded) / 15)*15,(DATEADD(HOUR, DATEDIFF(HOUR, 0, date_recorded), 0)))
FROM raw_bulbs
GROUP BY 
DATEADD(MINUTE,(DATEPART(MINUTE, date_recorded) / 15)*15,(DATEADD(HOUR, DATEDIFF(HOUR, 0, date_recorded), 0)))

Something like this might work

SELECT 
  count(1)
  ,DATEADD(MINUTE,(DATEPART(MINUTE, cl_StartDate) / 15)*15,(DATEADD(HOUR, DATEDIFF(HOUR, 0, cl_StartDate), 0)))
FROM tbl_Calls
WHERE 
  cl_StartTime BETWEEN @StartDate AND @EndDate
GROUP BY 
  DATEADD(MINUTE,(DATEPART(MINUTE, cl_StartDate) / 15)*15,(DATEADD(HOUR, DATEDIFF(HOUR, 0, cl_StartDate), 0)))

To get the quarter this will give you a value of 0-3. Multiply by 15 to get 0,15,30,45. 
DATEPART(MINUTE, date_recorded) / 15

To round down cl_StartTime to a full hour. 
DATEADD(HOUR, DATEDIFF(HOUR, 0, date_recorded), 0)

Then you just add it together like in the query to get the correct timestamp and group by that.
Post Deleted by thofle
Source Link
thofle
thofle
  • 41
  • 5

SELECT count(1)
,DATEADD(MINUTE,(DATEPART(MINUTE, date_recorded) / 15)*15,(DATEADD(HOUR, DATEDIFF(HOUR, 0, date_recorded), 0)))
FROM raw_bulbs
GROUP BY 
DATEADD(MINUTE,(DATEPART(MINUTE, date_recorded) / 15)*15,(DATEADD(HOUR, DATEDIFF(HOUR, 0, date_recorded), 0)))