ALTER PROCEDURE [dbo].[BillingCommonReportAllUsers] @ProjectID int = null, @date1 datetime, @date2 datetimeAS-- проверяем чтобы даты не были больше текущейif (@date2 > GETDATE()) set @date2 = GETDATE()if (@date2 < @date1) return-- выбираем всех юзеров, у которых были сессии пересекающиеся с заданным диапазоном-- если сессия ещё не закрыта - тоже считаем--select-- [UserID],-- (select [FIO] from [Users] where [ID] = b.[UserID]) as [UserName],-- dbo.BillingCalculateTime(1, [UserID], @projectid, @date1, @date2) as [WorkTime],-- dbo.BillingCalculateInterview([UserID], @projectid, @date1, @date2, null) as [InterviewCount], -- dbo.BillingCalculateTime(2, [UserID], @projectid, @date1, @date2) as [InterviewTime], -- dbo.BillingCalculateInterview([UserID], @projectid, @date1, @date2, 1) as [SuccessInterviewCount], -- dbo.BillingCalculateTime(3, [UserID], @projectid, @date1, @date2) as [SuccessInterviewTime]--from-- [BillingU] b--where-- [TypeID] = 10 and -- [BeginDate] < @date2 and [EndDate] > @date1--group by-- [UserID]--order by-- [UserName]declare @results table ( [UserID] int, [UserName] varchar(100), [WorkTime] int, [InterviewCount] int, [InterviewTime] int, [SuccessInterviewCount] int, [SuccessInterviewTime] int)declare users cursor local static forward_only read_only for select [UserID], (select [FIO] from [Users] where [ID] = b.[UserID]) as [UserName] from [BillingU] b where [TypeID] = 10 and [BeginDate] < @date2 and [EndDate] > @date1 group by [UserID] order by [UserName]open users declare @UserID int declare @UserName varchar(100) declare @WorkTime int declare @InterviewCount int declare @InterviewTime int declare @SuccessInterviewCount int declare @SuccessInterviewTime int fetch from users into @UserID, @UserName while @@fetch_status=0 begin insert into @results (UserID, UserName, WorkTime, InterviewCount, InterviewTime, SuccessInterviewCount, SuccessInterviewTime) select @UserID, @UserName, dbo.BillingCalculateTime(1, @UserID, @projectid, @date1, @date2), iv.Total, dbo.BillingCalculateTime(2, @UserID, @projectid, @date1, @date2), iv.Success, dbo.BillingCalculateTime(3, @UserID, @projectid, @date1, @date2), from dbo.BillingCalculateInterviewTbl(@UserID, @projectid, @date1, @date2) iv fetch next from users into @UserID, @UserName endclose usersdeallocate usersselect * from @results