row changes per hour by TagID

  • 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
  • What are your desired results, based on this sample data?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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

  • 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?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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

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

     

     

  • I would like just a query.

  • 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.

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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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

  • 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
  • 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

  • Perfect.. Thanks.

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

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