July 31, 2014 at 6:17 am
CREATE Table #tempreadings (ID int NOT NULL, RowID INT, Value float, Readingdate datetime)
INSERT INTO #tempreadings VALUES
(1, 1, 23.09, '2014-07-17 00:12:30.000'),
(1, 2, 78.56, '2014-07-17 00:18:24.000'),
(1, 3, 32.56, '2014-07-17 00:29:29.000'),
(1, 4, 12.34, '2014-07-17 01:03:20.000'),
(1, 5, 56.43, '2014-07-17 01:23:30.000'),
(1, 6, 90.76, '2014-07-17 01:29:20.000'),
(1, 7, 23.09, '2014-07-17 02:12:30.000'),
(1, 8, 17.56, '2014-07-17 02:17:29.000'),
(1, 9, 2.56, '2014-07-17 03:29:29.000'),
(1, 10, 45.34, '2014-07-17 04:03:20.000'),
(2, 11, 23.09, '2014-07-17 00:12:30.000'),
(2, 12, 78.56, '2014-07-17 00:18:24.000'),
(2, 13, 32.56, '2014-07-17 00:22:40.000'),
(2, 14, 12.34, '2014-07-17 01:03:20.000'),
(2, 15, 56.43, '2014-07-17 01:23:30.000'),
(2, 16, 90.76, '2014-07-17 01:26:20.000'),
(2, 17, 23.09, '2014-07-17 02:12:30.000'),
(2, 18, 17.56, '2014-07-17 02:21:29.000'),
(2, 19, 2.56, '2014-07-17 03:14:29.000'),
(2, 20, 45.34, '2014-07-17 05:03:20.000')
-- Result
-- I want to diplay a report for that i was to consider 30 minutes as a data point for a given day so 24 * 2 = 48 datapoints i have for a day
-- Now i want to dispay reading for every ID i have to take the latest value for every 30 minutes slot
ID ReadingDate SeqIDValue
1 2014-07-17 00:29:29.000 1 32.56 -- RowID = 3
1 2014-07-17 01:29:20.000 2 90.76 -- RowID = 6
1 2014-07-17 02:17:29.000 3 17.56 -- RowID = 8
1 2014-07-17 03:29:29.000 4 2.56 -- RowID = 9
1 2014-07-17 04:03:20.000 5 45.34 -- RowID = 10
2 2014-07-17 00:22:40.000 1 32.56 -- RowID = 13
2 2014-07-17 01:26:20.000 2 90.76 -- RowID = 16
2 2014-07-17 02:21:29.000 3 17.56 -- RowID = 18
2 2014-07-17 03:14:29.000 4 2.56 -- RowID = 19
2 2014-07-17 05:03:20.000 5 45.34 -- RowID = 20
July 31, 2014 at 6:55 am
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
July 31, 2014 at 7:16 am
Another option is
SELECT *
FROM (
SELECT id
,rowid
,value
,readingdate
,ROW_NUMBER() OVER (
PARTITION BY id
,datepart(hh, readingdate)
,CASE
WHEN datepart(n, readingdate) <= 30
THEN 0
ELSE 1
END ORDER BY id ASC
,readingdate DESC
) PartID
FROM #tempreadings
) a
WHERE partid = 1
July 31, 2014 at 7:23 am
stevenb 63624 (7/31/2014)
Another option is
SELECT *
FROM (
SELECT id
,rowid
,value
,readingdate
,ROW_NUMBER() OVER (
PARTITION BY id
,datepart(hh, readingdate)
,CASE
WHEN datepart(n, readingdate) <= 30
THEN 0
ELSE 1
END ORDER BY id ASC
,readingdate DESC
) PartID
FROM #tempreadings
) a
WHERE partid = 1
Quite a lot faster than my query too. Nice job 😎
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply