CODEWITHSUNDEEP
sqlt-sql
user3096487
user3096487

Reputation: 399

Selecting previous row number based on column value

I am trying to create a table that shows Operator Shifts be combining rows w Operator logons and logoffs. As there are multiple stations and operators I need to select the next row based on the operator not just the next value. For example, when separating the joined queries in the below the last logon time will be row 329 and the logoff time will be row 408.
Logon = 1 and Logoff = 0

select 
    A.Operator, 
    A.Station,A.[TIME] AS "Logon", 
    B.[TIME] AS "Logoff"
from  (SELECT 
        [Operator],
        [TIME],
        [Station], 
        ROW_NUMBER() OVER (ORDER BY [TIME], [Operator], Station) AS RowNumber
       FROM 
          shiftstagenew 
       WHERE [ON-OFF] = '0' 
       AND [Operator] = 'Operator1')  A
JOIN 
      (SELECT 
        [Operator],
        [TIME],
        [Station], 
        ROW_NUMBER() OVER (ORDER BY [TIME], [Operator], Station) AS RowNumber
       FROM shiftstagenew WHERE [ON-OFF] = '1' 
       AND [Operator]  = 'Operator1') B
ON (
    A.RowNumber = B.RowNumber 
        AND 
    A.Operator = B.Operator 
        AND 
    A.Station = B.Station)

I've updated the query and coming closer

   select A.Operator, A.Station,B.[TIME] AS "Logon", A.[TIME] AS "Logoff"
      from  (SELECT [TIME], [Operator], [Station], ROW_NUMBER() OVER (PARTITION 
      BY 
      [Operator], [Station] ORDER BY [Time], Station) AS RowNumber
   FROM 
      shiftstagenew WHERE [ON-OFF] = '1')  A
JOIN 
     (SELECT [Time],[Operator], [Station], ROW_NUMBER() OVER (PARTITION BY 
       [Operator], [Station] ORDER BY [Time], Station) AS RowNumber
     FROM shiftstagenew WHERE [ON-OFF] = '0') B
ON (A.Operator=B.Operator AND A.Station=B.Station AND A.RowNumber=B.RowNumber)

Query Results:
Operator    Station Logoff  Logon
Vitemeus    102 2019-07-07 08:15:19.380 2019-05-27 17:39:06.550
Vitemeus    102 2019-07-07 07:23:54.790 2019-05-27 14:50:13.687
Vitemeus    102 2019-07-07 03:45:46.890 2019-05-27 11:56:54.903
Vitemeus    102 2019-07-07 03:44:28.267 2019-05-24 23:52:49.313
Vitemeus    102 2019-07-07 03:08:16.343 2019-05-20 19:30:11.010

As you can see there are the logoff isn't following the last logon

OFF
2019-07-07 08:15:19.380 Vitemeus    102 445
2019-07-07 07:23:54.790 Vitemeus    102 444
2019-07-07 03:45:46.890 Vitemeus    102 443
2019-07-07 03:44:28.267 Vitemeus    102 442
2019-07-07 03:08:16.343 Vitemeus    102 441
2019-07-07 02:00:09.757 Vitemeus    102 440
2019-07-07 00:37:30.610 Vitemeus    102 439
2019-07-06 13:16:02.330 Vitemeus    102 438
2019-07-06 12:00:19.157 Vitemeus    102 437

ON
2019-07-07 08:02:02.500 Vitemeus    102 498
2019-07-07 06:52:34.303 Vitemeus    102 497
2019-07-07 03:44:42.427 Vitemeus    102 496
2019-07-07 03:25:28.957 Vitemeus    102 495
2019-07-07 02:52:40.210 Vitemeus    102 494
2019-07-07 01:45:00.373 Vitemeus    102 493
2019-07-07 00:32:39.800 Vitemeus    102 492

For those asking for the table structure(ON-OFF is either 0 or 1): 

CREATE TABLE [dbo].[ShiftStageNEW](
    [ON-OFF] [nvarchar](1) NULL,
    [Time] [datetime] NULL,
    [Station] [nvarchar](3) NULL,
    [Action] [nvarchar](4) NULL,
    [Operator] [varchar](8) NULL,
    [Data] [nvarchar](900) NULL,
    [Translation] [int] NULL,
    [LogTime] [bigint] NULL,
    [MessageId] [nvarchar](256) NULL
) ON [PRIMARY]

Upvotes: 0

Views: 185

Answers (2)

callisto
callisto

Reputation: 5083

You should look at the LAG function: 

SELECT
     [Year]                         = YEAR([OrderDate])
    ,[Sales Amount]                 = SUM([SalesAmount])
    ,[Sales Amount Previous Year]   = LAG(SUM([SalesAmount])) OVER (ORDER BY YEAR([OrderDate]))
FROM [dbo].[FactResellerSales]
GROUP BY YEAR([OrderDate])
ORDER BY [Year];

source: https://www.mssqltips.com/sqlservertutorial/9127/sql-server-window-functions-lead-and-lag/

Also see my question here that matches your requirement: Find next record where status field is different from current

Upvotes: 1

SAm
SAm

Reputation: 2222

Common table expression should help you with what you want to achieve.
But the sequencing of the row numbers seems a little fishy.
Added partitioning to the sequencing. You can stick to your original if that's what you need.

WITH LOGON AS (
    SELECT
        [Operator],
        [TIME],
        [Station], 
        ROW_NUMBER() OVER (PARTITION BY [Operator], Station ORDER BY [TIME]) AS RowNumber
    FROM 
        shiftstagenew
    WHERE 
        [ON-OFF] = '0' 
    AND [Operator] = 'Operator1'
),
LOGOFF AS (
    SELECT
        [Operator],
        [TIME],
        [Station], 
        ROW_NUMBER() OVER (PARTITION BY [Operator], Station ORDER BY [TIME]) AS RowNumber
    FROM 
        shiftstagenew
    WHERE 
        [ON-OFF] = '1' 
    AND [Operator] = 'Operator1'
)
select 
    LOGON.Operator, 
    LOGON.Station,
    LOGON.[TIME] AS "Logon", 
    LOGOFF.[TIME] AS "Logoff"
FROM LOGON LEFT JOIN LOGOFF 
ON (
    LOGON.RowNumber = LOGOFF.RowNumber 
        AND 
    LOGON.Operator = LOGOFF.Operator 
        AND 
    LOGON.Station = LOGOFF.Station)

Upvotes: 0

Related Questions