March 4, 2024 at 2:01 pm
I have a large table being written too every second with equipment (PLC) information. I need to figure out how much of this data is redundant using an hour window, so from 7am-8am how many different values did I capture per Tagid(Machine). I'm trying to figure out for floatvalue how many distinct values where recorded and how many repeated.
INSERT INTO [dbo].[tmp_sqlt_data_1_2022_05]
([tagid]
,[intvalue]
,[floatvalue]
,[stringvalue]
,[datevalue]
,[dataintegrity]
,[t_stamp])
VALUES
(<tagid, int,>
,<intvalue, bigint,>
,<floatvalue, float,>
,<stringvalue, nvarchar(255),>
,<datevalue, datetime,>
,<dataintegrity, int,>
,<t_stamp, bigint,>)
DATA:
4 NULL 3.62654304504395 NULL NULL 192 1651377600760
4 NULL 3.60732269287109 NULL NULL 192 1651377601757
4 NULL 3.63935852050781 NULL NULL 192 1651377602756
4 NULL 3.60732269287109 NULL NULL 192 1651377604754
4 NULL 3.62654304504395 NULL NULL 192 1651377605755
4 NULL 3.59450721740723 NULL NULL 192 1651377606754
4 NULL 3.64576530456543 NULL NULL 192 1651377607755
4 NULL 3.59450721740723 NULL NULL 192 1651377608758
4 NULL 3.6329517364502 NULL NULL 192 1651377611755
4 NULL 3.58810043334961 NULL NULL 192 1651377612757
4 NULL 3.62654304504395 NULL NULL 192 1651377613759
4 NULL 3.62013626098633 NULL NULL 192 1651377616758
4 NULL 3.65217399597168 NULL NULL 192 1651377617755
4 NULL 3.61372947692871 NULL NULL 192 1651377619754
4 NULL 3.6329517364502 NULL NULL 192 1651377626759
4 NULL 3.61372947692871 NULL NULL 192 1651377627758
4 NULL 3.65217399597168 NULL NULL 192 1651377630757
I have included some sample data...
Thanks.
CREATE TABLE [dbo].[tmp_sqlt_data_1_2022_05](
[tagid] [int] NOT NULL,
[intvalue] [bigint] NULL,
[floatvalue] [float] NULL,
[stringvalue] [nvarchar](255) NULL,
[datevalue] [datetime] NULL,
[dataintegrity] [int] NULL,
[t_stamp] [bigint] NOT NULL,
PRIMARY KEY CLUSTERED
(
[tagid] ASC,
[t_stamp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [History]
) ON [History]
GO
March 4, 2024 at 2:43 pm
March 4, 2024 at 3:12 pm
yes, it submitted twice
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply