Post reply

Calculate time difference based on Status field

  • May 31, 2016 at 2:27 am

    #326699

    Dear Team,

    My requirement is to calculate the failure time, I just want to know the difference in hours where the status is 'I' and it shouldnt calculate if status is A

    StatusTime-- Status

    5/30/2016 12:30 A

    5/30/2016 13:02 A

    5/30/2016 14:02 I

    5/30/2016 15:02 I

    5/30/2016 19:17 A

    5/30/2016 20:25 A

    5/30/2016 22:02 I

    5/30/2016 23:30 I

    Please help me

  • May 31, 2016 at 3:00 am

    #1882083

    Please post what you've already tried so that we can see what you're struggling with. If it's getting the time difference, use DATEDIFF. If it's getting a corresponding value from a different row, use LEAD or LAG. If it's filtering out the As, use WHERE.

    John

  • May 31, 2016 at 3:17 am

    #1882087

    Hi John

    I tried many queries, Below is one of it. Its gives me the time difference of Max and Min value of statustime where Status = 'I' and i am not able to filter the records where Status = 'A'. Result should only contain the time difference of In Active records

    with cteMax as

    (

    SELECT TOP 1 StatusTime, SiteCode

    FROM [Temp2]

    WHERE [CreateDate] > DateAdd(DAY, -1, GETDATE()) and [CreateDate]<=GETDATE() and SiteCode = '11119'

    and Status = 'I'

    group by Status, StatusTime, siteCode

    order by StatusTime desc

    ), cteMin as

    (

    SELECT TOP 1 StatusTime, SiteCode

    FROM [Temp2]

    WHERE [CreateDate] > DateAdd(DAY, -1, GETDATE()) and [CreateDate]<=GETDATE() and SiteCode = '11119'

    and Status = 'I'

    group by Status, StatusTime, siteCode

    order by StatusTime

    )

    select DATEDIFF(HOUR,MIN(cteMin.StatusTime), MAX(cteMax.StatusTime)), MIN(cteMin.StatusTime), MAX(cteMax.StatusTime) from cteMin

    inner join cteMax on cteMin.SiteCode = cteMax.SiteCode

    Can you suggest some queries

  • May 31, 2016 at 3:23 am

    #1882089

    Your query mentions at least one column that you didn't tell us about in your original post. Please will you post DDL in the form of a CREATE TABLE statement, sample data in the form of INSERT statements, and the results you expect to see based on the sample data?

    Thanks

    John

    Edit - corrected typo

  • May 31, 2016 at 3:23 am

    #1882090

    shagil.a.gopinath (5/31/2016)

    Dear Team,

    My requirement is to calculate the failure time, I just want to know the difference in hours where the status is 'I' and it shouldnt calculate if status is A

    StatusTime-- Status

    5/30/2016 12:30 A

    5/30/2016 13:02 A

    5/30/2016 14:02 I

    5/30/2016 15:02 I

    5/30/2016 19:17 A

    5/30/2016 20:25 A

    5/30/2016 22:02 I

    5/30/2016 23:30 I

    Please help me

    to save confusion....what result are you expecting for the above sample data?

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • May 31, 2016 at 3:41 am

    #1882098

    Hi John,

    CREATE TABLE [dbo].[Temp2](

    [SiteCode] [int] NULL,

    [StatusTime] [datetime] NULL,

    [Status] [char](2) NULL

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    insert into Temp2 values(111,'2016-05-30 12:30:00.000','A')

    insert into Temp2 values(111,'2016-05-30 13:02:00.000','A')

    insert into Temp2 values(111,'2016-05-30 14:02:00.000','I')

    insert into Temp2 values(111,'2016-05-30 15:02:00.000','I')

    insert into Temp2 values(112,'2016-05-30 19:17:00.000','A')

    insert into Temp2 values(112,'2016-05-30 20:25:00.000','A')

    insert into Temp2 values(112,'2016-05-30 21:02:00.000','I')

    insert into Temp2 values(112,'2016-05-30 23:02:00.000','I')

    Consider I as InActive and A as Active, So if i pull the records for 'I' so the Result expected for the code 111 is 1 Hour as and for the code 112 its 2 hours, My real scenario is different I just put sample table and records

  • May 31, 2016 at 3:47 am

    #1882099

    Hi,

    CREATE TABLE [dbo].[Temp2](

    [SiteCode] [int] NULL,

    [StatusTime] [datetime] NULL,

    [Status] [char](2) NULL

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    insert into Temp2 values(111,'2016-05-30 12:30:00.000','A')

    insert into Temp2 values(111,'2016-05-30 13:02:00.000','A')

    insert into Temp2 values(111,'2016-05-30 14:02:00.000','I')

    insert into Temp2 values(111,'2016-05-30 15:02:00.000','I')

    insert into Temp2 values(112,'2016-05-30 19:17:00.000','A')

    insert into Temp2 values(112,'2016-05-30 20:25:00.000','A')

    insert into Temp2 values(112,'2016-05-30 21:02:00.000','I')

    insert into Temp2 values(112,'2016-05-30 23:02:00.000','I')

    Consider I as InActive and A as Active, So if i pull the records for 'I' , Result expected for the code 111 is 1 Hour and for the code 112 its 2 hours, My real scenario is different I just put sample table and records

  • May 31, 2016 at 4:16 am

    #1882109

    Onthe assumption (dangerous I know!) that the inactive records always appear as two consecutive rows when ordered by site/datetime then the following mayhelp

    CREATE TABLE [dbo].[Temp2](

    [SiteCode] [int] NULL,

    [StatusTime] [datetime] NULL,

    [Status] [char](2) NULL

    )

    insert into Temp2 values(111,'2016-05-30 12:30:00.000','A')

    insert into Temp2 values(111,'2016-05-30 13:02:00.000','A')

    insert into Temp2 values(111,'2016-05-30 14:02:00.000','I')

    insert into Temp2 values(111,'2016-05-30 15:02:00.000','I')

    insert into Temp2 values(112,'2016-05-30 19:17:00.000','A')

    insert into Temp2 values(112,'2016-05-30 20:25:00.000','A')

    insert into Temp2 values(112,'2016-05-30 21:02:00.000','I')

    insert into Temp2 values(112,'2016-05-30 23:02:00.000','I')

    --added more rows to test for multiple inactive sessions per site

    insert into Temp2 values(113,'2016-05-30 02:02:00.000','A')

    insert into Temp2 values(113,'2016-05-30 03:06:00.000','I')

    insert into Temp2 values(113,'2016-05-30 04:00:00.000','I')

    insert into Temp2 values(113,'2016-05-30 08:06:00.000','A')

    insert into Temp2 values(113,'2016-05-30 10:02:00.000','A')

    insert into Temp2 values(113,'2016-05-30 12:12:00.000','I')

    insert into Temp2 values(113,'2016-05-30 14:12:00.000','I')

    insert into Temp2 values(113,'2016-05-30 15:02:00.000','A');

    WITH cte as (

    SELECT sitecode,

    StatusTime,

    status,

    lag(statustime, 1) OVER(PARTITION BY sitecode ORDER BY statustime) st_lag,

    lag(status, 1) OVER(PARTITION BY sitecode ORDER BY statustime) s_lag

    FROM temp2

    )

    SELECT sitecode,

    st_lag start_inactive,

    StatusTime end_Inactive,

    DATEDIFF(hour, st_lag, statustime) Inactive_hours

    FROM cte

    WHERE(status = 'I') AND (s_lag = 'I');

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • May 31, 2016 at 4:37 am

    #1882113

    I trust you understand that DATEDIFF returns the count of the specified datepart boundaries crossed between the specified startdate and enddate.

    eg...even though the start and end times below are only 2 minutes apart, datediff for hours returns 1

    DECLARE @start datetime = '2016-05-30 09:59:00.000',

    @end datetime = '2016-05-30 10:01:00.000'

    SELECT DATEDIFF(hour,@start,@end)

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • May 31, 2016 at 7:59 am

    #1882154

    Hi John

    Still its not meet my requirement, i need only the total hours as result. In the below query i get the total hours but i am not able to filter only the status = "I"

    with cteMax as

    (

    SELECT TOP 1 StatusTime, SiteCode

    FROM [Temp2]

    WHERE [CreateDate] > DateAdd(DAY, -1, GETDATE()) and [CreateDate]<=GETDATE() and SiteCode = '11119'

    and Status = 'I'

    group by Status, StatusTime, siteCode

    order by StatusTime desc

    ), cteMin as

    (

    SELECT TOP 1 StatusTime, SiteCode

    FROM [Temp2]

    WHERE [CreateDate] > DateAdd(DAY, -1, GETDATE()) and [CreateDate]<=GETDATE() and SiteCode = '11119'

    and Status = 'I'

    group by Status, StatusTime, siteCode

    order by StatusTime

    )

    select DATEDIFF(HOUR,MIN(cteMin.StatusTime), MAX(cteMax.StatusTime)), MIN(cteMin.StatusTime), MAX(cteMax.StatusTime) from cteMin

    inner join cteMax on cteMin.SiteCode = cteMax.SiteCode

  • May 31, 2016 at 8:19 am

    #1882162

    shagil.a.gopinath (5/31/2016)

    Hi John

    Still its not meet my requirement, i need only the total hours as result. In the below query i get the total hours but i am not able to filter only the status = "I"

    with cteMax as

    (

    SELECT TOP 1 StatusTime, SiteCode

    FROM [Temp2]

    WHERE [CreateDate] > DateAdd(DAY, -1, GETDATE()) and [CreateDate]<=GETDATE() and SiteCode = '11119'

    and Status = 'I'

    group by Status, StatusTime, siteCode

    order by StatusTime desc

    ), cteMin as

    (

    SELECT TOP 1 StatusTime, SiteCode

    FROM [Temp2]

    WHERE [CreateDate] > DateAdd(DAY, -1, GETDATE()) and [CreateDate]<=GETDATE() and SiteCode = '11119'

    and Status = 'I'

    group by Status, StatusTime, siteCode

    order by StatusTime

    )

    select DATEDIFF(HOUR,MIN(cteMin.StatusTime), MAX(cteMax.StatusTime)), MIN(cteMin.StatusTime), MAX(cteMax.StatusTime) from cteMin

    inner join cteMax on cteMin.SiteCode = cteMax.SiteCode

    assuming you are replying to me (my name is not John by the way)

    the code I gave you gives the results you asked for based on your sample data (Result expected for the code 111 is 1 Hour and for the code 112 its 2 hours,)

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • June 1, 2016 at 12:07 pm

    #1882588

    ; WITH MyData AS

    (

    SELECT

    SiteCode,

    RowSeq = row_number() --- enumerate StatusTime

    OVER (PARTITION BY SiteCode

    ORDER BY StatusTime)

    , StatusTime, [Status]

    FROM [Temp2]

    )

    SELECT---- Show two consecutive states next to each other

    SiteCode = A.SiteCode

    , FromRow = A.RowSeq

    , ToRow = B.RowSeq

    , FromStatus = A.[Status]

    , ToStatus = B.[Status]

    , FromTime = A.StatusTime

    , ToTime = B.StatusTime

    -- and calculate difference in seconds, or convert it to minutes or hours:

    , TimeDiff_Seconds = datediff (s,A.StatusTime,B.StatusTime)

    , TimeDiff_Minutes = 1.0 * datediff (s,A.StatusTime,B.StatusTime)/60

    , TimeDiff_Hours = 1.0 * datediff (s,A.StatusTime,B.StatusTime)/3600

    FROM MyData AS A

    JOIN MyData AS B ON A.RowSeq = B.RowSeq-1 AND A.SiteCode = B.SiteCode

    ORDER BY A.SiteCode, A.RowSeq

    ;

    The result of this T-SQL statement is:

    SiteCode FromRow ToRow FromStatus ToStatus FromTime ToTime Seconds Minutes Hours

    ----------- -------- ----- ---------- -------- ---------------- ---------------- ------- ---------- --------

    111 1 2 A A 2016-05-30 12:30 2016-05-30 13:02 1920 32.00 0.533333

    111 2 3 A I 2016-05-30 13:02 2016-05-30 14:02 3600 60.00 1.000000

    111 3 4 I I 2016-05-30 14:02 2016-05-30 15:02 3600 60.00 1.000000

    112 1 2 A A 2016-05-30 19:17 2016-05-30 20:25 4080 68.00 1.133333

    112 2 3 A I 2016-05-30 20:25 2016-05-30 21:02 2220 37.00 0.616666

    112 3 4 I I 2016-05-30 21:02 2016-05-30 23:02 7200 120.00 2.000000

    (6 row(s) affected)

    I believe you can figure out the rest...

  • June 4, 2016 at 12:06 pm

    #1883261

    Thank you so much, This is the perfect solution 🙂

    Hats off

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy