Lag complex function

Aditi Sharma 61 Reputation points
2022-08-05T01:57:47.663+00:00

Im racking my brain about how to do this and I dont think its possible but not sure what to even search for. So the below is a subset of the data (using example numbers)

I am trying to use the lag function to populate the flag column. 

   Status                                                     Flag      
      Connected -> Exiting                           Maillink      
      Exiting -> Not equal to Connected      OnCorp (All rows stay  oncorp untill we see             Connected)      
           
     Connected -> Reconnecting          Mailink (It should stay Mail-ink for       
                                                           all rows until we see "Exiting"       
                                                              status )

Is it possible in Sql?

Sample Data

Sql Code (But erroring out ) -: 

   WITH base as (      
                     select o.computername,      
                            o.currentuser,      
                            o.datetime,      
                            message,      
                            CASE      
                                WHEN      
                                        LEN(split_part(substring(split_part(message, 'STATE', 2), 13), ',', 1)) <> 0     
                                    THEN split_part(substring(split_part(message, 'STATE', 2), 13), ',', 1)     
                                WHEN  (message like 'WARNING%' OR message like 'OpenVPN%') THEN 'Start'     
                                WHEN message = 'SIGTERM[hard,] received, process exiting' THEN 'Exit1'     
                                WHEN message = 'Closing TUN/TAP interface' THEN 'Exit2'     
                                ELSE 'NO Status' END                                                                                        State1,     
                            CASE WHEN State1 = 'Start' THEN 1      
                                 WHEN State1 = 'RESOLVE' THEN 2      
                                 WHEN State1 = 'WAIT' THEN 3      
                                 WHEN State1 = 'AUTH' THEN 4      
                                 WHEN State1 = 'GET_CONFIG' THEN 5      
                                 WHEN State1 = 'ASSIGN_IP' THEN 6      
                                 WHEN State1 = 'ADD_ROUTES' THEN 7     
                                 WHEN State1 = 'CONNECTED' THEN 8     
                                 WHEN State1 = 'EXITING' THEN 9      
     
                            END orderofoperation      
                             --row_number() over (partition by o.computername,o.currentuser,DATE(o.datetime) order by o.computername,o.currentuser,o.datetime) as rownumber     
                     from maillink_openvpn_logs_ext_schema.open_vpn_filtered o     
                     where o.message != 'message'      
                      and currentuser = 'wuellie'      
                     --and State1 in  ('EXITING' ,'OpenVPN','RESOLVE','WAIT','AUTH','ASSIGN_IP','GET_CONFIG','ADD_ROUTES')     
                     order by o.datetime,orderofoperation      
       )      
     
   select final.*,      
     case when final.previous_record_state is null then ‘oncorp’     
    when final.currentstate = ‘CONNECTED’ then ‘maillink’      
    when final.previous_record_state is not null and final.previous_record_state not in (‘CONNECTED’)     
        then lag(flag) partition by computername,currentuser, nextstateddatetime ::DATE order by nextstateddatetime     
    when previous_record_state in (‘EXITING’) and lag(flag) partition by computername,currentuser, nextstateddatetime ::DATE  order by nextstateddatetime = ‘maillink’     
    then ‘oncorp’      
            else  lag(flag) partition by computername,currentuser, nextstateddatetime ::DATE  order by nextstateddatetime     
            end as flag      
     
   from      
   (      
   select b.computername,b.currentuser,b.State1 currentstate,b.datetime,     
          lag(State1) over (partition by b.computername,b.currentuser,(b.datetime) ::DATE  order by b.computername,b.currentuser, (b.datetime) ::TIMESTAMP,orderofoperation) as  previous_record_state     
          --lag(datetime) over (partition by b.computername,b.currentuser,b.datetime ::DATE order by b.computername,b.currentuser, (b.datetime) ::TIMESTAMP,orderofoperation) as nextdatetime     
   from base b      
   order by b.datetime,orderofoperation      
   )final

Sample data

following format -: 

       Computername      username    State       datetime           Flag  
     xyx              ads       start       2022-07-06T06:45:51         ONCORP                 
     xyx              ads       RESOLVE     2022-07-06T07:06:45         ONCORP                  
     xyx              ads       WAIT        2022-07-06T07:06:45         ONCORP     
     xyx              ads      AUTH         2022-07-06T07:07:00         ONCORP        
     xyx              ads     GET_CONFIG    2022-07-06T07:07:00         ONCORP         
     xyx              ads     ADD_ROUTES    2022-07-06T07:07:01         ONCORP     
     xyx              ads      CONNECTED    2022-07-06T07:07:01         MAILINK      
     xyx              ads       EXITING     2022-07-06T07:07:01         MAILINK      
     xyx              ads       RESOLVE     2022-07-06T07:07:46         ONCORP    
     xyx              ads       WAIT        2022-07-06T07:07:46         ONCORP    
     xyx              ads       AUTH        2022-07-06T07:07:50         ONCORP    
     xyx              ads    GET_CONFIG     2022-07-06T07:07:51         ONCORP    
     xyx              ads    ADD_ROUTES     2022-07-06T07:07:51         ONCORP    
     xyx              ads    CONNECTED      2022-07-06T07:07:52         MAILINK    
     xyx              ads    RECONNECTED    2022-07-06T07:08:01         MAILINK    
     xyx              ads      WAIT         2022-07-06T07:08:02         MAILINK    
     xyx              ads      AUTH         2022-07-06T07:08:09         MAILINK    
     xyx              ads    RECONNECTED    2022-07-06T07:08:10         MAILINK    
     xyx              ads    CONNECTED      2022-07-06T07:08:15         MAILINK    
     xyx              ads    RECONNECTED    2022-07-06T07:08:20         MAILINK    
     xyx              ads    CONNECTED      2022-07-06T07:09:01         MAILINK    
     xyx              ads    EXITING        2022-07-06T07:10:50         MAILINK    
     xyx              ads     START         2022-07-06T07:11:50          ONCORP    



   INSERT  into logdata (computername, currentuser, currentstate, datetime) values (xyz, Monica, Start, 2022-07-05T20:04:51)  
   INSERT  into logdata (computername, currentuser, currentstate,, datetime) values (xyz, Monica, Start, 2022-07-05T20:04:51)  
   INSERT  into logdata (computername, currentuser, currentstate, datetime) values (xyz, Monica, Start, 2022-07-06T06:45:51)  
   INSERT  into logdata (computername, currentuser, currentstate, datetime) values (xyz, Monica, CONNECTED, 2022-07-06T07:07:01)  
   INSERT  into logdata (computername, currentuser, currentstate, datetime) values (xyz, Monica, EXITING, 2022-07-06T07:07:46)  
   INSERT  into logdata (computername, currentuser, currentstate, datetime) values (xyz, Monica, Exit1, 2022-07-06T07:07:46)  
   INSERT  into logdata (computername, currentuser, currentstate, datetime) values (xyz, Monica, Exit2, 2022-07-06T07:07:46)  
   INSERT  into logdata (computername, currentuser, currentstate, datetime) values (xyz, Monica, ASSIGN_IP, 2022-07-06T07:59:26)  
   INSERT  into logdata (computername, currentuser, currentstate, datetime) values (xyz, Monica, ADD_ROUTES, 2022-07-06T07:59:26)  
   INSERT  into logdata (computername, currentuser, currentstate, datetime) values (xyz, Monica, CONNECTED, 2022-07-06T07:59:26)  
   INSERT  into logdata (computername, currentuser, currentstate, datetime) values (xyz, Monica, RECONNECTING, 2022-07-06T08:40:32)  
   INSERT  into logdata (computername, currentuser, currentstate, datetime) values (xyz, Monica, WAIT, 2022-07-06T08:40:34)  
   INSERT  into logdata (computername, currentuser, currentstate, datetime) values (xyz, Monica, ADD_ROUTES, 2022-07-06T11:59:56)  
   INSERT  into logdata (computername, currentuser, currentstate, datetime) values (xyz, Monica, CONNECTED, 2022-07-06T11:59:56)  
   INSERT  into logdata (computername, currentuser, currentstate, datetime) values (xyz, Monica, RECONNECTING, 2022-07-06T12:31:41)  
   INSERT  into logdata (computername, currentuser, currentstate, datetime) values (xyz, Monica, ASSIGN_IP, 2022-07-06T14:28:26)  
   INSERT  into logdata (computername, currentuser, currentstate, datetime) values (xyz, Monica, ADD_ROUTES, 2022-07-06T14:28:26)  
   INSERT  into logdata (computername, currentuser, currentstate, datetime) values (xyz, Monica, CONNECTED, 2022-07-06T14:28:26)  
   INSERT  into logdata (computername, currentuser, currentstate, datetime) values (xyz, Monica, EXITING ,2022-07-06T14:29:01)  
   INSERT  into logdata (computername, currentuser, currentstate, datetime) values (xyz, Monica, Exit2, 2022-07-06T14:29:01)  


   Create table logdata (  
            computername varchar(50),  
    currentuser  varchar(60),  
    currentstate varchar(100),  
    datetime     varchar(100)  
    )
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,820 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
Accepted answer
  1. LiHong-MSFT 10,046 Reputation points
    2022-08-08T06:33:56.14+00:00

    Hi @Aditi Sharma
    Check this query: 

    ;WITH CTE1 as   
(  
 SELECT computername,currentuser,datetime,currentstate,     
        CASE WHEN currentstate = 'CONNECTED' AND LEAD(currentstate)OVER(PARTITION BY computername,currentuser ORDER BY datetime)='EXITING'  
		     THEN 'MAILINK'      
             WHEN currentstate = 'EXITING' AND LAG(currentstate)OVER(PARTITION BY computername,currentuser ORDER BY datetime)='CONNECTED'  
		     THEN 'MAILINK'   
			 WHEN currentstate = 'CONNECTED' AND LEAD(currentstate)OVER(PARTITION BY computername,currentuser ORDER BY datetime)='RECONNECTING'  
		     THEN 'TO_BE_MAILINK'     
             ELSE 'ONCORP' END Flag      
 FROM logdata  
),CTE2 AS  
(  
 SELECT *,CASE WHEN Flag='ONCORP' THEN 0 ELSE 1 END AS TO_SUM      
 FROM CTE1   
),CTE3 AS  
(  
 SELECT *,SUM(TO_SUM)OVER(PARTITION BY computername,currentuser ORDER BY datetime ROWS UNBOUNDED PRECEDING)AS PART_VALUE  
 FROM CTE2  
)  
SELECT computername,currentuser,datetime,currentstate  
      ,CASE WHEN PART_VALUE IN (SELECT PART_VALUE FROM CTE3 WHERE Flag='TO_BE_MAILINK') THEN 'MAILINK' ELSE Flag END AS Flag  
FROM CTE3

    Best regards,
    LiHong

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 41,006 Reputation points
    2022-08-05T05:21:50.827+00:00

    Is it possible in Sql?

    A lot of things are possible in T-SQL. But you didn't mentioned what of data you have and what you are trying to achieve.

    But erroring out

    And which error message do you get? We can't guess that.

  2. Olaf Helper 41,006 Reputation points
    2022-08-05T06:29:59.807+00:00

    attached snapshot of data.

    Fine, but I can't query web sites to test it.
    And the answer on "And which error message do you get?" is still missing.

    Please post table design as DDL, some sample data as DML statement and the expected result.