CODEWITHSUNDEEP
sql-server-2008
Kapil
Kapil

Reputation: 1931

Compare the next row in the sql server

I have a scenerio from the below table

1) Column Name : Operation_Name = 'SEEDER' and the immediate next row Operation_Name = 'AUSEAL' mean

2) In the below table I need to add one more column Name Process_Time should be timestamp2 of AUSEAL - timestamp2 of SEEDER should display as process_Time

3) When Operation_Name 'SEEDER' and 'AUSEAL' come one after other, then only we need to calculate the process_time otherwise Process_time should be '0'

Select Statement for the below table is :

select * from (
    select
        ROW_NUMBER() over(Order by timestamp2) ROW ,*
   from [dbo].[HIST_ACTIVITY_LOG3] where serial_no ='WCC1S8530252') t 



    Row   Serialno     timestamp2   Route_name      Operation_Name    WorkStat  Dis_Name host_Name 
    25  WCC1S8530252    2014-03-15 04:18:11 R1044   SEEDER  IN_PROCESS  PULL    MTB4AS-STW01    ASSPW 2.09.01   START   2014-03-14 12:06:58 STW 1049D   PRIME   WD10EZEX-00ZF5  TRESSELS    HDA-B43 WD10EZEX-00ZF5  HDA-B43 9311B   58273
    26  WCC1S8530252    2014-03-15 04:26:09 R1044   AUSEAL  QUEUED  PASS    MTB4AS-STW02    ASSPW 2.09.01   COMPLETE    2014-03-15 04:18:11 SEEDER  1049D   PRIME   WD10EZEX-00ZF5  TRESSELS    HDA-B43 WD10EZEX-00ZF5  HDA-B43 1049D   478
    27  WCC1S8530252    2014-03-15 05:15:48 R1044   INL_LEAK    QUEUED  PASS    MTB4BCI-ATS01   BCI 9.38.00 COMPLETE    2014-03-15 04:26:09 AUSEAL  9304B   PRIME   WD10EZEX-00ZF5  TRESSELS    HDA-B43 WD10EZEX-00ZF5  HDA-B43 1049D   2979
    28  WCC1S8530252    2014-03-15 05:16:08 R1044   INL_LEAK    IN_PROCESS  NULL    MTB4AS-LKT02    ASSML 2.01.00   START   2014-03-15 05:15:47 INL_LEAK    RT-3713 PRIME   WD10EZEX-00ZF5  TRESSELS    HDA-B43 WD10EZEX-00ZF5  HDA-B43 9304B   20
    29  WCC1S8530252    2014-03-15 05:16:17 R1044   PCBA    QUEUED  PASS    MTB4AS-LKT02    ASSML 2.01.00   COMPLETE    2014-03-15 05:16:17 INL_LEAK    RT-3713 PRIME   WD10EZEX-00ZF5  TRESSELS    HDA-B43 WD10EZEX-00ZF5  HDA-B43 RT-3713 0
    30  WCC1S8530252    2014-03-15 05:16:17 R1044   INL_LEAK    IN_PROCESS  NULL    MTB4AS-LKT02    ASSML 2.01.00   START   2014-03-15 05:16:08 INL_LEAK    RT-3713 PRIME   WD10EZEX-00ZF5  TRESSELS    HDA-B43 WD10EZEX-00ZF5  HDA-B43 RT-3713 9
    31  WCC1S8530252    2014-03-15 05:52:10 R1044   PCBA    IN_PROCESS  NULL    MTB4AS-PCB04    ASCR 2.54.00    START   2014-03-15 05:16:17 PCBA    9304B   PRIME   WD10EZEX-00ZF5  TRESSELS    DRV-B42 WD10EZEX-00ZF5  HDA-B43 RT-3713 2153
    32  WCC1S8530252    2014-03-15 05:52:29 R1044   XFILLER QUEUED  MOVE    MTB4AS-PCB04    ASCR 2.54.00    MOVE    2014-03-15 05:52:29 PCBA    9304B   PRIME   WD10EZEX-00ZF5  TRESSELS    DRV-B42 WD10EZEX-00ZF5  DRV-B42 9304B   19
    33  WCC1S8530252    2014-03-15 06:17:26 R1044   HELCRG  IN_PROCESS  PULL    MTB4AS-HEL02    AS 1.98.05  START   2014-03-15 05:52:29 XFILLER RT-02111    PRIME   WD10EZEX-00ZF5  TRESSELS    DRV-B42 WD10EZEX-00ZF5  DRV-B42 9304B   1497
    34  WCC1S8530252    2014-03-15 06:19:58 R1044   HELSEALI    QUEUED  PASS    MTB4AS-HEL02    AS 1.98.05  COMPLETE    2014-03-15 06:17:26 HELCRG  RT-02111    PRIME   WD10EZEX-00ZF5  TRESSELS    DRV-B42 WD10EZEX-00ZF5  DRV-B42 RT-02111    152
    35  WCC1S8530252    2014-03-15 06:27:02 R1044   HELSEALI    IN_PROCESS  NULL    MTB4AS-HEL04    AS 1.98.05  START   2014-03-15 06:19:58 HELSEALI    RT-0401 PRIME   WD10EZEX-00ZF5  TRESSELS    DRV-B42 WD10EZEX-00ZF5  DRV-B42 RT-02111    424

Upvotes: 0

Views: 44

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

You can fetch the next value in an outer apply.

select H1.Serialno,
       H1.timestamp2,
       H1.Route_name,
       H1.Operation_Name,
       isnull(datediff(second, H1.timestamp2, LeadH.timestamp2), 0) as TimeStampDiffSec
from HIST_ACTIVITY_LOG3 as H1
  outer apply (
              select top(1) H2.timestamp2
              from HIST_ACTIVITY_LOG3 as H2
              where H2.Operation_Name = 'AUSEAL' and
                    H1.Operation_Name = 'SEEDER' and
                    H2.timestamp2 > H1.timestamp2
              order by H2.timestamp2
              ) as LeadH

Result:

Serialno     timestamp2              Route_name Operation_Name TimeStampDiffSec
------------ ----------------------- ---------- -------------- ----------------
WCC1S8530252 2014-03-15 04:18:11.000 R1044      SEEDER         478
WCC1S8530252 2014-03-15 04:26:09.000 R1044      AUSEAL         0
WCC1S8530252 2014-03-15 05:15:48.000 R1044      INL_LEAK       0
WCC1S8530252 2014-03-15 05:16:08.000 R1044      INL_LEAK       0
WCC1S8530252 2014-03-15 05:16:17.000 R1044      PCBA           0
WCC1S8530252 2014-03-15 05:16:17.000 R1044      INL_LEAK       0
WCC1S8530252 2014-03-15 05:52:10.000 R1044      PCBA           0
WCC1S8530252 2014-03-15 05:52:29.000 R1044      XFILLER        0
WCC1S8530252 2014-03-15 06:17:26.000 R1044      HELCRG         0
WCC1S8530252 2014-03-15 06:19:58.000 R1044      HELSEALI       0
WCC1S8530252 2014-03-15 06:27:02.000 R1044      HELSEALI       0

Upvotes: 1

Related Questions