Используйте функцию перекрестного применения
У меня есть этот перекрестный запрос и я хочу суммировать его результат
CROSS APPLY
(SELECT
SUM(CASE WHEN [day] BETWEEN @FirstDay AND @LastDay
THEN 1 ELSE 0
END) AS UsedDays
FROM
Calendar c
WHERE
([day] >= r.[DateFrom]
AND [day] <= r.[DateTo]
AND [WorkDay] = 1)) calculateUsedDays
У меня есть таблица запросов, которая содержит разные запросы от разных людей, и я хочу суммировать все дни от запросов от человека.
Перекрестное применение возвращает сумму дней по каждому запросу из таблицы запросов для каждого человека.
Например:
человек
John, usedDays - 5
John, - 7
Peter - 10
Peter - 5
..
И я хочу суммировать эти дни и группы по имени человека, чтобы я мог иметь все дни по человеку.
Пример:
John - 12
Peter - 15
Я пытался с суммой и сгруппировать, но он возвращает ошибку:
Каждое выражение GROUP BY должно содержать хотя бы один столбец, который не является внешней ссылкой
Спасибо:))
1 ответ
Спасибо, ребята, я решил это, но теперь моя проблема: неявное преобразование из типа данных datetime в int не допускается. Используйте функцию CONVERT, чтобы выполнить этот запрос.
Это мой код
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [core].[ReportBalanceYearSearch]
@Year int = NULL,
@TypeOfLeaveGid int = NULL,
@IsActive int = NULL
AS
BEGIN
SET NOCOUNT ON
DECLARE @Err int
DECLARE @sql nvarchar (max), @paramlist nvarchar(max)
DECLARE @FirstDay datetime
DECLARE @LastDay datetime
DECLARE @typeLeaveGid INT, @typeCreditGid INT, @relLeaveToCreditGid INT
SET @FirstDay = DATEFROMPARTS ( @Year, 1, 1)
SET @LastDay = DATEFROMPARTS ( @Year, 12, 31)
SELECT @typeLeaveGid = gid FROM Nomenclature WHERE type = 'RequestType' and Code = 1
SELECT @typeCreditGid = gid FROM Nomenclature WHERE type = 'RequestType' and Code = 2
SELECT @relLeaveToCreditGid = gid FROM Nomenclature WHERE type = 'Relation' and Code = 6
SELECT @sql = '
SELECT u.[Name],
u.[DepartmentGid],
sum(calculateUsedDays.UsedDays - isnull(calculateCreditDays.CreditDaysInPeriod,0)) as [UsedDays],
ub.[Balance],
sum(calculateUsedDays.UsedDays - isnull(calculateCreditDays.CreditDaysInPeriod,0)) + ub.[Balance] as [TotalDaysInYear],
r.[LeaveTypeGid]
FROM [dbo].[Request] r
inner join [User] u on r.[UserGid] = u.[Gid]
inner join [UserBalance] ub on r.[UserGid] = ub.UserGid and ub.Year = @xYear
LEFT OUTER JOIN dbo.Request CRD
inner join Relations rel ON rel.RelID2 = CRD.Gid AND rel.RelType = @xrelLeaveToCreditGid
inner join Nomenclature nsc ON nsc.Gid = CRD.StatusGid
cross apply (SELECT
sum(case when [day] between COALESCE(@xFirstDay, [day]) AND COALESCE(@xLastDay, [day]) then 1 else 0 end) as CreditDaysInPeriod
FROM Calendar c
WHERE [day] >= crd.[DateFrom] AND [day] <= crd.[DateTo] AND [WorkDay] = 1 ) calculateCreditDays
ON rel.RelID1 = r.Gid
and CRD.TypeGid = @xtypeCreditGid
cross apply (SELECT
sum(case when [day] between @xFirstDay and @xLastDay then 1 else 0 end) as UsedDays
FROM Calendar c
WHERE ([day] >= r.[DateFrom] AND [day] <= r.[DateTo] AND [WorkDay] = 1))calculateUsedDays
where @xYear = DATEPART(year,r.[DateFrom]) and r.TypeGid = @xtypeLeaveGid and @xIsActive IS NULL OR u.[Active] = @xIsActive
group by u.[Name], u.[DepartmentGid],r.[LeaveTypeGid], ub.[Balance]'
SELECT @paramlist ='
@xTypeOfLeaveGid int,
@xFirstDay datetime,
@xYear int,
@xLastDay datetime,
@xtypeLeaveGid int,
@xrelLeaveToCreditGid int,
@xtypeCreditGid int,
@xIsActive bit'
EXEC sys.sp_executesql @sql, @paramlist,
@TypeOfLeaveGid,
@Year,
@IsActive,
@typeLeaveGid,
@relLeaveToCreditGid,
@typeCreditGid,
@FirstDay,
@LastDay
SET @Err = @@Error
RETURN @Err
END