none
Query time difference when a column value changes

    Question

  • I have a table where we write part test info from robots. I'm trying to get a list of tool changes within last 5 days.

    Below a simplified version of my table. I would like to know when PART_NO column changes from 23147446 to 23147423 or vise versa. and when it does, start and the end time of both records, which gives me the downtime. I tried MIN and MAX, But only worked to get last tool change not a list.

    Further note: Im using SQL Server 2000, This table also contains several other values in PART_NO that I don't care about.Any help is appreciated, as im completely stuck now. Thanks !

    Ideal results would be:

    23147446     09/12 12:24:43 to 09/12 12:25:13

    23147423     09/12 00:57:39- 09/12 01:03:19and goes on for last 5 days

    SERL_TS                               PART_NO

    2013-09-12 12:33:42.000    23147446
    2013-09-12 12:32:25.000    23147446
    2013-09-12 12:30:36.000    23147446
    2013-09-12 12:29:15.000    23147446
    2013-09-12 12:28:06.000    23147446
    2013-09-12 12:27:13.000    23147446
    2013-09-12 12:26:13.000    23147446
    2013-09-12 12:24:43.000    23147423
    2013-09-12 12:23:10.000    23147423
    2013-09-12 12:21:22.000    23147423
    2013-09-12 12:20:26.000    23147423
    2013-09-12 12:19:29.000    23147423
    2013-09-12 12:18:32.000    23147423
    2013-09-12 12:17:25.000    23147423
    2013-09-12 12:16:30.000    23147423
    2013-09-12 12:15:24.000    23147423
    2013-09-12 12:14:29.000    23147423
    2013-09-12 12:13:34.000    23147423

    2013-09-12 01:09:39.000    23147458
    2013-09-12 01:08:47.000    23147458
    2013-09-12 01:08:02.000    23147458
    2013-09-12 01:06:44.000    23147458
    2013-09-12 01:05:42.000    23147423
    2013-09-12 01:04:08.000    23147423
    2013-09-12 01:03:19.000    23147423
    2013-09-12 01:02:38.000    23147458
    2013-09-12 01:01:30.000    23147458
    2013-09-12 01:00:13.000    23147453
    2013-09-12 00:59:25.000    23147453
    2013-09-12 00:58:31.000    23147453
    2013-09-12 00:57:39.000    23147446
    2013-09-12 00:56:55.000    23147446
    2013-09-12 00:55:45.000    23147453

    Thursday, September 12, 2013 5:32 PM

Answers

  • Hi Stol3n,

    Because you are on SQL Server 2000 you cannot use windowing functions and they would help a lot in this case.

    Try this code:

    if object_id('tempdb..#temp') is not null drop table #temp
    create table #temp
    (
    	ID int identity(1,1) not null primary key
    	, SERL_NBR varchar(100)
    	, SERL_TS datetime
    	, LIN_CUST_ITEM varchar(100)
    )
    
    insert into #temp (SERL_NBR, SERL_TS, LIN_CUST_ITEM)
    select
    	t.SERL_NBR, t.SERL_TS, t.LIN_CUST_ITEM
    from @table t
    order by t.SERL_TS, t.SERL_NBR
    
    if object_id('tempdb..#temp2') is not null drop table #temp2
    select
    	IDENTITY(int, 1,1) as ID
    	, t1.LIN_CUST_ITEM as PartFrom
    	, t2.LIN_CUST_ITEM as PartTo
    	, t1.SERL_TS as Time1
    	, t2.SERL_TS as Time2
    	, t1.ID as ID1, t2.ID as ID2
    into #temp2
    from #temp t1
    	inner join #temp t2 on t1.ID = t2.ID - 1
    			and t1.LIN_CUST_ITEM <> t2.LIN_CUST_ITEM
    order by t1.ID, t2.ID
    
    select
    	t1.ID, t1.PartFrom, t1.PartTo, tt1.SERL_TS as Time1, tt2.SERL_TS as Time2
    from #temp2 t1
    	left join #temp2 t2 on t1.ID - 1 = t2.ID
    	inner join #temp tt1 on coalesce(t2.ID2, 1) = tt1.ID
    	inner join #temp tt2 on t1.ID1 = tt2.ID
    order by t1.ID


    Regards, Dean Savović

    • Marked as answer by STOL3N Monday, September 16, 2013 3:14 PM
    Friday, September 13, 2013 11:47 AM
  • SELECT  * ,
            CASE WHEN Q.SERL_NBR <> Q.Next_SERL_NBR THEN 1
                 ELSE 0
            END AS SERL_NBR_Changed		
    FROM    ( SELECT    O.* ,
                        ( SELECT TOP 1
                                    I.SERL_NBR
                          FROM      @table I
                          WHERE     I.SERL_TS > O.SERL_TS
                          ORDER BY  I.SERL_TS ASC
                        ) AS Next_SERL_NBR ,
                        ( SELECT TOP 1
                                    I.SERL_TS
                          FROM      @table I
                          WHERE     I.SERL_TS > O.SERL_TS
                          ORDER BY  I.SERL_TS ASC
                        ) AS Next_SERL_TS
              FROM      @table O
            ) Q
    ORDER BY Q.SERL_TS ASC;

    • Marked as answer by STOL3N Monday, September 16, 2013 3:14 PM
    Friday, September 13, 2013 1:59 PM

All replies

  • E.g.

    SELECT L.*, 
      R.*, 
      CASE WHEN L.PART_NO <> R.PART_NO 
        THEN 1 
        ELSE 0
      END AS PartChanged
    FROM yourTable L
      LEFT JOIN 
      (
        SELECT TOP 1 *
        FROM yourTable I 
        WHERE I.SERL_TS > L.SERL_TS
        ORDER BY I.SERL_TS ASC
      ) R;

    Thursday, September 12, 2013 6:04 PM
  • As I said I have several other part #s in same table, and have hard time interpreting above statement as Im new to SQL. Can someone provide me with more info. TX

    Thursday, September 12, 2013 9:51 PM
  • First of all: Does it return what you want?

    btw you know the SQL Server 2000 is no longer supported?

    Friday, September 13, 2013 8:19 AM
  • No, Its not returning what I want, complaining about ":" in the end.

    Yes, I know, Planning on a upgrade soon. Thanks

    Friday, September 13, 2013 10:11 AM
  • Please post a concise and complete example. Include table DDL and sample data INSERT statements as a runnable T-SQL script.
    Friday, September 13, 2013 10:18 AM
  • DECLARE @table table (SERL_NBR int, SERL_TS datetime,LIN_CUST_ITEM int)
    INSERT INTO @table
    select 020861696, '2013-09-11 07:52:21.000', 23147453 union
    select 020861697, '2013-09-11 07:51:12.000', 23147453 union
    select 020861698, '2013-09-11 07:48:48.000', 23147453 union
    select 020861699, '2013-09-11 07:45:46.000', 23147453 union
    select 020861700, '2013-09-11 07:43:33.000', 23147474 union
    select 020861701, '2013-09-11 07:42:42.000', 23147474 union
    select 020861702, '2013-09-11 07:41:47.000', 23147474 union
    select 020862582, '2013-09-11 07:39:43.000', 23147422 union
    select 020862581, '2013-09-11 07:38:48.000', 23147422 union
    select 020863356, '2013-09-11 07:38:41.000', 23147422 union
    select 020862580, '2013-09-11 07:37:54.000', 23147422 union
    select 020862579, '2013-09-11 07:36:56.000', 23147422 union
    select 020862582, '2013-09-11 06:39:43.000', 23147453 union
    select 020862581, '2013-09-11 06:38:48.000', 23147453 union
    select 020863356, '2013-09-11 06:38:41.000', 23147453 union
    select 020862580, '2013-09-11 06:37:54.000', 23147422 union
    select 020862579, '2013-09-11 06:36:56.000', 23147422 union
    select 020862582, '2013-09-11 05:39:43.000', 23147453 union
    select 020862581, '2013-09-11 05:38:48.000', 23147453 union
    select 020863356, '2013-09-11 05:38:41.000', 23147453 union
    select 020862580, '2013-09-11 05:37:54.000', 23147445 union
    select 020862579, '2013-09-11 05:36:56.000', 23147445 union
    select 020862582, '2013-09-11 04:39:43.000', 23147453 union
    select 020862581, '2013-09-11 04:38:48.000', 23147453 union
    select 020863356, '2013-09-11 04:38:41.000', 23147453 union
    select 020862580, '2013-09-11 04:37:54.000', 23147422 union
    select 020862579, '2013-09-11 04:36:56.000', 23147422

    ---

    Table im working on contains serval other fields (if it matters)
    What i need is a list of part # changes and how long it took for those changes(only for 2 part #s(23147453
    23147422), This table contains
    several other part #s coming in from the machines that I dont want in my report

    My ideal result will look something like this

    Part#From   Part#to        Time1                    Time2
    23147422    23147453   09/11/2013 07:39            09/11/2013 07:45
    23147453    23147422   09/11/2013 06:39            09/11/2013 07:36
    23147422    23147453   09/11/2013 06:37            09/11/2013 06:38
    And goes on for last 5 days of changes


    Please let me now, If im not clear yet, Thank you !

    Friday, September 13, 2013 10:53 AM
  • What are your key fields here, we need to sort this dataset somehow, with what field you came to this order ?

    since serial_nbr and time seems unsorted, its difficult to get the right records,

    Show us how you sorted your dataset which you prepared in DDL ?


    Regards, Dineshkumar,
    Please 'Mark as Answer' if my post answers your question and 'Vote as Helpful' if it helps you

    Dineshkumar's BI Blog


    • Edited by Dineshkumar Friday, September 13, 2013 11:20 AM
    Friday, September 13, 2013 11:19 AM
  • Hi,

    Try this:

    EDIT: Ahhh, didn't see you were running on SQL2000. So this solution won't work for you.

    with sample_Data as (
     select 020861696 serialno, '2013-09-11 07:52:21.000' ts, 23147453 partno union
     select 020861697, '2013-09-11 07:51:12.000', 23147453 union
     select 020861698, '2013-09-11 07:48:48.000', 23147453 union
     select 020861699, '2013-09-11 07:45:46.000', 23147453 union
     select 020861700, '2013-09-11 07:43:33.000', 23147474 union
     select 020861701, '2013-09-11 07:42:42.000', 23147474 union
     select 020861702, '2013-09-11 07:41:47.000', 23147474 union
     select 020862582, '2013-09-11 07:39:43.000', 23147422 union
     select 020862581, '2013-09-11 07:38:48.000', 23147422 union
     select 020863356, '2013-09-11 07:38:41.000', 23147422 union
     select 020862580, '2013-09-11 07:37:54.000', 23147422 union
     select 020862579, '2013-09-11 07:36:56.000', 23147422 union
     select 020862582, '2013-09-11 06:39:43.000', 23147453 union
     select 020862581, '2013-09-11 06:38:48.000', 23147453 union
     select 020863356, '2013-09-11 06:38:41.000', 23147453 union
     select 020862580, '2013-09-11 06:37:54.000', 23147422 union
     select 020862579, '2013-09-11 06:36:56.000', 23147422 union
     select 020862582, '2013-09-11 05:39:43.000', 23147453 union
     select 020862581, '2013-09-11 05:38:48.000', 23147453 union
     select 020863356, '2013-09-11 05:38:41.000', 23147453 union
     select 020862580, '2013-09-11 05:37:54.000', 23147445 union
     select 020862579, '2013-09-11 05:36:56.000', 23147445 union
     select 020862582, '2013-09-11 04:39:43.000', 23147453 union
     select 020862581, '2013-09-11 04:38:48.000', 23147453 union
     select 020863356, '2013-09-11 04:38:41.000', 23147453 union
     select 020862580, '2013-09-11 04:37:54.000', 23147422 union
     select 020862579, '2013-09-11 04:36:56.000', 23147422
     
     )
     
     , row_numbers as (
     select serialno, ts, partno
    		, ROW_NUMBER() over (order by ts) rn
     From sample_Data
     )
     
     , part_changes as (
     select r1.rn
    	, r1.partno newpart
    	, r1.ts timestart
    	, coalesce(r2.partno, r1.partno) oldpart
    	, r2.ts timeend
    	, case when r1.partno <> r2.partno or r2.partno is null then 1 else 0 end as part_change
     from  row_numbers r1
     left outer join row_numbers r2
     on r1.rn - 1= r2.rn
     )
     
    select rn, oldpart, newpart, timeend, timestart, DATEDIFF(mi,timeend, timestart) minutes_to_change
    from part_changes
    where part_change = 1


    Friday, September 13, 2013 11:22 AM
  • Hi Stol3n,

    Because you are on SQL Server 2000 you cannot use windowing functions and they would help a lot in this case.

    Try this code:

    if object_id('tempdb..#temp') is not null drop table #temp
    create table #temp
    (
    	ID int identity(1,1) not null primary key
    	, SERL_NBR varchar(100)
    	, SERL_TS datetime
    	, LIN_CUST_ITEM varchar(100)
    )
    
    insert into #temp (SERL_NBR, SERL_TS, LIN_CUST_ITEM)
    select
    	t.SERL_NBR, t.SERL_TS, t.LIN_CUST_ITEM
    from @table t
    order by t.SERL_TS, t.SERL_NBR
    
    if object_id('tempdb..#temp2') is not null drop table #temp2
    select
    	IDENTITY(int, 1,1) as ID
    	, t1.LIN_CUST_ITEM as PartFrom
    	, t2.LIN_CUST_ITEM as PartTo
    	, t1.SERL_TS as Time1
    	, t2.SERL_TS as Time2
    	, t1.ID as ID1, t2.ID as ID2
    into #temp2
    from #temp t1
    	inner join #temp t2 on t1.ID = t2.ID - 1
    			and t1.LIN_CUST_ITEM <> t2.LIN_CUST_ITEM
    order by t1.ID, t2.ID
    
    select
    	t1.ID, t1.PartFrom, t1.PartTo, tt1.SERL_TS as Time1, tt2.SERL_TS as Time2
    from #temp2 t1
    	left join #temp2 t2 on t1.ID - 1 = t2.ID
    	inner join #temp tt1 on coalesce(t2.ID2, 1) = tt1.ID
    	inner join #temp tt2 on t1.ID1 = tt2.ID
    order by t1.ID


    Regards, Dean Savović

    • Marked as answer by STOL3N Monday, September 16, 2013 3:14 PM
    Friday, September 13, 2013 11:47 AM
  • Serial # is a unique value and always in a ascending order.

    I sorted this records by Timestamp in descending order.

    I dont have a key value in this tabl :( 

    Friday, September 13, 2013 11:50 AM
  • When I try above code with minor changes this is what i gt below result. 

    Im getting there to what I want, but start/end time and part #s are not exactly as wanted.

    + Question : Can I even format these two part #s into a group of part #s (for eg: part no 1,3,5,7,9) as left and part #2,4,6,8,10 as right and get results set for changes from left to right vise vera. 

    I know, Im asking too much but I really really appreciate any help. Thank you !

    Code :

    if object_id('tempdb..#temp') is not null drop table #temp
    create table #temp
    (
    	ID int identity(1,1) not null primary key
    	, SERL_NBR varchar(100)
    	, SERL_TS datetime
    	, LIN_CUST_ITEM varchar(100)
    )
    
    insert into #temp (SERL_NBR, SERL_TS, LIN_CUST_ITEM)
    select
    	t.SERL_NBR, t.SERL_TS, t.LIN_CUST_ITEM
    from whitbytest.dbo.REPL_SLT t
    WHERE (SERL_TS >DateADD (mi, -510, Current_TimeStamp))
    and (LIN_CUST_ITEM IN ('23147453','23147445'))
    order by t.SERL_TS, t.SERL_NBR
    
    if object_id('tempdb..#temp2') is not null drop table #temp2
    select
    	IDENTITY(int, 1,1) as ID
    	, t1.LIN_CUST_ITEM as PartFrom
    	, t2.LIN_CUST_ITEM as PartTo
    	, t1.SERL_TS as Time1
    	, t2.SERL_TS as Time2
    	, t1.ID as ID1, t2.ID as ID2
    into #temp2
    from #temp t1
    	inner join #temp t2 on t1.ID = t2.ID - 1
    			and t1.LIN_CUST_ITEM <> t2.LIN_CUST_ITEM
    order by t1.ID, t2.ID
    
    select
    	t1.ID, t1.PartFrom, t1.PartTo, tt1.SERL_TS as Time1, tt2.SERL_TS as Time2
    from #temp2 t1
    	left join #temp2 t2 on t1.ID - 1 = t2.ID
    	inner join #temp tt1 on coalesce(t2.ID2, 1) = tt1.ID
    	inner join #temp tt2 on t1.ID1 = tt2.ID
    order by t1.ID

    Results:

    23147445        23147453                      	2013-09-13 06:56:35.000	2013-09-13 07:27:38.000
    23147453        23147445                      	2013-09-13 07:29:41.000	2013-09-13 07:32:39.000
    23147445        23147453                      	2013-09-13 07:34:41.000	2013-09-13 07:34:41.000
    23147453     	23147445                      	2013-09-13 07:35:05.000	2013-09-13 07:54:32.000
    23147445        23147453                      	2013-09-13 07:55:45.000	2013-09-13 07:55:45.000

    Table for this period:

    2013-09-13 07:58:58.000	23147453
    2013-09-13 07:57:31.000	23147453
    2013-09-13 07:56:17.000	23147453
    2013-09-13 07:55:45.000	23147445
    2013-09-13 07:54:32.000	23147453
    2013-09-13 07:46:13.000	23147453
    2013-09-13 07:44:40.000	23147453
    2013-09-13 07:43:12.000	23147453
    2013-09-13 07:41:18.000	23147453
    2013-09-13 07:39:29.000	23147453
    2013-09-13 07:37:51.000	23147453
    2013-09-13 07:36:09.000	23147453
    2013-09-13 07:35:05.000	23147453
    2013-09-13 07:34:41.000	23147445
    2013-09-13 07:32:39.000	23147453
    2013-09-13 07:31:31.000	23147453
    2013-09-13 07:30:30.000	23147453
    2013-09-13 07:29:41.000	23147453
    2013-09-13 07:27:38.000	23147445
    2013-09-13 07:26:37.000	23147445
    2013-09-13 07:25:37.000	23147445
    2013-09-13 07:24:38.000	23147445
    2013-09-13 07:23:33.000	23147445
    2013-09-13 07:22:08.000	23147445
    2013-09-13 07:20:50.000	23147445
    2013-09-13 07:19:46.000	23147445
    2013-09-13 07:18:15.000	23147445


    • Edited by STOL3N Friday, September 13, 2013 12:19 PM
    Friday, September 13, 2013 12:18 PM

  • + Question : Can I even format these two part #s into a group of part #s (for eg: part no 1,3,5,7,9) as left and part #2,4,6,8,10 as right and get results set for changes from left to right vise vera. 

    I don't quite get this. Where is this part no 1,3,5,.. stored?


    Regards, Dean Savović

    Friday, September 13, 2013 12:38 PM
  • Its stored in LIN_CUST_ITEM.

    Above statement you gave me generates a list of changes and timestamp from/to

    23147445        23147453  

    What i want to do is:

    Group some of the part #s in that column into 2 groups

    Group1: 23147445,23147446 etc

    Group2: 23147447,23147448,23147449 etc

    and get similar results within that two groups eg:

    Group1 to Group2 fromtime totime

    Group2 to Group1 fromtime totime

    Friday, September 13, 2013 12:47 PM
  • SELECT  * ,
            CASE WHEN Q.SERL_NBR <> Q.Next_SERL_NBR THEN 1
                 ELSE 0
            END AS SERL_NBR_Changed		
    FROM    ( SELECT    O.* ,
                        ( SELECT TOP 1
                                    I.SERL_NBR
                          FROM      @table I
                          WHERE     I.SERL_TS > O.SERL_TS
                          ORDER BY  I.SERL_TS ASC
                        ) AS Next_SERL_NBR ,
                        ( SELECT TOP 1
                                    I.SERL_TS
                          FROM      @table I
                          WHERE     I.SERL_TS > O.SERL_TS
                          ORDER BY  I.SERL_TS ASC
                        ) AS Next_SERL_TS
              FROM      @table O
            ) Q
    ORDER BY Q.SERL_TS ASC;

    • Marked as answer by STOL3N Monday, September 16, 2013 3:14 PM
    Friday, September 13, 2013 1:59 PM
  • Thanks Stefan !
    Monday, September 16, 2013 3:14 PM