Forum Discussion

Oded-Dror's avatar
Oded-Dror
Helper III
2 years ago

Time interval

Hi there, See image below. In the left I have SQL Server table called example with two ID's  And DTTM columns. I need it to capture 15 running min interval with reseting the loop (every 15 min set...
Need Help
AmiraBedh's avatar
AmiraBedh
Super User
2 years ago

But I need the dataset at least.

Oded-Dror's avatar
Oded-Dror
Helper III
2 years ago

Amira,
Here is the TSQL code I used

drop table if exists example

CREATE TABLE example(
[ID] [varchar](9) NULL,
[DTTM] [datetime] NULL

) ON [PRIMARY]

Insert into [example] (ID,DTTM) values
('123456789','2017-10-05 08:00:00.000'), -- Expected Result
('123456789','2017-10-05 08:05:00.000'),
('123456789','2017-10-05 08:07:00.000'),
('123456789','2017-10-05 08:15:00.000'), -- Expected Result
('123456789','2017-10-05 08:25:00.000'),
('123456789','2017-10-05 10:12:00.000') , -- Expected Result
('123456789','2017-10-05 10:26:00.000'),
('123456789','2017-10-05 10:32:00.000'), -- Expected Result
('123456789','2017-10-05 10:33:00.000'),
('123456789','2017-10-05 10:34:00.000'),
('123456789','2017-10-05 10:35:00.000'),
('123456789','2017-10-05 10:36:00.000'),
('123456789','2017-10-05 10:37:00.000'),
('123456789','2017-10-05 10:38:00.000'),
('123456789','2017-10-05 10:39:00.000'),
('123456789','2017-10-05 10:40:00.000'),
('123456789','2017-10-05 10:41:00.000'),
('123456789','2017-10-05 10:42:00.000'),
('123456789','2017-10-05 10:43:00.000'),
('123456789','2017-10-05 10:44:00.000'),
('123456789','2017-10-05 10:45:00.000'),
('123456789','2017-10-05 10:46:00.000'),
('123456789','2017-10-05 10:47:00.000'), -- Expected Result
('123456789','2017-10-05 10:48:00.000')


Insert into [example] (ID,DTTM) values
('789','2017-10-05 08:00:00.000'), -- Expected Result
('789','2017-10-05 08:05:00.000'),
('789','2017-10-05 08:07:00.000'),
('789','2017-10-05 08:15:00.000'), -- Expected Result
('789','2017-10-05 08:25:00.000'),
('789','2017-10-05 10:12:00.000') , -- Expected Result
('789','2017-10-05 10:26:00.000'),
('789','2017-10-05 10:32:00.000'), -- Expected Result
('789','2017-10-05 10:33:00.000'),
('789','2017-10-05 10:34:00.000'),
('789','2017-10-05 10:35:00.000'),
('789','2017-10-05 10:36:00.000'),
('789','2017-10-05 10:37:00.000'),
('789','2017-10-05 10:38:00.000'),
('789','2017-10-05 10:39:00.000'),
('789','2017-10-05 10:40:00.000'),
('789','2017-10-05 10:41:00.000'),
('789','2017-10-05 10:42:00.000'),
('789','2017-10-05 10:43:00.000'),
('789','2017-10-05 10:44:00.000'),
('789','2017-10-05 10:45:00.000'),
('789','2017-10-05 10:46:00.000'),
('789','2017-10-05 10:47:00.000'), -- Expected Result
('789','2017-10-05 10:48:00.000')

 

Thanks,

Oded Dror

  • gmsamborn's avatar
    gmsamborn
    Super User
    2 years ago

    Hi Oded-Dror 

     

    I added a time dimension with 1 row per minute.  It also has a 15-minute bucket.  (I added a DimDate table.)

     

    After adding a couple of relationships and measures, my model is below.

     

    DimTime - 15 minute buckets.pbix

     

    Let me know if you have any questions.

     

    • Oded-Dror's avatar
      Oded-Dror
      Helper III
      2 years ago

      Amira,
      Thank you for solving this issue, it works! 

      Point to remember DAX dateadd func dosen't have Minute interval like SQL dose.
      Thanks again

      Oded Dror

      • Oded-Dror's avatar
        Oded-Dror
        Helper III
        2 years ago

        Amira,

         

        This is not accurate if you see in your result 10:26 - 10:12 is 14 min and not 15
        And the last record as well

         

        Thanks,

        Oded Dror

         

  • Ahmedx's avatar
    Ahmedx
    Super User
    2 years ago

    Please check this '789','2017-10-05 10:32:00.000')
    ('789','2017-10-05 10:47:00.000'
    does not fall within the 15 minute interval

     

    • Oded-Dror's avatar
      Oded-Dror
      Helper III
      2 years ago

      The running total is reseting every 15 min. where is 10:32 (10:32 - 10:12 = 20)