SQL Server How to use LEAD() function with null value on T-SQL

Context - some agents send or not different types of messages, the date messages are recorded.

Sometimes several agents are presents on the same event (mess_id on the table below).

How to create columns by messages and retrieve for each message the minimum date if the agent send the same message and case if the message is not send.

Here is the table :
| mess_id | alltripid | categ_agent | message_order | type_message | dt_message |
| ------------ | ------------ | ------------ | ------------ | ------------ | ------------ |
| 2022-12-19-2310-8891702 | 17 | x | 1 | enter | 2022-12-19 10:53:43 |
| 2022-12-19-2310-8891702 | 17 | x | 2 | mess1_to_give | NULL |
| 2022-12-19-2310-8891702 | 17 | x | 3 | mess1_given | NULL |
| 2022-12-19-2310-8891702 | 17 | x | 4 | mess2_to_give | NULL |
| 2022-12-19-2310-8891702 | 17 | x | 5 | mess2_given | NULL |
| 2022-12-19-2310-8891702 | 17 | x | 6 | mess_played | NULL |
| 2022-12-19-2310-8891702 | 17 | x | 7 | leave | NULL |
| 2022-12-19-2310-8891702 | 18 | y | 1 | enter | 2022-12-19 10:48:51 |
| 2022-12-19-2310-8891702 | 18 | y | 2 | mess1_to_give | 2022-12-19 10:57:13 |
| 2022-12-19-2310-8891702 | 18 | y | 3 | mess1_given | NULL |
| 2022-12-19-2310-8891702 | 18 | y | 4 | mess2_to_give | 2022-12-19 10:57:49 |
| 2022-12-19-2310-8891702 | 18 | y | 5 | mess2_given | 2022-12-19 10:57:58 |
| 2022-12-19-2310-8891702 | 18 | y | 6 | mess_played | NULL |
| 2022-12-19-2310-8891702 | 18 | y | 7 | leave | 2022-12-19 11:00:36.07 |

and the expected result :

mess_id	alltripid	categ_agent	message_order	type_message	dt_message	enter	mess1_to_give	mess1_given	mess2_to_give	mess2_given	mess_played	leave
2022-12-19-2310-8891702	17	x	1	enter	19/12/2022 10:53	19/12/2022 10:48	19/12/2022 10:57	NULL	19/12/2022 10:57	19/12/2022 10:57	NULL	19/12/2022 11:00
2022-12-19-2310-8891702	17	x	2	mess1_to_give	NULL	19/12/2022 10:48	19/12/2022 10:57	NULL	19/12/2022 10:57	19/12/2022 10:57	NULL	19/12/2022 11:00
2022-12-19-2310-8891702	17	x	3	mess1_given	NULL	19/12/2022 10:48	19/12/2022 10:57	NULL	19/12/2022 10:57	19/12/2022 10:57	NULL	19/12/2022 11:00
2022-12-19-2310-8891702	17	x	4	mess2_to_give	NULL	19/12/2022 10:48	19/12/2022 10:57	NULL	19/12/2022 10:57	19/12/2022 10:57	NULL	19/12/2022 11:00
2022-12-19-2310-8891702	17	x	5	mess2_given	NULL	19/12/2022 10:48	19/12/2022 10:57	NULL	19/12/2022 10:57	19/12/2022 10:57	NULL	19/12/2022 11:00
2022-12-19-2310-8891702	17	x	6	mess_played	NULL	19/12/2022 10:48	19/12/2022 10:57	NULL	19/12/2022 10:57	19/12/2022 10:57	NULL	19/12/2022 11:00
2022-12-19-2310-8891702	17	x	7	leave	NULL	19/12/2022 10:48	19/12/2022 10:57	NULL	19/12/2022 10:57	19/12/2022 10:57	NULL	19/12/2022 11:00
2022-12-19-2310-8891702	18	y	1	enter	19/12/2022 10:48	19/12/2022 10:48	19/12/2022 10:57	NULL	19/12/2022 10:57	19/12/2022 10:57	NULL	19/12/2022 11:00
2022-12-19-2310-8891702	18	y	2	mess1_to_give	19/12/2022 10:57	19/12/2022 10:48	19/12/2022 10:57	NULL	19/12/2022 10:57	19/12/2022 10:57	NULL	19/12/2022 11:00
2022-12-19-2310-8891702	18	y	3	mess1_given		19/12/2022 10:48	19/12/2022 10:57	NULL	19/12/2022 10:57	19/12/2022 10:57	NULL	19/12/2022 11:00
2022-12-19-2310-8891702	18	y	4	mess2_to_give	19/12/2022 10:57	19/12/2022 10:48	19/12/2022 10:57	NULL	19/12/2022 10:57	19/12/2022 10:57	NULL	19/12/2022 11:00
2022-12-19-2310-8891702	18	y	5	mess2_given	19/12/2022 10:57	19/12/2022 10:48	19/12/2022 10:57	NULL	19/12/2022 10:57	19/12/2022 10:57	NULL	19/12/2022 11:00
2022-12-19-2310-8891702	18	y	6	mess_played	NULL	19/12/2022 10:48	19/12/2022 10:57	NULL	19/12/2022 10:57	19/12/2022 10:57	NULL	19/12/2022 11:00
2022-12-19-2310-8891702	18	y	7	leave	19/12/2022 11:00	19/12/2022 10:48	19/12/2022 10:57	NULL	19/12/2022 10:57	19/12/2022 10:57	NULL	19/12/2022 11:00

I am unable to use lead() and min() correctly in this case I don't know what the right solution is and I tried this for the first value and the last.

WITH first_step AS
(
    SELECT 
        *,
        CONCAT(mess_id, '-', type_message) AS mess_type_id,
        ROW_NUMBER() OVER (PARTITION BY type_message ORDER BY mess_id) AS rn_message
    FROM 
        mytable
),
second_step AS
(
    SELECT 
        *,
        MIN(dt_message) OVER (PARTITION BY mess_type_id ORDER BY mess_type_id) AS min_dt_mess
    FROM 
        first_step
)
SELECT 
    *,
    FIRST_VALUE(min_dt_mess) OVER (PARTITION BY mess_id ORDER BY message_order) AS enter,
    MIN(min_dt_mess) OVER (PARTITION BY mess_id ORDER BY message_order ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) mess_played,
    FIRST_VALUE(min_dt_mess) OVER (PARTITION BY mess_id ORDER BY message_order desc) AS leave
FROM 
    second_step
ORDER BY 
    categ_agent, message_order

See the Fiddle

One of the ways to get the desired result

SELECT mess_id, alltripid, categ_agent, message_order, type_message, dt_message,
  min(case when type_message ='enter' then dt_message end) OVER (PARTITION BY mess_id ORDER BY mess_id) as enter,
  min(case when type_message ='mess1_to_give' then dt_message end) OVER (PARTITION BY mess_id ORDER BY mess_id) as mess1_to_give,
  min(case when type_message ='mess1_given' then dt_message end) OVER (PARTITION BY mess_id ORDER BY mess_id) as mess1_given,
  min(case when type_message ='mess2_to_give' then dt_message end) OVER (PARTITION BY mess_id ORDER BY mess_id) as mess2_to_give,
  min(case when type_message ='mess2_given' then dt_message end) OVER (PARTITION BY mess_id ORDER BY mess_id) as mess2_given,  
  min(case when type_message ='mess_played' then dt_message end) OVER (PARTITION BY mess_id ORDER BY mess_id) as mess_played,
  min(case when type_message ='leave' then dt_message end) OVER (PARTITION BY mess_id ORDER BY mess_id) as leave
  FROM mytable

See the Fiddle(https://dbfiddle.uk/iwx0MzHZ)

SQL Server How to use LEAD() function with null value on T-SQL

1条答案

相关问题

热门标签

最新问答