Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Результат - чат клиента с агентом в хронологической последовательсности  [new]
jounymod
Member

Откуда:
Сообщений: 1
Всем привет. Подскажите как решить эту задачу одним сложным запросом.

Есть таблица Т1:


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


Вот исходник:

CREATE TABLE T1(

datemessage [varchar] (100) NOT NULL,

[id_tasks] [varchar] (100) NOT NULL,

[Whowritedmessage] [varchar] (30) NOT NULL

)

GO

INSERT INTO T1 (DATEMESSAGE, ID_TASKS, Whowritedmessage) values

('2017-11-01 04:59:07.6140000','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Client'),

('2017-11-01 05:05:17.5000000','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Client'),

('2017-11-01 05:06:53.9090000','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Agent'),

('2017-11-01 05:06:53.9090000','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Agent'),

('2017-11-01 05:07:18.7020000','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Client'),

('2017-11-01 05:07:43.1280000','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Agent'),

('2017-11-01 05:07:59.5780000','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Client'),

('2017-11-01 05:09:55.0630000','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Client'),

('2017-11-01 05:14:12.5870000','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Agent'),

('2017-11-01 05:14:56.3690000','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Client'),

('2017-11-01 06:06:56.1080000','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Agent'),

('2017-11-01 06:07:07.2790000','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Agent'),

('2017-11-01 06:21:17.5480000','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Client'),

('2017-11-01 06:21:38.4280000','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Agent')

GO


Нужно написать запрос, который из исходных данных (выше) сделает выборку следующего вида :
//Результат - чат клиента с агентом в хронологической последовательсности


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

Решил эту задачу так:

create table #temp_Client (
id int not null identity,
Whowritedmessage nvarchar(100),
datemessage nvarchar(100)
)
insert into #temp_Client
select Whowritedmessage, datemessage from T1
where Whowritedmessage like '%Client%'
order by datemessage

create table #temp_Agent (
id int not null identity,
datemessage nvarchar(100),
Whowritedmessage nvarchar(100),
id_tasks nvarchar(100)
)
insert into #temp_Agent
select datemessage, Whowritedmessage, id_tasks from T1
where Whowritedmessage like '%Agent%'
order by datemessage
--
select c.Whowritedmessage as Client, c.datemessage as DateBefore, a.datemessage as DateAfter, a.Whowritedmessage as Agent, a.id_tasks from #temp_Client c
join #temp_Agent a on a.id=c.id

drop table #temp_Client, #temp_Agent


Но мое решение неверное, хоть и дает желаемый результат.
Нужно решить задачу в один запрос (количество подзапросов не ограничено).
2 окт 19, 23:56    [21985467]     Ответить | Цитировать Сообщить модератору
 Re: Результат - чат клиента с агентом в хронологической последовательсности  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29246
jounymod
Нужно решить задачу в один запрос
select c.Whowritedmessage as Client, c.datemessage as DateBefore, a.datemessage as DateAfter, a.Whowritedmessage as Agent, a.id_tasks 
from (
	select Whowritedmessage, datemessage, id_tasks, row_number() over(order by datemessage) as id
	from T1
	where Whowritedmessage like '%Client%'
) c
join (
	select Whowritedmessage, datemessage, id_tasks, row_number() over(order by datemessage) as id
	from T1
	where Whowritedmessage like '%Agent%'
) a on a.id=c.id
order by c.datemessage
3 окт 19, 02:51    [21985492]     Ответить | Цитировать Сообщить модератору
 Re: Результат - чат клиента с агентом в хронологической последовательсности  [new]
aleks222
Member

Откуда:
Сообщений: 747
alexeyvg
jounymod
Нужно решить задачу в один запрос
select c.Whowritedmessage as Client, c.datemessage as DateBefore, a.datemessage as DateAfter, a.Whowritedmessage as Agent, a.id_tasks 
from (
	select Whowritedmessage, datemessage, id_tasks, row_number() over(order by datemessage) as id
	from T1
	where Whowritedmessage like '%Client%'
) c
join (
	select Whowritedmessage, datemessage, id_tasks, row_number() over(order by datemessage) as id
	from T1
	where Whowritedmessage like '%Agent%'
) a on a.id=c.id
order by c.datemessage


Садись. Неуд.
3 окт 19, 06:07    [21985511]     Ответить | Цитировать Сообщить модератору
 Re: Результат - чат клиента с агентом в хронологической последовательсности  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6788
jounymod,

в один запрос - это ради спорта? Ничего не слышали о функциях, возвращающих таблицу?
3 окт 19, 11:19    [21985648]     Ответить | Цитировать Сообщить модератору
 Re: Результат - чат клиента с агентом в хронологической последовательсности  [new]
invm
Member

Откуда: Москва
Сообщений: 8797
jounymod
Нужно решить задачу в один запрос
with a as
(
 select
  id_tasks, Whowritedmessage, datemessage,
  row_number() over (partition by id_tasks order by datemessage) as rn
 from
  T1
 where
  Whowritedmessage like '%Client%'
),
b as
(
 select
  id_tasks, Whowritedmessage, datemessage,
  row_number() over (partition by id_tasks order by datemessage) as rn
 from
  T1
 where
  Whowritedmessage like '%Agent%'
)
select
 a.Whowritedmessage, a.datemessage, b.datemessage, b.Whowritedmessage, a.id_tasks
from
 a join
 b on b.id_tasks = a.id_tasks and b.rn = a.rn
order by
 a.id_tasks, a.datemessage;

Если допускается нарушение чередования client - agent - client - agent, то результат будет неверен.
3 окт 19, 12:08    [21985700]     Ответить | Цитировать Сообщить модератору
 Re: Результат - чат клиента с агентом в хронологической последовательсности  [new]
PavelPS
Member

Откуда: петербург
Сообщений: 84
jounymod,
какая версия сервера?
при нескольких сообщениях подряд выводить только время первого сообщения?
3 окт 19, 12:24    [21985726]     Ответить | Цитировать Сообщить модератору
 Re: Результат - чат клиента с агентом в хронологической последовательсности  [new]
Ftt330
Member

Откуда:
Сообщений: 1
with tt as (
		select t1.*, 
			   case when whowritedmessage = 'Client' and lag(whowritedmessage, 1, 'Agent') over (partition by id_tasks order by datemessage) = 'Agent' 
					then 1 
					else 0 
				end as w
		from  T1
),
tt1 as (
		select tt.*,
			   sum(w) over (partition by id_tasks order by datemessage) as grp 
		  from tt)
select 'Client',
        min(datemessage),
	max(datemessage),
	'Agent',
	id_tasks
from tt1
group by grp, id_tasks
3 окт 19, 18:11    [21986144]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить