row changes per hour by TagID

Question

Post reply

row changes per hour by TagID

Bruin

SSCrazy

Points: 2083
More actions
March 4, 2024 at 2:01 pm
Go to Answer
#4389363
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
```

Steve Collins

SSCrazy

Points: 2176

March 6, 2024 at 7:29 pm

Answer

#4390827

One way could be to add the date and hour to the GROUP BY

select t.tagid, cast(v.ss_dt as date) day_dt, datepart(hour, v.ss_dt) hr,
       count(*) float_count, 
       count(distinct t.floatvalue) unq_float_count
from #tmp_sqlt_data_1_2022_05 t
     cross apply (values (cast(dateadd(millisecond, t.t_stamp%1000, dateadd(second, t.t_stamp/1000, '1970-01-01')) as datetime2(3)))) v(ss_dt)
group by t.tagid, cast(v.ss_dt as date), datepart(hour, v.ss_dt)
order by t.tagid, day_dt, hr;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246990 More actions · Answer 1

What are your desired results, based on this sample data?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Bruin SSCrazy Points: 2083 More actions · Answer 2

A report that shows timespan of the analysis and then total for the TagID then Unique and Nonunique(differnt value)

There where 800 captures and 750 all had same value and, in that hour, only 50 records were different.

7-8am captue

tagid count unique-cnt duplicate-cnt

4 800 750 50

Phil Parkin SSC Guru Points: 246990 More actions · Answer 3

Bruin wrote:

A report that shows timespan of the analysis and then total for the TagID then Unique and Nonunique(differnt value)
There where 800 captures and 750 all had same value and, in that hour, only 50 records were different.
7-8am captue
tagid count unique-cnt duplicate-cnt 4 800 750 50

You've provided 17 rows of sample data and yet can't be bothered providing your desired results based on that tiny data sample?

You want a report rather than a query, is that right? SSRS?

You've got nearly 2,000 points. This should not be that tough for you. Which part of it are you having difficulty with?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Steve Collins SSCrazy Points: 2176 More actions · Answer 4

Bruin wrote:

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

Maybe there's a further explanation of the [t_stamp] column? It's BIGINT so how to convert to a SQL Server DATETIME or DATETIME2(?)? Is it Unix epoch milliseconds?

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Bruin SSCrazy Points: 2083 More actions · Answer 5

Yes -- Is it Unix epoch milliseconds? Convert-DATETIME

Bruin SSCrazy Points: 2083 More actions · Answer 6

Bruin

SSCrazy

Points: 2083

March 4, 2024 at 7:28 pm

#4389547

I would like just a query.

Bruin SSCrazy Points: 2083 More actions · Answer 7

Looks like if I add this in my query logic I'm getting the datetime desired

DATEADD(s,t_stamp/1000,'1969-12-31 20:00:00')

2024-02-01 01:00:00.000

2024-02-01 01:00:01.000

Struggle with doing the count of records per TagID within the hour that are the same and how many are different.

Thanks.

Phil Parkin SSC Guru Points: 246990 More actions · Answer 8

Check out COUNT(x) vs COUNT(DISTINCT x)

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Steve Collins SSCrazy Points: 2176 More actions · Answer 9

Bruin wrote:

Looks like if I add this in my query logic I'm getting the datetime desired
DATEADD(s,t_stamp/1000,'1969-12-31 20:00:00')
2024-02-01 01:00:00.000 2024-02-01 01:00:01.000
Struggle with doing the count of records per TagID within the hour that are the same and how many are different.
Thanks.

Looks like your formula is off by 4 hours. The Unix Epoch begins at T00:00 on Jan 1, 1970 afaik. Imo this is a simple way to convert without losing any information

select cast(dateadd(millisecond, @unix_epoch_ms%1000, dateadd(second, @unix_epoch_ms/1000, '1970-01-01')) as datetime2(3)) as dt2_val;

Also, I Google'ed it and Jeff Moden wrote an article on this subject

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Bruin SSCrazy Points: 2083 More actions · Answer 10

I came up with this, but not sure how to get 1 hour time frames from the data. I want to see how many times in an hour for a single tag that we have multiple matches.

Thanks.

  select case reccount
           when 1 then 'Single Value'
           else 'Multiple matches'
       end as number_of_matches,
       count(*) as distinct_dates,
       sum(reccount) as record_count
from (select tagid,floatvalue,
             count(*) reccount
      from [sqlt_data_4_2024_02]
  where 
   ????????
      group by tagid,floatvalue)) v
group by
case reccount
           when 1 then 'Single Value'
           else 'Multiple matches'
       end

Bruin SSCrazy Points: 2083 More actions · Answer 11

Bruin

SSCrazy

Points: 2083

March 6, 2024 at 11:05 pm

#4390904

Perfect.. Thanks.