Need help to write query

[reddy21]

Need help to write query to bring from 1st table and need to get as out put table

input table data
col1	col2	col3	col4	col5
1	21	12-06-2021 09:08:07	1	idle
2	21	12-06-2021 09:08:10	1	gant
3	21	12-06-2021 09:08:11	0	gant
4	21	12-06-2021 09:08:17	1	idle
5	21	12-06-2021 09:08:27	1	idle
6	21	12-06-2021 09:08:37	1	idle
7	21	12-06-2021 09:08:47	1	idle
8	21	12-06-2021 09:09:47	1	gan
9	21	12-06-2021 09:09:50	1	gan
10	21	12-06-2021 10:08:17	1	idle
11	21	12-06-2021 10:09:17	1	idle
12	21	12-06-2021 10:18:17	1	idle

Output
col2	col5	Max_date	min_date
21	idle	12-06-2021 09:08:07	12-06-2021 09:08:07
21	gant	12-06-2021 09:08:11	12-06-2021 09:08:10
21	idle	12-06-2021 09:08:47	12-06-2021 09:08:17
21	gan	12-06-2021 09:09:50	12-06-2021 09:09:47
21	idle	12-06-2021 10:18:17	12-06-2021 10:18:17

strong text

[jeffw8713]

This is a typical gaps & islands issue - here is a solution that does not rely on col1 being an ever increasing numeric value with no gaps:

 --==== Sample data
Declare @testData Table (col1 int, col2 int, col3 datetime, col4 int, col5 varchar(10));
 Insert Into @testData (col1, col2, col3, col4, col5)
 Values (1,	21,	'12-06-2021 09:08:07',	1,	'idle')	
      , (2,	21,	'12-06-2021 09:08:10',	1,	'gant')	
      , (3,	21,	'12-06-2021 09:08:11',	0,	'gant')	
      , (4,	21,	'12-06-2021 09:08:17',	1,	'idle')	
      , (5,	21,	'12-06-2021 09:08:27',	1,	'idle')	
      , (6,	21,	'12-06-2021 09:08:37',	1,	'idle')	
      , (7,	21,	'12-06-2021 09:08:47',	1,	'idle')	
      , (8,	21,	'12-06-2021 09:09:47',	1,	'gan')	
      , (9,	21,	'12-06-2021 09:09:50',	1,	'gan')	
      , (10,	21,	'12-06-2021 10:08:17',	1,	'idle')
      , (11,	21,	'12-06-2021 10:09:17',	1,	'idle')	
      , (12,	21,	'12-06-2021 10:18:17',	1,	'idle');

 --==== Solution
   With groups
     As (
 Select *
      , group_id = row_number() over(Order By col3)
                 - row_number() over(Partition By col2, col5 Order By col3)
   From @testData
        )
 Select g.col2
      , g.col5
      , max_date = max(g.col3)
      , min_date = min(g.col3)
   From groups                          g
  Group by
        group_id
      , col2
      , col5
  Order By
        max_date;

If col1 is guaranteed to not have any gaps - and is ordered by the col3 then you can replace the first row_number function in group_id with col1.

[reddy21]

Hi Jeffery,
Thank you so much, it is working fine as expected.

[jeffw8713]

You are welcome - thank you for the feedback.

[alexsunny123]

@jeffw8713

This is a typical gaps & islands issue - here is a solution that does not rely on col1 being an ever increasing numeric value with no gaps:

 --==== Sample data
Declare @testData Table (col1 int, col2 int, col3 datetime, col4 int, col5 varchar(10));
 Insert Into @testData (col1, col2, col3, col4, col5)
 Values (1,	21,	'12-06-2021 09:08:07',	1,	'idle')	
      , (2,	21,	'12-06-2021 09:08:10',	1,	'gant')	
      , (3,	21,	'12-06-2021 09:08:11',	0,	'gant')	
      , (4,	21,	'12-06-2021 09:08:17',	1,	'idle')	
      , (5,	21,	'12-06-2021 09:08:27',	1,	'idle')	
      , (6,	21,	'12-06-2021 09:08:37',	1,	'idle')	
      , (7,	21,	'12-06-2021 09:08:47',	1,	'idle')	
      , (8,	21,	'12-06-2021 09:09:47',	1,	'gan')	
      , (9,	21,	'12-06-2021 09:09:50',	1,	'gan')	
      , (10,	21,	'12-06-2021 10:08:17',	1,	'idle')
      , (11,	21,	'12-06-2021 10:09:17',	1,	'idle')	
      , (12,	21,	'12-06-2021 10:18:17',	1,	'idle');

 --==== Solution
   With groups
     As (
 Select *
      , group_id = row_number() over(Order By col3)
                 - row_number() over(Partition By col2, col5 Order By col3)
   From @testData
        )
 Select g.col2
      , g.col5
      , max_date = max(g.col3)
      , min_date = min(g.col3)
   From groups                          g
  Group by
        group_id
      , col2
      , col5
  Order By
        max_date;

If col1 is guaranteed to not have any gaps - and is ordered by the col3 then you can replace the first row_number function in group_id with col1.

thanks, my issue has been fixed.

[alexsunny123]

@alexsunny123

@jeffw8713

This is a typical gaps & islands issue - here is a solution that does not rely on col1 being an ever increasing numeric value with no gaps:

 --==== Sample data

Declare @testData Table (col1 int, col2 int, col3 datetime, col4 int, col5 varchar(10));
Insert Into @testData (col1, col2, col3, col4, col5)
Values (1, 21, '12-06-2021 09:08:07', 1, 'idle')
, (2, 21, '12-06-2021 09:08:10', 1, 'gant')
, (3, 21, '12-06-2021 09:08:11', 0, 'gant')
, (4, 21, '12-06-2021 09:08:17', 1, 'idle')
, (5, 21, '12-06-2021 09:08:27', 1, 'idle')
, (6, 21, '12-06-2021 09:08:37', 1, 'idle')
, (7, 21, '12-06-2021 09:08:47', 1, 'idle')
, (8, 21, '12-06-2021 09:09:47', 1, 'gan')
, (9, 21, '12-06-2021 09:09:50', 1, 'gan')
, (10, 21, '12-06-2021 10:08:17', 1, 'idle')
, (11, 21, '12-06-2021 10:09:17', 1, 'idle')
, (12, 21, '12-06-2021 10:18:17', 1, 'idle');

--==== Solution
With groups
As (
Select *
, group_id = row_number() over(Order By col3)
- row_number() over(Partition By col2, col5 Order By col3)
From @testData
)
Select g.col2
, g.col5
, max_date = max(g.col3)
, min_date = min(g.col3)
From groups g
Group by
group_id
, col2
, col5
Order By
max_date;

[Ometv](https://ometv.onl) [chatroulette](https://chatroulette.top) [omegle](https://omegle.wtf) [bazoocam](https://bazoocam.cam)
If col1 is guaranteed to not have any gaps - and is ordered by the col3 then you can replace the first row_number function in group_id with col1.

thanks, my issue has been fixed.

thanks for the awesome information.