How to add data from a table to a query

Here is the finished part of the code, it works correctly. Displays the employee, the number of calls and the total time in a dialogue with the subscriber. In this query, I want to add data from another table, data about the break. How many minutes is in the break.

declare @id table (id uniqueidentifier) insert into @id select id from [oktell_settings].[dbo].[A_Users] where ParentGroupID = @group Select US.Name,Tb2.[CountCall],Tb2.[TIme] FROM ( Select Count(id) [CountCall], oktell.dbo.GetTimeFromSecond(sum(Time)) [TIme], [UserId] FROM ( select id, datediff(ss,TimeAnswer, TimeStop) [Time], AUserId [UserId] FROM [oktell].[dbo].[A_Stat_Connections_1x1] where AuserID in (select * from @id) and TimeStart between @dt1 and @dt2 and CAST(CONVERT(CHAR(10), TimeStart, 108) AS DATETIME) between @tm1 and @tm2 Union All select id, datediff(ss,TimeAnswer, TimeStop) [Time], BUserId [UserId] FROM [oktell].[dbo].[A_Stat_Connections_1x1] where BuserID in (select * from @id) and TimeStart between @dt1 and @dt2 and CAST(CONVERT(CHAR(10), TimeStart, 108) AS DATETIME) between @tm1 and @tm2 )Tb1 Group BY [UserId] )Tb2, [oktell_settings].[dbo].[A_Users] US Where Tb2.UserId = US.Id Order by name asc Я пробовал вот так сделать. declare @id table (id uniqueidentifier) insert into @id select id from [oktell_settings].[dbo].[A_Users] where ParentGroupID = @group Select US.Name,Tb2.[CountCall],Tb2.[TIme], Tb2.[LengthSec] FROM ( Select Count(id) [CountCall], oktell.dbo.GetTimeFromSecond(sum(Time)) [TIme], [LengthSec], [UserId] FROM ( select kk.id, datediff(ss,TimeAnswer, kk.TimeStop) [Time], kk.AUserId [UserId] , ee.LengthSec [LengthSec] FROM [oktell].[dbo].[A_Stat_Connections_1x1] kk, [A_CallCenter_UserStateHistory] ee where kk.AuserID in (select * from @id) and kk.TimeStart between @dt1 and @dt2 and CAST(CONVERT(CHAR(10), kk.TimeStart, 108) AS DATETIME) between @tm1 and @tm2 and OperatorId=kk.AuserID and State=3 and ee.TimeEnter between @dt1 and @dt2 and CAST(CONVERT(CHAR(10), ee.TimeEnter, 108) AS DATETIME) between @tm1 and @tm2 Union All select tt.id, datediff(ss,TimeAnswer, tt.TimeStop) [Time], tt.BUserId [UserId] , ww.LengthSec [LengthSec] FROM [oktell].[dbo].[A_Stat_Connections_1x1] tt, [A_CallCenter_UserStateHistory] ww where tt.BuserID in (select * from @id) and tt.TimeStart between @dt1 and @dt2 and CAST(CONVERT(CHAR(10), tt.TimeStart, 108) AS DATETIME) between @tm1 and @tm2 and OperatorId=tt.BuserID and State=3 and ww.TimeEnter between @dt1 and @dt2 and CAST(CONVERT(CHAR(10), ww.TimeEnter, 108) AS DATETIME) between @tm1 and @tm2 )Tb1 Group BY [UserId] , [LengthSec] )Tb2, [oktell_settings].[dbo].[A_Users] US Where Tb2.UserId = US.Id Order by name asc 

Then I tried it like this, well, it doesn’t work, how can I edit it correctly?

 Select US.Name,Tb2.[CountCall],Tb2.[TIme], ee.LengthSec FROM ( Select Count(id) [CountCall], oktell.dbo.GetTimeFromSecond(sum(Time)) [TIme], [UserId] FROM ( select id, datediff(ss,TimeAnswer, TimeStop) [Time], AUserId [UserId] FROM [oktell].[dbo].[A_Stat_Connections_1x1] where AuserID in (select * from @id) and TimeStart between @dt1 and @dt2 and CAST(CONVERT(CHAR(10), TimeStart, 108) AS DATETIME) between @tm1 and @tm2 Union All select id, datediff(ss,TimeAnswer, TimeStop) [Time], BUserId [UserId] FROM [oktell].[dbo].[A_Stat_Connections_1x1] where BuserID in (select * from @id) and TimeStart between @dt1 and @dt2 and CAST(CONVERT(CHAR(10), TimeStart, 108) AS DATETIME) between @tm1 and @tm2 )Tb1 Group BY [UserId] )Tb2, [oktell_settings].[dbo].[A_Users] US, [A_CallCenter_UserStateHistory] ee Where Tb2.UserId = US.Id and ee.OperatorId in (select * from @id) and ee.State=3 and ee.TimeEnter between @dt1 and @dt2 and CAST(CONVERT(CHAR(10), ee.TimeEnter, 108) AS DATETIME) between @tm1 and @tm2 Order by name asc 

Here is the table itself, which I want to shove. It is clear that we must take all the records from date to date and put them together, translate them into minutes and output them for each employee.

In this table, received a time of dialogue and summarized.

Yes tables are related.

 [A_Stat_Connections_1x1] - таблица хранящая информацию о состоявшихся разговорах [Id] [uniqueidentifier] - id записи [IdChain] [uniqueidentifier] - id цепочки коммутаций (звонка) [TimeStart] [datetime] - время начала коммутации [TimeAnswer] [datetime] - время ответа [TimeStop] [datetime] - время конца коммутации [ALineNum] [nvarchar](10) - А (IVR если А это IVR) [BLineNum] [nvarchar](10) - В (IVR если В это IVR) [AUserId] [uniqueidentifier] - Id пользователя (bf-внешняя линия, ab IVR) [BUserId] [uniqueidentifier] - Id пользователя (bf- внешняя линия, ab IVR) В этой таблице как раз и есть то что мне надо, продолжительность нахождения в перерыве. [A_CallCenter_UserStateHistory] — история входа\выхода пользователей в\из Call-центра [Id] [uniqueidentifier] — ID записи [OperatorId] [uniqueidentifier] — ID пользователя [State] [int] — состояние (0 - Система, 1 - Call-центр, 2 - Онлайн, 3 - Перерыв, 4 - Ручной режим Call-центра) [TimeEnter] [datetime] — дата входа (установки нового состояния) [TimeLeave] [datetime] — дата выхода (смены состояния) [LengthSec] [float] — продолжительность нахождения в состоянии [Info] [nvarchar](100) — причина перерыва при выборе причины «Прочие» указывается вручную