CODEWITHSUNDEEP
mysqlsqlsql-server
Zeo
Zeo

Reputation: 53

How to solve a problem using row_number, lag / lead, join SQL

In sql, I'm completely new, but I need to solve this problem using only row_number, lag / lead, join. I’ve been struggling with the problem for 2 days and I just can’t solve it correctly.

We have a table with data, you need to get the answer of the following from:

 Client 2017-11-01 04:59:07.6140000 2017-11-01 05:06:53.9090000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
    Client 2017-11-01 05:07:18.7020000 2017-11-01 05:07:43.1280000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
    Client 2017-11-01 05:07:59.5780000 2017-11-01 05:14:12.5870000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
    Client 2017-11-01 05:14:56.3690000 2017-11-01 06:06:56.1080000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
    Client 2017-11-01 06:21:17.5480000 2017-11-01 06:21:38.4280000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E

It's my code: 

      if object_id('tempdb..#c1') is not null
          drop table #c1

        create table #c1 (datemessage datetime2 ,id_tasks nvarchar(255),Whowritedmessage nvarchar(255));

        insert into #c1 
        values
        ('2017-11-01 04:59:07.614','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Client'),
        ('2017-11-01 05:05:17.500','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Client'),
        ('2017-11-01 05:06:53.909','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Agent'),
        ('2017-11-01 05:06:53.909','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Agent'),
        ('2017-11-01 05:07:18.702','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Client'),
        ('2017-11-01 05:07:43.128','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Agent'),
        ('2017-11-01 05:07:59.578','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Client'),
        ('2017-11-01 05:09:55.063','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Client'),
        ('2017-11-01 05:14:12.587','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Agent'),
        ('2017-11-01 05:14:56.369','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Client'),
        ('2017-11-01 06:06:56.108','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Agent'),
        ('2017-11-01 06:07:07.279','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Agent'),
        ('2017-11-01 06:21:17.548','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Client'),
        ('2017-11-01 06:21:38.428','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Agent')



        SELECT 
tabC.whowritedmessage,
tabA.datemessage,
tabA.answer,
tabA.whowritedmessage,
tabA.id_tasks
FROM(SELECT 
ROW_NUMBER() OVER (PARTITION BY whowritedmessage ORDER BY datemessage) as number,
whowritedmessage
FROM #c1
where whowritedmessage ='Client') as tabC
join (SELECT 
              top 50 percent
ROW_NUMBER() OVER (PARTITION BY whowritedmessage ORDER BY datemessage) as number,
LEAD(datemessage,7) OVER (ORDER BY whowritedmessage) as datemessage,
datemessage as answer,
whowritedmessage,
id_tasks
FROM #c1
) as tabA  on tabA.number = tabC.number

We have a table with data, you need to get the answer of the following from:

 Client 2017-11-01 04:59:07.6140000 2017-11-01 05:06:53.9090000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
 Client 2017-11-01 05:07:18.7020000 2017-11-01 05:07:43.1280000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
 Client 2017-11-01 05:07:59.5780000 2017-11-01 05:14:12.5870000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
 Client 2017-11-01 05:14:56.3690000 2017-11-01 06:06:56.1080000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
 Client 2017-11-01 06:21:17.5480000 2017-11-01 06:21:38.4280000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E

but I constantly get 7 lines, although there should be 5, because if the client goes the same next line, it is not taken into account and the same with the agent

Upvotes: 2

Views: 1073

Answers (2)

mkRabbani
mkRabbani

Reputation: 16908

Check this script-

WITH CTE AS
(
    SELECT *,
    LAG(whowritedmessage) OVER (ORDER BY datemessage) who_lag
    FROM #c1
)

SELECT whowritedmessage,
datemessage,
ans,
ISNULL(who_lag,'Agent') whowritedmessage2,
id_tasks 
FROM
(
    SELECT *,
    LEAD(datemessage) OVER (ORDER BY datemessage) ans,
    LEAD(whowritedmessage) OVER (ORDER BY datemessage) who_lead
    FROM CTE
    WHERE (whowritedmessage <> who_lag OR who_lag IS NULL)
)A
WHERE whowritedmessage = 'Client'

Output is-

whowritedmessage    datemessage                 ans                          whowritedmessage2  id_tasks
Client              2017-11-01 04:59:07.6140000 2017-11-01 05:06:53.9090000  Agent              C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
Client              2017-11-01 05:07:18.7020000 2017-11-01 05:07:43.1280000  Agent              C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
Client              2017-11-01 05:07:59.5780000 2017-11-01 05:14:12.5870000  Agent              C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
Client              2017-11-01 05:14:56.3690000 2017-11-01 06:06:56.1080000  Agent              C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
Client              2017-11-01 06:21:17.5480000 2017-11-01 06:21:38.4280000  Agent              C56D5EF0-CBC4-473F-90A7-A7A10223AE3E

Changed: For better understanding, I have added 1 more CTE here with same output as above script.

WITH CTE AS
(
    SELECT *,
    LAG(whowritedmessage) OVER (ORDER BY datemessage) who_lag
    FROM #c1
),
CTE2 AS
(
    SELECT  whowritedmessage,   datemessage,    
    LEAD(datemessage) OVER (ORDER BY datemessage) ans,
    ISNULL(who_lag,'Agent') whowritedmessage2,
    id_tasks
    FROM CTE
    WHERE (whowritedmessage <> who_lag OR who_lag IS NULL)
)

--SELECT * FROM CTE
--SELECT * FROM CTE2
SELECT * FROM CTE2 WHERE whowritedmessage = 'Client'

You can run one by one SELECT statement(Commented now) to understand how data is transforming step by step. Remember, 3rd SELECT statement is your final script and other 2 commented SELECT is just for your checking. Hope this will help you understand the whole process.

Upvotes: 2

Zeo
Zeo

Reputation: 53

SELECT t2.whowritedmessage,t2.datemessage,t2.answer,ISNULL(this_lag,'Agent') as whowritedmessage2,t2.id_tasks
FROM 
(SELECT *,
LEAD(datemessage) over (order by datemessage) answer,
LEAD(whowritedmessage) over (order by datemessage) this_lead
FROM
 (SELECT *,
LAG(whowritedmessage) over (order by datemessage) this_lag
FROM #c1) as t1
Where whowritedmessage <> this_lag or this_lag IS NULL) as t2
Where whowritedmessage = 'Client'

Upvotes: 0

Related Questions