May 24, 2018 at 8:37 am
I have a table that is created by a Stored procedure. This itself works fine. A new requirement has been asked of me, and I am unsure how to handle/do it. In the table that is created by the stored procedure, it creates table date like this;
| WEA | Date_Time | Base1 | Base2 | Base3 | SSI1 | SSI2 | SSI3 | Lock1 | Lock2 | Lock3 | Pref_base | dbu | alarm |
| 225170070000 | 30:06.0 | 905.2.01 | 480.2.01 | 588.2.01 | 50 | 47 | 34 | 0 | 0 | 0 | 905.2.01 | 0 | 0 |
| 225280930000 | 15:51.0 | 804.1.01 | 939.3.01 | 688.2.01 | 61 | 34 | 21 | 0 | 0 | 0 | 804.1.01 | 0 | 0 |
I need to average the values of each of the SSI1/SSI2/SSI3 (in separate columns) for each line of WEA over a 24 hour period, and post to a new table so that it can be trended over a 30 day period or more. I attached a sample of the above data as example of what is currently stored.
This is the SQL to create the table of the data;CREATE TABLE RT_Group_Coverage(
WEA INTEGER NOT NULL PRIMARY KEY
,Date_Time VARCHAR(23) NOT NULL
,Base1 VARCHAR(10) NOT NULL
,Base2 VARCHAR(10)
,Base3 VARCHAR(10)
,SSI1 INTEGER NOT NULL
,SSI2 INTEGER NOT NULL
,SSI3 INTEGER NOT NULL
,Lock1 BIT NOT NULL
,Lock2 BIT NOT NULL
,Lock3 BIT NOT NULL
,Pref_base VARCHAR(10) NOT NULL
,dbu BIT NOT NULL
,alarm BIT NOT NULL
);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225170070000,'2018-05-11 14:30:06.013','905.2.01','480.2.01','588.2.01',50,47,34,0,0,0,'905.2.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225280930000,'2018-05-11 18:15:50.980','804.1.01','939.3.01','688.2.01',61,34,21,0,0,0,'804.1.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (227590250000,'2018-05-12 02:21:48.900','172.2.10',NULL,NULL,61,0,0,0,0,0,'172.2.10',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225190720000,'2018-05-12 15:12:22.893','496.1.01','494.1.01','495.1.01',61,44,27,0,0,0,'496.1.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225190710000,'2018-05-13 07:39:05.070','496.1.01','494.1.01','495.1.01',61,43,31,0,0,0,'496.1.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (761680120000,'2018-05-14 03:12:15.727','544.1.01','545.3.01','544.3.01',222,222,184,1,1,0,'544.1.01',1,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225101100000,'2018-05-15 02:48:29.200','588.2.01','905.2.01','480.2.01',60,58,19,0,0,0,'588.2.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (222020100000,'2018-05-16 05:56:14.103','671.2.01','120.1.01','540.3.01',25,23,13,0,0,0,'671.2.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225320520000,'2018-05-17 15:44:52.897','453.1.01','487.3.01','407.1.01',54,47,35,0,0,0,'453.1.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225050960000,'2018-05-19 04:58:30.310','382.2.01','821.3.01','379.3.01',61,61,55,0,0,0,'382.2.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (761100070000,'2018-05-20 19:37:27.397','556.1.01','556.2.01','552.1.01',219,56,36,1,1,0,'556.1.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225200030000,'2018-05-22 11:15:50.913','480.1.01','815.1.01','821.2.01',61,46,35,0,0,0,'480.1.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225060850000,'2018-05-24 04:44:37.950','418.1.01','417.2.01','112.2.01',61,34,29,0,0,0,'418.1.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225060860000,'2018-05-24 04:44:38.013','418.1.01','417.2.01','348.1.01',46,14,9,0,0,0,'418.1.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225060880000,'2018-05-24 04:44:38.030','418.1.01','417.2.01','348.1.01',61,34,24,0,0,0,'418.1.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225060890000,'2018-05-24 04:44:38.043','418.1.01','348.1.01',NULL,25,6,0,0,0,0,'418.1.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225060910000,'2018-05-24 04:44:38.060','418.1.01','417.2.01','376.3.01',25,22,9,0,0,0,'418.1.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225060920000,'2018-05-24 04:44:38.090','417.2.01','418.1.01','379.1.01',28,27,19,0,0,0,'417.2.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225060940000,'2018-05-24 04:44:38.123','417.2.01','418.1.01','175.3.01',61,25,23,0,0,0,'417.2.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225160250000,'2018-05-24 04:44:42.983','391.1.01','489.1.01','167.3.01',61,61,61,0,0,0,'391.1.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225160260000,'2018-05-24 04:44:43.013','391.1.01','167.3.01','489.1.01',61,57,53,0,0,0,'391.1.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225160270000,'2018-05-24 04:44:43.030','391.1.01','167.3.01','489.1.01',61,61,61,0,0,0,'391.1.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225160280000,'2018-05-24 04:44:43.047','391.1.01','489.2.01','167.3.01',61,58,61,0,0,0,'391.1.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225160300000,'2018-05-24 04:44:43.063','391.1.01','489.2.01','167.3.01',61,58,47,0,0,0,'391.1.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225160330000,'2018-05-24 04:44:43.077','391.1.01','489.2.01','424.1.01',43,45,42,0,0,0,'391.1.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225160340000,'2018-05-24 04:44:43.093','391.1.01','424.1.01','489.2.01',48,32,36,0,0,0,'391.1.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225160350000,'2018-05-24 04:44:43.110','424.1.01','391.1.01','489.2.01',54,52,46,0,0,0,'424.1.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225160360000,'2018-05-24 04:44:45.013','424.1.01','391.1.01','489.2.01',59,51,45,0,0,0,'424.1.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225160370000,'2018-05-24 04:44:45.047','424.1.01','364.2.01','364.3.01',61,39,34,0,0,0,'424.1.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225240940000,'2018-05-24 04:44:45.063','940.3.01','942.2.01','941.1.01',61,52,40,0,0,0,'940.3.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225160380000,'2018-05-24 04:44:45.077','424.1.01','364.2.01','364.3.01',61,38,38,0,0,0,'424.1.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225240960000,'2018-05-24 04:44:45.093','940.3.01','935.3.01','936.1.01',61,25,30,0,0,0,'940.3.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225160390000,'2018-05-24 04:44:45.110','424.1.01','364.2.01','364.3.01',61,37,33,0,0,0,'424.1.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225240970000,'2018-05-24 04:44:45.140','940.3.01','936.1.01','935.3.01',61,45,43,0,0,0,'940.3.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225160400000,'2018-05-24 04:44:45.203','424.1.01','364.2.01','364.3.01',61,31,27,0,0,0,'424.1.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225240980000,'2018-05-24 04:44:47.030','940.3.01','936.1.01','935.3.01',56,36,41,0,0,0,'940.3.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225241000000,'2018-05-24 04:44:47.110','940.3.01','936.1.01','941.1.01',61,32,24,0,0,0,'940.3.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225280520000,'2018-05-24 04:44:47.123','675.3.01','838.2.01','453.2.01',61,48,43,0,0,0,'675.3.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225241090000,'2018-05-24 04:44:47.170','936.1.01','935.3.01','940.3.01',61,48,46,0,0,0,'936.1.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225280530000,'2018-05-24 04:44:47.217','453.1.01','675.3.01','487.3.01',61,57,42,0,0,0,'453.1.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225280540000,'2018-05-24 04:44:47.233','675.3.01','453.1.01','838.2.01',61,48,41,0,0,0,'675.3.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (225280720000,'2018-05-24 04:44:52.113','453.2.01','688.2.01','389.3.01',61,61,49,0,0,0,'453.2.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (226270140000,'2018-05-24 04:45:47.870','683.1.01','840.3.01','602.1.01',61,48,44,0,0,0,'683.1.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (226270180000,'2018-05-24 04:45:47.947','684.1.01','840.3.01','602.1.01',53,50,39,0,0,0,'684.1.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (226270190000,'2018-05-24 04:45:47.963','684.1.01','840.3.01','683.1.01',61,47,45,0,0,0,'684.1.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (226270200000,'2018-05-24 04:45:47.980','684.1.01','840.3.01','683.1.01',61,28,14,0,0,0,'684.1.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (220820050000,'2018-05-24 04:46:07.267','616.2.01','123.2.01','383.3.01',61,30,33,0,0,0,'616.2.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (220820060000,'2018-05-24 04:46:07.280','123.2.01','680.3.01','303.3.01',28,28,32,0,0,0,'123.2.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (220820070000,'2018-05-24 04:46:07.297','680.3.01','123.2.01','383.3.01',42,38,16,0,0,0,'680.3.01',0,0);
INSERT INTO RT_Group_Coverage(WEA,Date_Time,Base1,Base2,Base3,SSI1,SSI2,SSI3,Lock1,Lock2,Lock3,Pref_base,dbu,alarm) VALUES (220820210000,'2018-05-24 04:46:07.313','191.1.01','191.2.01','281.3.01',56,61,44,0,0,0,'191.1.01',0,0);
Thanks,
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
May 24, 2018 at 9:01 am
brian.cook - Thursday, May 24, 2018 8:37 AMI have a table that is created by a Stored procedure. This itself works fine. A new requirement has been asked of me, and I am unsure how to handle/do it. In the table that is created by the stored procedure, it creates table date like this;
WEA Date_Time Base1 Base2 Base3 SSI1 SSI2 SSI3 Lock1 Lock2 Lock3 Pref_base dbu alarm 225170070000 30:06.0 905.2.01 480.2.01 588.2.01 50 47 34 0 0 0 905.2.01 0 0 225280930000 15:51.0 804.1.01 939.3.01 688.2.01 61 34 21 0 0 0 804.1.01 0 0 I need to average the values of each of the SSI1/SSI2/SSI3 (in separate columns) for each line of WEA over a 24 hour period, and post to a new table so that it can be trended over a 30 day period or more. I attached a sample of the above data as example of what is currently stored.
Thanks,
That's not helpful sample data. Read the articles on my signature to learn how to post sample data.
The date_time apparently has no date (and 30 hours?)
Have you already tried something?
May 24, 2018 at 9:04 am
What are you stuck on?
May 24, 2018 at 9:39 am
Luis, thank you and I will update the question shortly.
sestell1, I am stuck on how to accomplish it. I have not done anything like this before.
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
May 24, 2018 at 9:54 am
The current description seems to be a basic aggregate query using AVG(). We just want to know if something else is missing.
May 24, 2018 at 10:01 am
Luis, that is true, it may end up being that simple. The three SSI columns need to be averaged over the 24 hour period, for each WEA. Some days may have 14 entries for one WEA, some days less, etc.
Thank you for pointing the question format references. that convert website is perfect. I have looked for something like that before.
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
May 24, 2018 at 10:31 am
Ah, so for every row, you want to average each of the SSI columns for all rows within 24 hours prior to the current row date/time?
I doubt this is the most efficient approach, but something like this?
SELECT
MainData.WEA,
Max(MainData.SSI1) AS SSI1,
Max(MainData.SSI2) AS SSI2,
Max(MainData.SSI3) AS SSI3,
AVG(AvgData.SSI1) AS Average_SSI1,
AVG(AvgData.SSI2) AS Average_SSI2,
AVG(AvgData.SSI3) AS Average_SSI3
FROM
RT_Group_Coverage MainData
Join
RT_Group_Coverage AvgData
ON
AvgData.Date_Time BETWEEN DateAdd(hour, -24, MainData.Date_Time) AND MainData.Date_Time
GROUP BY
MainData.WEA
May 25, 2018 at 6:42 am
Thanks sestell1, Unfortunately it gives a syntax error on the usage of hour in the DateAdd portion. I have tried adding a single quote before and after the word hour. Single quoting around the Parenthesis section of that line, to no avail.
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
May 25, 2018 at 8:07 am
Hmm, it shouldn't. I suspect the error is actually somewhere else.
The aliases I put on the average column results are invalid because they start with a number. Try removing the "24h_" prefix on those aliases and see if that helps.
I've updated the post above with the same change.
May 25, 2018 at 8:14 am
sestell1, based on the query you posted, I tried it this way, and do get results;
SELECT
WEA,
Date_Time,
AVG(SSI1),
AVG(SSI2),
AVG(SSI3)
OVER
(ORDER BY Date_Time ASC)
FROM RT_Group_Coverage
GROUP BY WEA, Date_Time, SSI1, SSI2, SSI3
This looks like it is giving me the results I am wanting.
WEA Date_Time (No column name) (No column name) (No column name)
220980260000 05/11/2017 49 47 29
220980280000 05/11/2017 61 37 32
220980300000 05/11/2017 59 31 31
220980310000 05/11/2017 61 50 30
222020070000 05/11/2017 39 34 25
222020080000 05/11/2017 39 35 22
222020100000 05/11/2017 39 37 23
222020110000 05/11/2017 30 33 22
222020130000 05/11/2017 61 12 22
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
May 25, 2018 at 8:43 am
Hmm, you are grouping by the same columns you are averaging, so that probably isn't giving you what you want.
Also the OVER clause is only being applied to the last average of SSI3.
If you just want a straight average of the SSI columns by WEA, you could do this:
SELECT
WEA,
AVG(SSI1),
AVG(SSI2),
AVG(SSI3)
FROM RT_Group_Coverage
GROUP BY WEA
Based on your initial post though, I thought you were looking to average only records in the 24 hours prior to each record, which is what this code does:
SELECT
MainData.WEA,
Max(MainData.SSI1) AS SSI1,
Max(MainData.SSI2) AS SSI2,
Max(MainData.SSI3) AS SSI3,
Max(MainData.Date_Time) AS Date_Time,
AVG(AvgData.SSI1) AS Average_SSI1,
AVG(AvgData.SSI2) AS Average_SSI2,
AVG(AvgData.SSI3) AS Average_SSI3
FROM
RT_Group_Coverage MainData
Join
RT_Group_Coverage AvgData
ON
AvgData.Date_Time BETWEEN DateAdd(hour, -24, MainData.Date_Time) AND MainData.Date_Time
GROUP BY
MainData.WEA
It joins each record to all records within 24 hours prior to the record's date_time, allowing them to then be averaged.
I was able to run that query against the sample table and data you provided (although I did have to change WEA to a BigInt in the table as your values were larger than an integer allows).
Feel free to post your modified code so we can try and spot the syntax error.
May 25, 2018 at 9:01 am
SELECT
WEA,
Date_Time,
AVG(SSI1),
AVG(SSI2),
AVG(SSI3)
OVER
(ORDER BY Date_Time ASC)
FROM RT_Group_Coverage
GROUP BY WEA, Date_Time, SSI1, SSI2, SSI3
This looks like it is giving me the results I am wanting.
WEA Date_Time (No column name) (No column name) (No column name)
220980260000 05/11/2017 49 47 29
220980280000 05/11/2017 61 37 32
220980300000 05/11/2017 59 31 31
220980310000 05/11/2017 61 50 30
222020070000 05/11/2017 39 34 25
222020080000 05/11/2017 39 35 22
222020100000 05/11/2017 39 37 23
222020110000 05/11/2017 30 33 22
222020130000 05/11/2017 61 12 22
So why is the SSI3 Average done differently than the other two. If you want to compute a "rolling average"; meaning one that will be computed for each and every row across the entire period and for each combination of WEA and Date_Time; then you should probably apply that technique to all of the AVG functions. At the moment, your query will only apply it to SSI3. The question is why a rolling average instead of just the overall ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 25, 2018 at 9:02 am
I have refined it to this;SELECT
WEA,
Date_Time,
AVG(SSI1),
AVG(SSI2),
AVG(SSI3)
OVER
(ORDER BY Date_Time ASC)
FROM RT_Group_Coverage
WHERE Date_time > DATEADD(hour, -24, GETDATE()) AND SSI1 <> 12 AND SSI2 <> 12 AND SSI3 <> 12
GROUP BY WEA, Date_Time, SSI1, SSI2, SSI3
I am however unable to add the Columns names though. Any suggestions?
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
May 25, 2018 at 9:37 am
sestell1 - Friday, May 25, 2018 8:43 AMHmm, you are grouping by the same columns you are averaging, so that probably isn't giving you what you want.
Also the OVER clause is only being applied to the last average of SSI3.If you just want a straight average of the SSI columns by WEA, you could do this:
SELECT
WEA,
AVG(SSI1),
AVG(SSI2),
AVG(SSI3)
FROM RT_Group_Coverage
GROUP BY WEABased on your initial post though, I thought you were looking to average only records in the 24 hours prior to each record, which is what this code does:
SELECT
MainData.WEA,
Max(MainData.SSI1) AS SSI1,
Max(MainData.SSI2) AS SSI2,
Max(MainData.SSI3) AS SSI3,
Max(MainData.Date_Time) AS Date_Time,
AVG(AvgData.SSI1) AS Average_SSI1,
AVG(AvgData.SSI2) AS Average_SSI2,
AVG(AvgData.SSI3) AS Average_SSI3
FROM
RT_Group_Coverage MainData
Join
RT_Group_Coverage AvgData
ON
AvgData.Date_Time BETWEEN DateAdd(hour, -24, MainData.Date_Time) AND MainData.Date_Time
GROUP BY
MainData.WEAIt joins each record to all records within 24 hours prior to the record's date_time, allowing them to then be averaged.
I was able to run that query against the sample table and data you provided (although I did have to change WEA to a BigInt in the table as your values were larger than an integer allows).
Feel free to post your modified code so we can try and spot the syntax error.
sestell1, thanks, I see in this one you added Max(MainData.Date_Time) AS Date_Time, now the query does run. It errors now with an Arithmetic overflow error converting expression to data type int.
The WEA field is actually a FLOAT data type, by changing the Select statement to read;SELECT It executes correctly.
MainData.WEA AS BIGINT,
Max(MainData.SSI1) AS SSI1,
Max(MainData.SSI2) AS SSI2,
Max(MainData.SSI3) AS SSI3,
Max(MainData.Date_Time) AS Date_Time,
AVG(AvgData.SSI1) AS Average_SSI1,
AVG(AvgData.SSI2) AS Average_SSI2,
AVG(AvgData.SSI3) AS Average_SSI3
FROM
RT_Group_Coverage MainData
Join
RT_Group_Coverage AvgData
ON
AvgData.Date_Time BETWEEN DateAdd(hour, -24, MainData.Date_Time) AND MainData.Date_Time
GROUP BY
MainData.WEA
You are correct in the initial assumptions, I was just trying to expand on what you had given me and see if I could make it work. 🙂 Thank you for explaining what I was getting. The education helps.
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
May 25, 2018 at 10:54 am
brian.cook - Friday, May 25, 2018 9:37 AMsestell1 - Friday, May 25, 2018 8:43 AMHmm, you are grouping by the same columns you are averaging, so that probably isn't giving you what you want.
Also the OVER clause is only being applied to the last average of SSI3.If you just want a straight average of the SSI columns by WEA, you could do this:
SELECT
WEA,
AVG(SSI1),
AVG(SSI2),
AVG(SSI3)
FROM RT_Group_Coverage
GROUP BY WEABased on your initial post though, I thought you were looking to average only records in the 24 hours prior to each record, which is what this code does:
SELECT
MainData.WEA,
Max(MainData.SSI1) AS SSI1,
Max(MainData.SSI2) AS SSI2,
Max(MainData.SSI3) AS SSI3,
Max(MainData.Date_Time) AS Date_Time,
AVG(AvgData.SSI1) AS Average_SSI1,
AVG(AvgData.SSI2) AS Average_SSI2,
AVG(AvgData.SSI3) AS Average_SSI3
FROM
RT_Group_Coverage MainData
Join
RT_Group_Coverage AvgData
ON
AvgData.Date_Time BETWEEN DateAdd(hour, -24, MainData.Date_Time) AND MainData.Date_Time
GROUP BY
MainData.WEAIt joins each record to all records within 24 hours prior to the record's date_time, allowing them to then be averaged.
I was able to run that query against the sample table and data you provided (although I did have to change WEA to a BigInt in the table as your values were larger than an integer allows).
Feel free to post your modified code so we can try and spot the syntax error.sestell1, thanks, I see in this one you added Max(MainData.Date_Time) AS Date_Time, now the query does run. It errors now with an Arithmetic overflow error converting expression to data type int.
The WEA field is actually a FLOAT data type, by changing the Select statement to read;SELECTIt executes correctly.
MainData.WEA AS BIGINT,
Max(MainData.SSI1) AS SSI1,
Max(MainData.SSI2) AS SSI2,
Max(MainData.SSI3) AS SSI3,
Max(MainData.Date_Time) AS Date_Time,
AVG(AvgData.SSI1) AS Average_SSI1,
AVG(AvgData.SSI2) AS Average_SSI2,
AVG(AvgData.SSI3) AS Average_SSI3
FROM
RT_Group_Coverage MainData
Join
RT_Group_Coverage AvgData
ON
AvgData.Date_Time BETWEEN DateAdd(hour, -24, MainData.Date_Time) AND MainData.Date_Time
GROUP BY
MainData.WEAYou are correct in the initial assumptions, I was just trying to expand on what you had given me and see if I could make it work. 🙂 Thank you for explaining what I was getting. The education helps.
I almost posted a lot earlier to indicate that the data you supplied for the WEA column exceeded the size of an Integer, so if you are going to avoid the float data type, here's how you do that:SELECT
CONVERT(BIGINT, MainData.WEA) AS WEA,
MAX(MainData.SSI1) AS SSI1,
MAX(MainData.SSI2) AS SSI2,
MAX(MainData.SSI3) AS SSI3,
MAX(MainData.Date_Time) AS Date_Time,
AVG(AvgData.SSI1) AS Average_SSI1,
AVG(AvgData.SSI2) AS Average_SSI2,
AVG(AvgData.SSI3) AS Average_SSI3
FROM RT_Group_Coverage AS MainData
INNER JOIN RT_Group_Coverage AS AvgData
ON MainData.WEA = AvgData.WEA
AND AvgData.Date_Time BETWEEN DateAdd(hour, -24, MainData.Date_Time) AND MainData.Date_Time
GROUP BY CONVERT(BIGINT, MainData.WEA)
ORDER BY CONVERT(BIGINT, MainData.WEA);
The way you have the query set up, it will remain a float data type in the result set, as you end up using the word BIGINT as a column alias rather than a data type. Also please note that your join should probably include matching the WEA values.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply