none
T-SQL help required for the below scenario

    Question

  • Hi Guys,

    I would require some help on T-SQL to achieve the following, Data appears in the table as  below

    Alarm   Terminal     Severity    Record_Timestamp

    10         90020             1             2010-07-19 04:00:00 AM

    10         90020             1             2010-07-19 05:00:00 AM

    10         90020             1             2010-07-19 06:00:00 AM

    10         90020             5             2010-07-19 07:00:00 AM

    10         90020             1             2010-07-19 08:00:00 AM

    10         90020             1             2010-07-19 09:00:00 AM

    10         90020             5             2010-07-19 10:00:00 AM

    Now from the above data For Alarm id 10 if severity is 1 then it is down and if severity is 5 then it is up. Now the output from the above data should be

     

    Alarm Id     Terminal     Severity      Down Time                                   Up Time

    10                90020             1             2010-07-19 04:00:00 AM           2010-07-19 07:00:00 AM

    10                 90020             1             2010-07-19 08:00:00 AM          2010-07-19 10:00:00 AM

     

    Please note though there are multiple downs we should consider only the first one and also once the alarm down and up are captured the next down should be greater than the previous up of the same alarm.

    Let me know if anything unclear on the above scenario.


    Raju

    Raju
    Tuesday, July 20, 2010 8:10 AM

Answers

  • use tempdb

    another solution:

    use tempdb

    create table test (Alarm int null, Terminal int null, Severity int null, Record_Timestamp datetime null)

    insert into test
    values(10, 90020, 1, convert(datetime,'2010-07-19 04:00:00',120)),
    (10, 90020, 1, convert(datetime,'2010-07-19 05:00:00',120)),
    (10, 90020, 5, convert(datetime,'2010-07-19 07:00:00',120)),
    (10, 90020, 1, convert(datetime,'2010-07-19 08:00:00',120)),
    (10, 90020, 1, convert(datetime,'2010-07-19 09:00:00',120)),
    (10, 90020, 5, convert(datetime,'2010-07-19 10:00:00',120))

    ;with cte (Alarm,Terminal, Severity,Down,UP) as
    (select Alarm,Terminal,Severity,Record_Timestamp as Down,
    (select top 1 Record_Timestamp from test where a.Alarm = Alarm and a.Terminal = Terminal and a.Record_Timestamp < Record_Timestamp and Severity = 5) as Up
    from test a
    where Severity = 1)
    select Alarm,  Terminal,   Severity,  min(Down) as down, UP from cte
    group by Alarm,  Terminal,   Severity, UP

    Tuesday, July 20, 2010 9:08 AM

All replies

  • Hi Guys,

    I would require some help on T-SQL to achieve the following, Data appears in the table as  below

    Alarm   Terminal     Severity    Record_Timestamp

    10         90020             1             2010-07-19 04:00:00 AM

    10         90020             1             2010-07-19 05:00:00 AM

    10         90020             1             2010-07-19 06:00:00 AM

    10         90020             5             2010-07-19 07:00:00 AM

    10         90020             1             2010-07-19 08:00:00 AM

    10         90020             1             2010-07-19 09:00:00 AM

    10         90020             5             2010-07-19 10:00:00 AM

    Now from the above data For Alarm id 10 if severity is 1 then it is down and if severity is 5 then it is up. Now the output from the above data should be

     

    Alarm Id     Terminal     Severity      Down Time                                   Up Time

    10                90020             1             2010-07-19 04:00:00 AM           2010-07-19 07:00:00 AM

    10                 90020             1             2010-07-19 08:00:00 AM          2010-07-19 10:00:00 AM

     

    Please note though there are multiple downs we should consider only the first one and also once the alarm down and up are captured the next down should be greater than the previous up of the same alarm.

    Let me know if anything unclear on the above scenario.


    Raju
    • Moved by Darren Green SQLIS Tuesday, July 20, 2010 8:14 AM Moved as requested (From:SQL Server Integration Services)
    • Merged by KJian_ Wednesday, July 21, 2010 9:06 AM duplicate
    Tuesday, July 20, 2010 8:06 AM
  • Sorry guys wrong forum, can some one move it into T-SQL
    Raju
    Tuesday, July 20, 2010 8:09 AM
  • create

     

    table #tmp (Alarm int,Terminal int,Severity int,Record_Timestamp datetime)

    insert

     

    into #tmp values (10,90020, 1,'2010-07-19 04:00:00')

    insert

     

    into #tmp values (10,90020, 1,'2010-07-19 05:00:00')

    insert

     

    into #tmp values (10,90020, 1,'2010-07-19 06:00:00')

    insert

     

    into #tmp values (10,90020, 5,'2010-07-19 07:00:00')

    insert

     

    into #tmp values (10,90020, 1,'2010-07-19 08:00:00')

    insert

     

    into #tmp values (10,90020, 1,'2010-07-19 09:00:00')

    insert

     

    into #tmp values (10,90020, 5,'2010-07-19 10:00:00')

    alter

     

    table #tmp add id int identity(1,1)

    with

     

    cte

    as

    (

    SELECT

     

    alarm,Terminal,Record_Timestamp,NTILE(2) OVER(ORDER BY Record_Timestamp ) AS tile

    FROM

     

    #tmp t

    )

     

    select alarm,Terminal,min(Record_Timestamp),max(Record_Timestamp)

    from

     

    cte group by tile,alarm,Terminal


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, July 20, 2010 8:49 AM
  • use tempdb

    another solution:

    use tempdb

    create table test (Alarm int null, Terminal int null, Severity int null, Record_Timestamp datetime null)

    insert into test
    values(10, 90020, 1, convert(datetime,'2010-07-19 04:00:00',120)),
    (10, 90020, 1, convert(datetime,'2010-07-19 05:00:00',120)),
    (10, 90020, 5, convert(datetime,'2010-07-19 07:00:00',120)),
    (10, 90020, 1, convert(datetime,'2010-07-19 08:00:00',120)),
    (10, 90020, 1, convert(datetime,'2010-07-19 09:00:00',120)),
    (10, 90020, 5, convert(datetime,'2010-07-19 10:00:00',120))

    ;with cte (Alarm,Terminal, Severity,Down,UP) as
    (select Alarm,Terminal,Severity,Record_Timestamp as Down,
    (select top 1 Record_Timestamp from test where a.Alarm = Alarm and a.Terminal = Terminal and a.Record_Timestamp < Record_Timestamp and Severity = 5) as Up
    from test a
    where Severity = 1)
    select Alarm,  Terminal,   Severity,  min(Down) as down, UP from cte
    group by Alarm,  Terminal,   Severity, UP

    Tuesday, July 20, 2010 9:08 AM
  • Another try

    WITH

     

    cte

    AS

    (

    SELECT

     

    *,CASE WHEN Severity =1

     

    THEN (SELECT min(Record_Timestamp)

     

    FROM #tmp

     

    WHERE ID >= t.ID AND Severity =5)

     

    ELSE (SELECT max(Record_Timestamp)

     

    FROM #tmp

     

    WHERE ID <= t.ID)

     

    END AS X

    FROM

     

    #tmp t

    )

     

    SELECT alarm,Terminal,X, MIN(Record_Timestamp)

     

    FROM cte

     

    GROUP BY alarm,Terminal,X


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, July 20, 2010 9:27 AM
  • Hi Christa,

    Thank you very much, couldn't have asked better than your solution. It works perfectly.


    Raju
    Tuesday, July 20, 2010 9:42 AM
  • Guys please close this thread as this has been already answered in another thread.
    Raju
    Tuesday, July 20, 2010 10:39 AM
  • Take a look at this blog post

    http://pratchev.blogspot.com/2010/02/refactoring-ranges.html


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, July 21, 2010 3:43 AM