Query to Group by only if more than 1 minute

Transact-SQL
1

Hi,
Create Table dbo.#test
(
date datetime,
Person varchar(25),
Type varchar(25),
time time
)

Insert into dbo.#test (Date, Person, Type, Time)
Values ('20180521','X','Morning','04:15')
Insert into dbo.#test (Date, Person, Type, Time)
Values ('20180521','X','Morning','04:16')
Insert into dbo.#test (Date, Person, Type, Time)
Values ('20180521','Y','Morning','04:17')
Insert into dbo.#test (Date, Person, Type, Time)
Values ('20180521','Y','Morning','04:19')
Insert into dbo.#test (Date, Person, Type, Time)
Values ('20180521','Y','Afternoon','11:25')
Insert into dbo.#test (Date, Person, Type, Time)
Values ('20180521','Y','Afternoon','11:25')
Insert into dbo.#test (Date, Person, Type, Time)
Values ('20180521','Y','Afternoon','11:28')
Insert into dbo.#test (Date, Person, Type, Time)
Values ('20180521','X','Afternoon','11:28')

GO

select Date, Person, Max(Type), time from dbo.#test
Group by date, Person, Time

The above query produces the result like this
query%20error1

Basically, if the time difference is just 1 minute, it should not display it. If more than 1 minute, it should display the row.

2

Here is one method:

Declare @test Table (
        test_date datetime,
        Person varchar(25),
        test_Type varchar(25),
        test_time time
        );

 Insert Into @test (test_date, Person, test_type, test_time)
 Values ('20180521','X','Morning','04:15')
      , ('20180521','X','Morning','04:16')
      , ('20180521','Y','Morning','04:17')
      , ('20180521','Y','Morning','04:19')
      , ('20180521','Y','Afternoon','11:25')
      , ('20180521','Y','Afternoon','11:25')
      , ('20180521','Y','Afternoon','11:28')
      , ('20180521','X','Afternoon','11:28');

 Select *
   From @test
  Order By
        Person, test_date, test_time;

   With nextTest
     As (
 Select *
      , ntest = lag(test_time, 1) over(Partition By Person, test_date Order By test_time)
   From @test
        )
 Select *
   From nextTest
  Where ntest Is Null
     Or datediff(minute, ntest, test_time) > 1
  Order By
        Person, test_date, test_time;
1 Like
3

Thanks Mr. Jeffw8713, it worked great.