使用交叉应用函数

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

我尝试使用sum和group by，但它返回错误：

每个组按表达式必须至少包含一个不是外部引用的列。

(谢谢：)

Answer 1

谢谢，我解决了这个问题，但现在我的问题是:不允许从数据类型datetime到int的隐式转换。使用转换函数运行此查询。

这是我的密码

    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