0

I have some messy RFID data due to over sensitive antenna's. There is a physical process that tracks an RFID tag moving through different stations in a cycle. An item with an RFID tag can move through the cycle more than one time a day, but it is highly unlikely that it could start the cycle within a two hour window of that first read.

I am trying to either create a flag column to determine when the new cycle begins for an item or return a count of how many times an item has gone through a cycle.

Here is some sample data:

enter image description here

CREATE TABLE [dbo].[samplerfiddata](
    [Item] [nvarchar](50) NOT NULL,
    [Station_Type] [nvarchar](50) NOT NULL,
    [Station_Name] [nvarchar](50) NOT NULL,
    [Timestamp] [datetime2](7) NOT NULL,
    [Trying_to_Create_this_Flag_Column] [nvarchar](50) NOT NULL
);

INSERT [dbo].[samplerfiddata] ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'A', N'Decontamination', N'Decontamination', CAST(N'2020-10-10T06:30:00.0000000' AS DateTime2), N'1')
INSERT [dbo].[samplerfiddata] ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'A', N'Decontamination', N'Decontamination', CAST(N'2020-10-11T14:30:00.0000000' AS DateTime2), N'1')
INSERT [dbo].[samplerfiddata] ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'A', N'Washer', N'Washer', CAST(N'2020-10-11T14:45:00.0000000' AS DateTime2), N'0')
INSERT [dbo].[samplerfiddata] ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'A', N'Decontamination', N'Decontamination', CAST(N'2020-10-11T15:15:00.0000000' AS DateTime2), N'0')
INSERT [dbo].[samplerfiddata] ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'A', N'Other', N'Decontamination', CAST(N'2020-10-11T23:30:00.0000000' AS DateTime2), N'1')
INSERT [dbo].[samplerfiddata] ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'A', N'Washer', N'Washer', CAST(N'2020-10-12T00:15:00.0000000' AS DateTime2), N'0')
INSERT [dbo].[samplerfiddata] ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'A', N'Other', N'Decontamination', CAST(N'2020-10-12T00:45:00.0000000' AS DateTime2), N'0')
INSERT [dbo].[samplerfiddata] ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'A', N'Other', N'Decontamination', CAST(N'2020-10-13T16:00:00.0000000' AS DateTime2), N'1')
INSERT [dbo].[samplerfiddata] ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'A', N'Other', N'Decontamination', CAST(N'2020-10-13T16:30:00.0000000' AS DateTime2), N'0')
INSERT [dbo].[samplerfiddata] ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'A', N'Decontamination', N'Decontamination', CAST(N'2020-10-14T13:30:00.0000000' AS DateTime2), N'1')
INSERT [dbo].[samplerfiddata] ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'B', N'Other', N'Decontamination', CAST(N'2020-10-12T08:30:00.0000000' AS DateTime2), N'1')
INSERT [dbo].[samplerfiddata] ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'B', N'Decontamination', N'Decontamination', CAST(N'2020-10-12T14:30:00.0000000' AS DateTime2), N'1')
INSERT [dbo].[samplerfiddata] ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'B', N'Washer', N'Washer', CAST(N'2020-10-12T14:45:00.0000000' AS DateTime2), N'0')
INSERT [dbo].[samplerfiddata] ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'B', N'Decontamination', N'Decontamination', CAST(N'2020-10-12T15:15:00.0000000' AS DateTime2), N'0')
INSERT [dbo].[samplerfiddata] ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'B', N'Decontamination', N'Decontamination', CAST(N'2020-10-12T18:00:00.0000000' AS DateTime2), N'1')
INSERT [dbo].[samplerfiddata] ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'B', N'Washer', N'Washer', CAST(N'2020-10-13T18:15:00.0000000' AS DateTime2), N'0')
INSERT [dbo].[samplerfiddata] ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'B', N'Decontamination', N'Decontamination', CAST(N'2020-10-13T19:00:00.0000000' AS DateTime2), N'1')

In this data we have two different items moving through the cycle with all scenarios captured. The business logic for a new cycle is defined as when an item is read by an RFID Antenna (Station) where the Station Type = Decontamination or the Station Name = Decontamination and it has been more than two hours since the previous first read of the cycle.

Ultimately, I am trying to return a result set that looks like this:

enter image description here

How could this be completed?

1
  • What result do you expect if your fourth row in your sample data is 2020-10-11T16:31:00 (e.g. row 3 is less than 2 hours after row 2, but row 4 is less than 2 hours after row 3 but more than 2 hours after row 2)? Just trying to understand if the immediately prior row is the only one that's important. – Aaron Bertrand Oct 12 '20 at 21:40
1

In this case you can take advantage of LAG() function to get previous Timestamp value.

Accesses data from a previous row in the same result set without the use of a self-join starting with SQL Server 2012 (11.x). LAG provides access to a row at a given physical offset that comes before the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a previous row.

Then simply use a conditional sum, if DATEDIFF with current Timestamp >= 2 add 1 otherwise add 0 to the final result grouped by Item.

;WITH data AS
(
SELECT
  Item,
      CASE WHEN 
                DATEDIFF (hour, 
                          COALESCE(LAG(Timestamp) 
                                       OVER (PARTITION BY Item 
                                             ORDER BY Timestamp), 
                                   DATEADD(hour, -2, Timestamp)),
                          Timestamp) >= 2 THEN 1 ELSE 0 END AS lastTM
FROM
  samplerfiddata
)
SELECT
  Item,
  SUM(lastTM) as Cycles
FROM
  data
GROUP BY
  Item;
Item | Cycles
:--- | -----:
A    |      5
B    |      4

db<>fiddle here

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.