3

I have following table:

 Declare @YourTable table ([Event] varchar(100),[Start] DateTime,[End] DateTime, [Tag] varchar(25))
 Insert Into @YourTable values
 ('10PIC700422.PV 10-PSV-700073A 10-PSV-700073B','9/9/16 10:44','9/9/16 10:49','Big'),
 ('10PIC700422.PV 10-PSV-700073A 10-PSV-700073B','9/9/16 10:50','9/9/16 10:51','Small'),
 ('11PIC41010.PV 11-PSV-401002A 11-PSV-401002B','4/4/16 12:51','4/4/16 13:58','Big'),
 ('11PIC41010.PV 11-PSV-401002A 11-PSV-401002B','4/4/16 14:04','4/4/16 14:29','Small'),
 ('11PIC41010.PV 11-PSV-401002A 11-PSV-401002B','4/4/16 14:51','4/4/16 14:58','Big'),
 ('11PIC41010.PV 11-PSV-401002A 11-PSV-401002B','4/4/16 15:04','4/4/16 15:29','Small'),
 ('11PIC41010.PV 11-PSV-401002W 11-PSV-401002D','4/4/16 16:04','4/4/16 16:45','Big')

And use following query to get result I need, group by event and order by start, and change big to small whenever small goes afer big:

 Select [Event]
  ,[Start]
  ,[End] 
  ,[Tag]
  ,[Tag_new] = case when Tag='Big' and 'Small' = Lead(Tag,1,Tag) over (Partition By Event Order By Start) then 'Small' else tag end
  From  @YourTable

   Event                                          Start         End             Tag    Tag_new
   10PIC700422.PV 10-PSV-700073A 10-PSV-700073B   9-9-16 10:44  9-9-16 10:49    Big    Small
   10PIC700422.PV 10-PSV-700073A 10-PSV-700073B   9-9-16 10:50  9-9-16 10:51    Small  Small
   11PIC41010.PV 11-PSV-401002A 11-PSV-401002B    4-4-16 12:51  4-4-16 13:58    Big    Small
   11PIC41010.PV 11-PSV-401002A 11-PSV-401002B    4-4-16 14:04  4-4-16 14:29    Small  Small
   11PIC41010.PV 11-PSV-401002A 11-PSV-401002B    4-4-16 14:51  4-4-16 14:58    Big    Small
   11PIC41010.PV 11-PSV-401002A 11-PSV-401002B    4-4-16 15:04  4-4-16 15:29    Small  Small
   11PIC41010.PV 11-PSV-401002W 11-PSV-401002D    4-4-16 16:04  4-4-16 16:45    Big    Big

The only thing is I need to add one exception whenever sequence below appears in Tag column in a group, it should add aditional row between small and big with starttime and endtime accordingly, and starttime is 1 min more then previous endtime and endtime 1 min more than starttime and Tag_new is "bad":

  small
  big
  small

and I'd like to get result below:

   Event                                          Start         End             Tag    Tag_new
   10PIC700422.PV 10-PSV-700073A 10-PSV-700073B   9-9-16 10:44  9-9-16 10:49    Big    Small
   10PIC700422.PV 10-PSV-700073A 10-PSV-700073B   9-9-16 10:50  9-9-16 10:51    Small  Small
   11PIC41010.PV 11-PSV-401002A 11-PSV-401002B    4-4-16 12:51  4-4-16 13:58    Big    Small
   11PIC41010.PV 11-PSV-401002A 11-PSV-401002B    4-4-16 14:04  4-4-16 14:29    Small  Small
   11PIC41010.PV 11-PSV-401002A 11-PSV-401002B    4-4-16 14:30  4-4-16 14:31    Bad    Bad
   11PIC41010.PV 11-PSV-401002A 11-PSV-401002B    4-4-16 14:51  4-4-16 14:58    Big    Small
   11PIC41010.PV 11-PSV-401002A 11-PSV-401002B    4-4-16 15:04  4-4-16 15:29    Small  Small
   11PIC41010.PV 11-PSV-401002W 11-PSV-401002D    4-4-16 16:04  4-4-16 16:45    Big    Big
Cœur
  • 37,241
  • 25
  • 195
  • 267
Joe Green
  • 209
  • 2
  • 11

1 Answers1

0

You can try using LEAD() twice, to select the next val and the last val, and then use CASE EXPRESSION on it with all the conditions :

SELECT [Event],[Start],[End],[Tag],
       CASE WHEN t.next_one = 'Small' and t.Last_one = 'Small' And t.Tag = 'Big' then 'Big'
            WHEN t.tag = 'Big' and t.next_one = 'Small' THEN 'Small'
            ELSE t.tag
       END as new_tag
FROM(
     Select [Event]
       ,[Start]
       ,[End] 
       ,[Tag]
       Lead(Tag,1,Tag) over (Partition By Event Order By Start) as next_one,
       Lead(Tag,1,Tag) over (Partition By Event Order By Start DESC) as last_one,
    From  @YourTable) t
sagi
  • 40,026
  • 6
  • 59
  • 84