Используйте функцию перекрестного применения

Question

У меня есть этот перекрестный запрос и я хочу суммировать его результат

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 должно содержать хотя бы один столбец, который не является внешней ссылкой

Спасибо:))

Answer 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