WITH DistinctDays AS (
SELECT DISTINCT ID, ReadingdateDT = CAST(CAST(Readingdate AS DATE) AS DATETIME)
FROM #tempreadings
)
SELECT *
FROM DistinctDays d
CROSS APPLY (
SELECT Timeslot = DATEADD(minute,n*30,ReadingdateDT)
FROM (VALUES
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),
(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),
(40),(41),(42),(43),(44),(45),(46),(47)
) iTally (n)
) x
CROSS APPLY (
SELECT TOP 1 *
FROM #tempreadings i
WHERE i.ID = d.ID
AND Readingdate >= Timeslot AND Readingdate < DATEADD(minute,30,Timeslot)
ORDER BY Readingdate DESC
) y
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden