SQLTeam.com | Weblogs | Forums

Need help to write query

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

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.

2 Likes

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

1 Like

You are welcome - thank you for the feedback.

thanks, my issue has been fixed.

thanks for the awesome information.