Rama
Reputation: 65
Generating data averages for 15min slots using SQL Server
I have an SQL Server table as below.
CREATE TABLE [dbo].[ChannelData](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ChannelId] [int] NOT NULL,
[ChannelValue] [decimal](10, 2) NULL,
[ChannelDataLogTime] [datetime] NOT NULL,
[Active] [bit] NULL,CONSTRAINT [PK_ChannelData] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
Sample Data is as follows:::
+----+-----------+--------------+-------------------------+--------+
| Id | ChannelId | ChannelValue | ChannelDataLogTime | Active |
+----+-----------+--------------+-------------------------+--------+
| 1 | 9 | 5.46 | 2015-06-09 14:00:11.463 | 1 |
| 2 | 9 | 8.46 | 2015-06-09 14:01:11.503 | 1 |
| 3 | 9 | 3.46 | 2015-06-09 14:02:27.747 | 1 |
| 4 | 9 | 6.46 | 2015-06-09 14:03:11.503 | 1 |
| 5 | 9 | 1.46 | 2015-06-09 14:04:11.530 | 1 |
| 6 | 9 | 4.46 | 2015-06-09 14:05:11.537 | 1 |
| 7 | 9 | 7.46 | 2015-06-09 14:06:11.547 | 1 |
| 8 | 9 | 2.46 | 2015-06-09 14:07:33.983 | 1 |
| 9 | 9 | 5.46 | 2015-06-09 14:08:11.570 | 1 |
| 10 | 9 | 8.46 | 2015-06-09 14:09:11.603 | 1 |
| 11 | 9 | 3.46 | 2015-06-09 14:10:11.613 | 1 |
| 12 | 9 | 6.47 | 2015-06-09 14:11:11.623 | 1 |
| 13 | 9 | 1.47 | 2015-06-09 14:12:24.497 | 1 |
| 14 | 9 | 4.47 | 2015-06-09 14:13:11.623 | 1 |
| 15 | 9 | 7.47 | 2015-06-09 14:14:11.650 | 1 |
| 16 | 9 | 2.47 | 2015-06-09 14:15:11.707 | 1 |
| 17 | 9 | 5.47 | 2015-06-09 14:16:11.707 | 1 |
| 18 | 9 | 8.47 | 2015-06-09 14:17:25.647 | 1 |
| 19 | 9 | 3.47 | 2015-06-09 14:18:11.707 | 1 |
| 20 | 9 | 6.47 | 2015-06-09 14:19:11.753 | 1 |
| 21 | 9 | 1.47 | 2015-06-09 14:20:11.760 | 1 |
| 22 | 9 | 4.47 | 2015-06-09 14:21:11.790 | 1 |
| 23 | 9 | 7.47 | 2015-06-09 14:22:29.500 | 1 |
| 24 | 9 | 2.47 | 2015-06-09 14:23:11.907 | 1 |
| 25 | 9 | 5.47 | 2015-06-09 14:24:12.057 | 1 |
| 26 | 9 | 8.47 | 2015-06-09 14:25:11.817 | 1 |
| 27 | 9 | 3.47 | 2015-06-09 14:26:11.837 | 1 |
| 28 | 9 | 6.47 | 2015-06-09 14:27:32.253 | 1 |
| 29 | 9 | 1.47 | 2015-06-09 14:28:11.870 | 1 |
| 30 | 9 | 4.47 | 2015-06-09 14:29:11.870 | 1 |
| 31 | 9 | 7.50 | 2015-06-09 16:00:13.313 | 1 |
| 32 | 9 | 2.50 | 2015-06-09 16:01:13.260 | 1 |
| 33 | 9 | 5.50 | 2015-06-09 16:02:13.290 | 1 |
| 34 | 9 | 8.50 | 2015-06-09 16:03:13.270 | 1 |
| 35 | 9 | 3.50 | 2015-06-09 16:04:32.827 | 1 |
| 36 | 9 | 6.50 | 2015-06-09 16:05:13.323 | 1 |
| 37 | 9 | 1.50 | 2015-06-09 16:06:13.330 | 1 |
| 38 | 9 | 4.50 | 2015-06-09 16:07:13.337 | 1 |
| 39 | 9 | 7.50 | 2015-06-09 16:08:13.313 | 1 |
| 40 | 9 | 2.50 | 2015-06-09 16:09:28.497 | 1 |
| 41 | 9 | 5.50 | 2015-06-09 16:10:13.370 | 1 |
| 42 | 9 | 8.50 | 2015-06-09 16:11:13.417 | 1 |
| 43 | 9 | 3.50 | 2015-06-09 16:12:13.540 | 1 |
| 44 | 9 | 6.50 | 2015-06-09 16:13:13.577 | 1 |
| 45 | 9 | 1.50 | 2015-06-09 16:14:33.880 | 1 |
| 46 | 9 | 4.50 | 2015-06-09 16:15:13.453 | 1 |
| 47 | 9 | 7.50 | 2015-06-09 16:16:13.500 | 1 |
| 48 | 9 | 2.50 | 2015-06-09 16:17:13.497 | 1 |
| 49 | 9 | 5.50 | 2015-06-09 16:18:13.503 | 1 |
| 50 | 9 | 8.50 | 2015-06-09 16:19:38.717 | 1 |
| 51 | 9 | 3.50 | 2015-06-09 16:21:13.567 | 1 |
| 52 | 9 | 6.50 | 2015-06-09 16:22:13.557 | 1 |
| 53 | 9 | 1.50 | 2015-06-09 16:23:14.163 | 1 |
| 54 | 9 | 4.50 | 2015-06-09 16:24:13.607 | 1 |
| 55 | 9 | 7.50 | 2015-06-09 16:25:38.783 | 1 |
| 56 | 9 | 2.50 | 2015-06-09 16:27:13.660 | 1 |
| 57 | 9 | 5.51 | 2015-06-09 16:28:13.710 | 1 |
| 58 | 9 | 8.51 | 2015-06-09 16:29:13.703 | 1 |
| 59 | 9 | 3.51 | 2015-06-09 16:30:13.713 | 1 |
+----+-----------+--------------+-------------------------+--------+
Now I am generating 15 minute averaged data for a period of time, with start date and end date. Which is working fine with out any issues.
I have scenario where the data will be missing for some time. Which inturn missing the 15 minute slots as there is no data for that 15min slot. What I need is to list the 15 minute slots even if the data is not available during that time slot using SQL Query.
SELECT
Avg(chnldata.ChannelValue) AS ChannelValue,
DATEADD(minute,FLOOR(DATEDIFF(minute,0,ChannelDataLogTime)/15)*15,0) as HourlyDateTime,
chnldata.ChannelId as Id
FROM ChannelData as chnldata
WHERE chnldata.ChannelId in (9) AND chnldata.ChannelDataLogTime >= '06/09/2015' AND chnldata.ChannelDataLogTime < '06/11/2015 23:59:50'
GROUP BY chnldata.ChannelId, DATEADD(minute,FLOOR(DATEDIFF(minute,0,ChannelDataLogTime)/15)*15,0)
This is the existing 15 min average query. But it doesn't display missing 15min slots. The current output is:::
+--------------+-------------------------+----+
| ChannelValue | HourlyDateTime | Id |
+--------------+-------------------------+----+
| 5.129333 | 2015-06-09 14:00:00.000 | 9 |
| 4.803333 | 2015-06-09 14:15:00.000 | 9 |
| 5.033333 | 2015-06-09 16:00:00.000 | 9 |
| 5.270769 | 2015-06-09 16:15:00.000 | 9 |
| 3.510000 | 2015-06-09 16:30:00.000 | 9 |
+--------------+-------------------------+----+
Required Output is:::
+--------------+-------------------------+----+
| ChannelValue | HourlyDateTime | Id |
+--------------+-------------------------+----+
| 5.129333 | 2015-06-09 14:00:00.000 | 9 |
| 4.803333 | 2015-06-09 14:15:00.000 | 9 |
| NULL | 2015-06-09 14:30:00.000 | 9 |
| NULL | 2015-06-09 14:45:00.000 | 9 |
| NULL | 2015-06-09 15:00:00.000 | 9 |
| NULL | 2015-06-09 15:15:00.000 | 9 |
| NULL | 2015-06-09 15:30:00.000 | 9 |
| NULL | 2015-06-09 15:45:00.000 | 9 |
| 5.033333 | 2015-06-09 16:00:00.000 | 9 |
| 5.270769 | 2015-06-09 16:15:00.000 | 9 |
| 3.510000 | 2015-06-09 16:30:00.000 | 9 |
+--------------+-------------------------+----+
Upvotes: 0
Views: 77
Answers (3)
Hector Montero
Reputation: 221
Take in consideration the limitations of maxrecursion option.
DECLARE @StartDT DATETIME = '2015-06-09';
DECLARE @EndDT DATETIME = '2015-06-12'; -- moved to the next day to use >= and < operators correctly
;WITH
[Interval]
AS
(
SELECT
[Start] = @StartDT
,[End] = DATEADD(MINUTE, 15, @StartDT)
UNION ALL
SELECT
[Start] = [End]
,[End] = DATEADD(MINUTE, 15, [End])
FROM [Interval]
WHERE (1 = 1)
AND ([End] < @EndDT)
),
[Available]
AS
(
SELECT
[Start] = CONVERT(SMALLDATETIME, MIN([CD].[ChannelDataLogTime]))
,[End] = CONVERT(SMALLDATETIME, MAX([CD].[ChannelDataLogTime]))
FROM [dbo].[ChannelData] AS [CD]
WHERE (1 = 1)
AND (@StartDT <= [CD].[ChannelDataLogTime] AND [CD].[ChannelDataLogTime] < @EndDT)
)
SELECT
[ChannelValue] = AVG([CD].[ChannelValue])
,[HourlyDateTime] = [I].[Start]
,[Id] = [CD].[ChannelId]
FROM [Available] AS [A]
INNER JOIN [Interval] AS [I]
ON ([A].[Start] <= [I].[Start] AND [I].[Start] <= [A].[End])
LEFT OUTER JOIN [dbo].[ChannelData] AS [CD]
ON
(
([CD].[ChannelId] IN (9))
AND ([I].[Start] <= [CD].[ChannelDataLogTime] AND [CD].[ChannelDataLogTime] < [I].[End])
)
GROUP BY
[I].[Start]
,[CD].[ChannelId]
ORDER BY
[I].[Start]
OPTION (MAXRECURSION 32767);
Upvotes: 0
Cato
Reputation: 3701
build a time range CTE, which can be done in various ways, but the cartesian product method is probably faster than many methods if you want speed it might be best to build a static dates table and maybe a dates and a time table
;WITH mins as (SELECT 0 as q union select 15 union select 30 union select 45),
dats as (SELECT MIN(ChannelDataLogTime) as t1, max(ChannelDataLogTime) as t2 from channeldata),
ranges as (SELECT CAST(t1 as date) s1 FROM dats
union all
SELECT dateadd(day,1,r.s1) from ranges r where r.s1< (select t2 from dats)
),
hrs as (select 0 h union all select h + 1 from hrs where h < 23), --hours 0 to 23
slots as (select dateadd(MINUTE,mins.q,dateadd(hour,hrs.h,cast(ranges.s1 as datetime2))) as strt from mins,ranges,hrs ),
ids as (SELECT distinct ChannelId from ChannelData),
allslot as (select channelid, strt from slots,ids)
SELECT count(0) as x,
coalesce(Avg(chnldata.ChannelValue) , 0) AS ChannelValue,
s.strt HourlyDateTime,
s.ChannelId as Id
FROM ChannelData as chnldata
RIGHT JOIN allslot s on s.strt <= ChannelDataLogTime and ChannelDataLogTime < dateadd(minute,15,s.strt) and s.ChannelId = chnldata.ChannelId
WHERE chnldata.ChannelId is null or chnldata.ChannelId in (9) AND chnldata.ChannelDataLogTime >= '20150906' AND chnldata.ChannelDataLogTime < '20151123'
GROUP BY s.ChannelId, s.strt
Upvotes: 0
Tab Alleman
Reputation: 31785
RIGHT OUTER JOIN to a CTE that has all the possible 15-minute intervals in your time-range.
Upvotes: 1
Related Questions
- SQL Server: hour and minute average
- Use T-SQL window functions to retrieve 5-minute averages from 1-minute data
- calculate the average value in a certain time interval
- Get average and max in 1 minute intervals T-SQL
- Get minute average of value
- SQL Server 2008 TSQL loop through and calculate average time between start and end time
- Average value per minute from second
- Get average of TIME(7)
- T-SQL Time Averaging
- Query to obtain average each 30 seconds