none
calculating time interval RRS feed

  • Question

  • Hi there,

    I have a table looks like this

    CREATE TABLE [dbo].[example](
           [ID] [varchar](9) NULL,
           [DTTM] [datetime] NULL
    ) ON [PRIMARY]

    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 08:00:00.000')
    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 08:05:00.000')
    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 08:07:00.000')
    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 08:15:00.000')
    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 08:25:00.000')
    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 09:00:00.000')
    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 09:01:00.000')
    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 09:02:00.000')
    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 09:03:00.000')
    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 11:00:00.000')

    I need to create a flag based on time > = 15 from the first row until the next row met and it become the first row
    So in this case row 1,4,6 and 10 will get 1 the rest will get 0 (for counting) the date it doesn't matter 
    Because I have a date range for month and multiple IDs so grouping is needed also or windows function

    I tried to do something like this but it doesn't solve the challenge (maybe dynamic pivot will help?)

    ;With myLead
    AS
    (
    Select        top 100 percent
                ID,
                DTTM,               
                LEAD(DTTM,1) over (partition by ID order by DTTM) as NextDTTM
    From        Example
    Order by    ID
    ), myCount
    AS
    (
    Select      Top 100 percent
       ID,
       DTTM,
       NextDTTM,
       DateDiff("MINUTE",DTTM,NextDTTM) as Interval
    from        myLead
    )
    Select      ID,
                DTTM,
                NextDTTM,
                Interval,
                Case When Interval >= 15 then 1 else 0 END as CountFlag
    From        myCount
    --Where       Interval is not NULL
    Order by    ID

    How do I solve this challenge?

    Thanks,

    Oded Dror


    Thanks, Oded Dror

    Friday, October 6, 2017 4:22 AM

Answers

  • Here is another approach with recursive CTE:

    CREATE TABLE [dbo].[example](
           [ID] [varchar](9) NULL,
           [DTTM] [datetime] NULL
    ) ON [PRIMARY]
    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 08:00:00.000')
    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 08:05:00.000')
    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 08:07:00.000')
    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 08:15:00.000')
    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 08:25:00.000')
    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 09:00:00.000')
    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 09:01:00.000')
    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 09:02:00.000')
    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 09:03:00.000')
    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 11:00:00.000')
    
    
    ;with src as (
    Select row_number() Over(Partition by ID Order by DTTM) rn
    , * from [dbo].[example]
    )
    --resursive cte
    ,mycte  as (
    Select rn, ID, DTTM, DTTM as DTTM2  from src where rn=1
    union all
    Select s.rn, s.ID, s.DTTM
    , Case when datediff(minute,m.DTTM2, s.DTTM)>=15 then s.DTTM else m .DTTM2 end   
      
    from mycte  m  join src s on s.DTTM>m .DTTM and s.rn=m.rn+1
    )
    
    Select ID, DTTM, 
    Case when row_number() Over(Partition by ID, DTTM2 Order by DTTM)=1 
    then 1 else 0 end CountFlag
    from mycte 
    
    
    
    drop table [dbo].[example]

    • Marked as answer by odeddror Thursday, October 12, 2017 3:44 AM
    Wednesday, October 11, 2017 2:18 PM
    Moderator

All replies

  • Hi odeddror,

    Please try this.

    CREATE TABLE [dbo].[example1](
            [ID] [varchar](9) NULL,
            [DTTM] [datetime] NULL
     ) ON [PRIMARY]
    
    
    Insert into [example1] (ID,DTTM) values ('123456789','2017-10-05 08:00:00.000')
     Insert into [example1] (ID,DTTM) values ('123456789','2017-10-05 08:05:00.000')
     Insert into [example1] (ID,DTTM) values ('123456789','2017-10-05 08:07:00.000')
     Insert into [example1] (ID,DTTM) values ('123456789','2017-10-05 08:15:00.000')
     Insert into [example1] (ID,DTTM) values ('123456789','2017-10-05 08:25:00.000')
     Insert into [example1] (ID,DTTM) values ('123456789','2017-10-05 09:00:00.000')
     Insert into [example1] (ID,DTTM) values ('123456789','2017-10-05 09:01:00.000')
     Insert into [example1] (ID,DTTM) values ('123456789','2017-10-05 09:02:00.000')
     Insert into [example1] (ID,DTTM) values ('123456789','2017-10-05 09:03:00.000')
     Insert into [example1] (ID,DTTM) values ('123456789','2017-10-05 11:00:00.000')
    
     ;With myLead
     AS
     (
     Select        top 100 percent 
                 ID,
                 DTTM,                
                 lag(DTTM,1,DTTM) over (partition by ID order by DTTM) as Last_DTTM
     From        Example1
     Order by    ID
     )
     , 
     myCount
     AS
     (
     Select      Top 100 percent
        ID,
        DTTM,
        Last_DTTM,
    	DateDiff("MINUTE",Last_DTTM,DTTM) as Interval,
        sum(DateDiff("MINUTE",Last_DTTM,DTTM)) over (partition by ID order by DTTM)/15 as cal_Interval
     from        myLead
     )
     Select      ID,
                 DTTM,
                 Last_DTTM,
                 Interval,
    			 case when lag(cal_Interval,1) over (partition by ID order by DTTM) is null or lag(cal_Interval,1) over (partition by ID order by DTTM)!=cal_Interval then 1 else 0 end as CountFlag
                 --Case When Interval >= 15 then 1 else 0 END as CountFlag
     From        myCount
     --Where       Interval is not NULL
     Order by    ID

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by odeddror Friday, October 6, 2017 12:49 PM
    • Unmarked as answer by odeddror Wednesday, October 11, 2017 3:39 AM
    Friday, October 6, 2017 5:56 AM
    Moderator
  • Will,

    Thank you for the quick response it works as expected.
    Where I can find some examples about all nuances of Lead and Windows function like you represent in your code?

    P.S I have the book of Itzik Ben-Gan about the Windows function

    Thanks,

    Oded Dror


    Thanks, Oded Dror


    • Edited by odeddror Wednesday, October 11, 2017 3:42 AM still not working
    Friday, October 6, 2017 12:54 PM
  • Will,

    Please try this set it doesn't seems to be working line 7 interval 14 should be 0

    Thanks,

    Oded Dror

    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 08:00:00.000')
    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 08:05:00.000')
    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 08:07:00.000')
    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 08:15:00.000')
    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 08:25:00.000')
    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 10:12:00.000')
    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 10:26:00.000')
    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 10:32:00.000')
    --Select * from Example

    ;With myLead
    AS
    (
    Select        top 100 percent
                         ID,
                         DTTM,               
                         lag(DTTM,1,DTTM) over (partition by ID order by DTTM) as Last_DTTM
    From       example
    Order by   ID
    ), myCount
    AS
    (
    Select        Top 100 percent
                ID,
                DTTM,
                Last_DTTM,
                DateDiff("MINUTE",Last_DTTM,DTTM) as Interval,
                sum(DateDiff("MINUTE",Last_DTTM,DTTM)) over (partition by ID order by DTTM)/15 as cal_Interval
    from       myLead
    )
    Select        Top 100 percent
                ID,
                DTTM,
                Last_DTTM,
                Interval,
                         case   when lag(cal_Interval,1) over (partition by ID order by DTTM) is null
                               or
                                             lag(cal_Interval,1) over (partition by ID order by DTTM)!=cal_Interval then 1 else 0
                         end as CountFlag
    From        myCount
    Order by    ID


    Thanks, Oded Dror

    Wednesday, October 11, 2017 3:41 AM
  • Will,

    Thank you for the quick response it works as expected.
    Where I can find some examples about all nuances of Lead and Windows function like you represent in your code?

    P.S I have the book of Itzik Ben-Gan about the Windows function

    Thanks,

    Oded Dror


    Thanks, Oded Dror


    Hi Oded,

    Sorry for delayed response. I just got back today.

    In fact, the key here is to understand how to use window functions. based on this, you could figure out your expected result by combining to use multiple functions. In other words, it depends on your usual accumulation.

    If you want to know more about it, you may read the following articles.

    https://docs.microsoft.com/en-us/sql/t-sql/functions/lead-transact-sql

    https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 11, 2017 5:42 AM
    Moderator
  • Will,

    Thank you and a mean time I open the ticket again because it still not show the result as expected

    Its work fine on 15 min time but when it comes to 14 or 13 min it still show 1

    Please take a look at the sample I sent you.

    Thanks again

    Oded Dror

     


    Thanks, Oded Dror

    Wednesday, October 11, 2017 12:50 PM
  • Here is another approach with recursive CTE:

    CREATE TABLE [dbo].[example](
           [ID] [varchar](9) NULL,
           [DTTM] [datetime] NULL
    ) ON [PRIMARY]
    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 08:00:00.000')
    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 08:05:00.000')
    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 08:07:00.000')
    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 08:15:00.000')
    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 08:25:00.000')
    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 09:00:00.000')
    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 09:01:00.000')
    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 09:02:00.000')
    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 09:03:00.000')
    Insert into [example] (ID,DTTM) values ('123456789','2017-10-05 11:00:00.000')
    
    
    ;with src as (
    Select row_number() Over(Partition by ID Order by DTTM) rn
    , * from [dbo].[example]
    )
    --resursive cte
    ,mycte  as (
    Select rn, ID, DTTM, DTTM as DTTM2  from src where rn=1
    union all
    Select s.rn, s.ID, s.DTTM
    , Case when datediff(minute,m.DTTM2, s.DTTM)>=15 then s.DTTM else m .DTTM2 end   
      
    from mycte  m  join src s on s.DTTM>m .DTTM and s.rn=m.rn+1
    )
    
    Select ID, DTTM, 
    Case when row_number() Over(Partition by ID, DTTM2 Order by DTTM)=1 
    then 1 else 0 end CountFlag
    from mycte 
    
    
    
    drop table [dbo].[example]

    • Marked as answer by odeddror Thursday, October 12, 2017 3:44 AM
    Wednesday, October 11, 2017 2:18 PM
    Moderator