SQLTeam.com | Weblogs | Forums

Group by datetime


#1

hi team,

i have table where there is column starttime datetime is availble for diffrent dates and time with 15 min duration of data like

2017-05-31 13:00:00
2017-05-31 13:15:00
2017-05-31 13:30:00
2017-05-31 13:45:00
2017-05-31 14:00:00
2017-05-31 14:15:00
2017-05-31 14:30:00
2017-05-31 14:45:00
2017-05-31 15:00:00
2017-05-31 15:15:00

Now i want to fetch data and group by according to hour but only till that last hour where all mins are availble like 2017-05-31 14:45:00 in this case.

in between missing data does not a matters only last hour data matters.


#2
;WITH cte AS
(
	SELECT *,
		COUNT(*) OVER (PARTITION BY DATEADD(hh,DATEDIFF(hh,0,DateColumn),0) AS N
	FROM
		YourTable
)
SELECT * FROM cte WHERE N= 4;