时间戳

首页 > 解决方案 > 为 ProjectManager 列中为“是”的员工返回 PastYrGrossPay 列的总和

sql - 为 ProjectManager 列中为“是”的员工返回 PastYrGrossPay 列的总和

问题描述

我想要做的是从员工表中返回 PastYrGrossPay 列的总和（从 2020 年 1 月 1 日到 2020 年 12 月 31 日（公司在 2020 年之前不存在））。但我只希望它为在Employees 表的ProjectManager 列中具有“是”的员工的PastYrGrossPay 列求和。我也只希望它对 JobStatus 'Completed' （一些 Jobs 说'Incomplete' 但我只想要 ProjectManager 至少有一个'Complete' 工作）在 Jobs 表中的 ProjectManager 求和。这是我的代码：

CREATE PROC spPastYrPay
    @SDate date = NULL,
    @EDate date = '12/31/2020',
    @PM varchar(3) = 'Yes',
    @Comp varchar(50) = 'Complete'
AS
BEGIN
    IF @SDate IS NULL
        SELECT @SDate = MIN(StartDate) FROM Employees

    DECLARE @TotPastYrPayroll money

    SELECT @TotPastYrPayroll = SUM(PastYrGrossPay)
    FROM Employees e
    JOIN Departments d ON e.EmpID = d.EmpID
    JOIN Jobs j ON d.DeptID = j.DeptID
    WHERE ((StartDate BETWEEN @SDate AND @EDate) AND (ProjectManager LIKE @PM)
    AND (JobStatus LIKE @Comp))

    RETURN @TotPastYrPayroll
END

USE ABC_Mechanical
DECLARE @TotPastYrPayroll money
EXEC @TotPastYrPayroll = spPastYrPay
PRINT 'Project Managers (with at least one completed job) total payroll for the past year: $'
+ CONVERT(varchar, @TotPastYrPayroll, 1)
GO

标签： sqlsql-servertsqlstored-procedures

解决方案

我在这里看到的是，您不需要传递所有这些参数，因为它们是预定义的。您可以在 where 子句中将它们作为过滤器传递，因为它们在您的问题中是固定的。如果它们不同，那么您可以将它们作为参数传递。

SP 的好处是您可以有许多输出参数。因此，在您的情况下，您可以使用输出参数。

即使没有输出参数，您也可以通过返回变量来做到这一点。就像选择@TotPastYrPayroll As [totalPayroll]

CREATE PROCEDURE spPastYrPay (
 @SDate date = NULL,
@EDate date = '12/31/2020',
@PM varchar(3) = 'Yes',
@Comp varchar(50) = 'Complete',
@TotPastYrPayroll money  OUTPUT
) AS
BEGIN
 
 IF @SDate IS NULL
    SELECT @SDate = MIN(StartDate) FROM Employees

    
   SELECT @TotPastYrPayroll = SUM(PastYrGrossPay)
   FROM Employees e
   JOIN Departments d ON e.EmpID = d.EmpID
   JOIN Jobs j ON d.DeptID = j.DeptID
   WHERE ((StartDate BETWEEN @SDate AND @EDate) AND (ProjectManager = @PM)
  AND (JobStatus LIKE @Comp))

END;

使用输出参数执行存储过程

DECLARE @PastPayroll MONEY;

EXEC spPastYrPay
   @SDate = NULL,
   @EDate =  '12/31/2020',
   @PM = 'Yes',
   @Comp= 'Complete'
   @product_count = @PastPayroll OUTPUT;

SELECT @product_count AS 'Sum Of Payroll';

推荐阅读