以下是我数据集的代码：
WITH 部门信息 AS ( SELECT a.职工编号, b.部门名称 FROM dbo. a LEFT JOIN dbo. b ON a.部门编号 = b.部门编号 WHERE a.离厂日期 IS NULL),a AS ( SELECT Number_Employee AS 职工编号, 职工姓名, SUM(CASE WHEN Payment_General IS NULL THEN 0 WHEN ISNUMERIC(Payment_General) = 1 AND Payment_General NOT LIKE '%%' THEN CAST(Payment_General AS DECIMAL(10,2)) ELSE 0 END) AS 合计金额, CONVERT(varchar(100), Date, 23) AS 日期, Number_Order AS 订单编号, SUM(Amount) AS 数量 FROM dbo. a WHERE CONVERT(varchar(100), Date, 23) = '${finddate}' GROUP BY Number_Employee, 职工姓名, CONVERT(varchar(100), Date, 23), Number_Order
UNION ALL
SELECT 职工编号, 职工姓名, SUM(CASE WHEN 合计金额 IS NULL THEN 0 WHEN ISNUMERIC(合计金额) = 1 AND 合计金额 NOT LIKE '%%' THEN CAST(合计金额 AS DECIMAL(10,2)) ELSE 0 END) AS 合计金额, CONVERT(varchar(100), 日期, 23) AS 日期, 订单编号 AS 订单编号, SUM(计件数量) AS 数量 FROM dbo. a WHERE CONVERT(varchar(100), 日期, 23) = '${finddate}' GROUP BY 职工编号, 职工姓名, CONVERT(varchar(100), 日期, 23), 订单编号
UNION ALL
SELECT Number_Employee AS 职工编号, 职工姓名, SUM(CASE WHEN Payment_Total IS NULL AND Payment_General IS NULL THEN 0 WHEN ISNUMERIC(Payment_Total) = 1 AND Payment_Total NOT LIKE '%%' THEN CAST(Payment_Total AS DECIMAL(10,2)) WHEN ISNUMERIC(Payment_General) = 1 AND Payment_General NOT LIKE '%%' THEN CAST(Payment_General AS DECIMAL(10,2)) ELSE 0 END) AS 合计金额, CONVERT(varchar(100), Date, 23) AS 日期, Number_Order AS 订单编号, SUM(Amount_Fitting) AS 数量 FROM dbo. a WHERE CONVERT(varchar(100), Date, 23) = '${finddate}' GROUP BY Number_Employee, 职工姓名, CONVERT(varchar(100), Date, 23), Number_Order UNION ALL
SELECT Number_Employee AS 职工编号, 职工姓名, SUM(CASE WHEN Payment_Total IS NULL AND Payment_General IS NULL THEN 0 WHEN ISNUMERIC(Payment_Total) = 1 AND Payment_Total NOT LIKE '%%' THEN CAST(Payment_Total AS DECIMAL(10,2)) WHEN ISNUMERIC(Payment_General) = 1 AND Payment_General NOT LIKE '%%' THEN CAST(Payment_General AS DECIMAL(10,2)) ELSE 0 END) AS 合计金额, CONVERT(varchar(100), Date, 23) AS 日期, Number_Order AS 订单编号, SUM(Amount_Fitting) AS 数量 FROM dbo. a WHERE CONVERT(varchar(100), Date, 23) = '${finddate}' GROUP BY Number_Employee, 职工姓名, CONVERT(varchar(100), Date, 23), Number_Order
UNION ALL
SELECT 职工编号, 职工姓名, SUM(CASE WHEN 金额 IS NULL THEN 0 WHEN ISNUMERIC(金额) = 1 AND 金额 NOT LIKE '%%' THEN CAST(金额 AS DECIMAL(10,2)) ELSE 0 END) AS 合计金额, CONVERT(varchar(100), 日期, 23) AS 日期, 订单编号 AS 订单编号, SUM(数量) AS 数量 FROM dbo. a WHERE CONVERT(varchar(100), 日期, 23) = '${finddate}' GROUP BY 职工编号, 职工姓名, CONVERT(varchar(100), 日期, 23), 订单编号
UNION ALL
SELECT Number_Employee AS 职工编号, Name_Employee AS 职工姓名, CONVERT(varchar(100), Date, 23) AS 日期, Number_Order AS 订单编号, SUM(Amount) AS 数量, SUM(CASE WHEN Wage IS NULL THEN 0 WHEN ISNUMERIC(Wage) = 1 AND Wage NOT LIKE '%%' THEN CAST(Wage AS DECIMAL(10,2)) ELSE 0 END) AS 合计金额 FROM dbo. a WHERE CONVERT(varchar(100), Date, 23) = '${finddate}' GROUP BY Number_Employee, Name_Employee, CONVERT(varchar(100), Date, 23), Number_Order
UNION ALL
SELECT EmployeeNumber AS 职工编号, 职工姓名, SUM(CASE WHEN Wage IS NULL THEN 0 WHEN ISNUMERIC(Wage) = 1 AND Wage NOT LIKE '%%' THEN CAST(Wage AS DECIMAL(10,2)) ELSE 0 END) AS 合计金额, CONVERT(varchar(100), Date, 23) AS 日期, OrderNumber AS 订单编号, SUM(Quantity) AS 数量 FROM dbo. a WHERE CONVERT(varchar(100), Date, 23) = '${finddate}' GROUP BY EmployeeNumber, 职工姓名, CONVERT(varchar(100), Date, 23), OrderNumber
UNION ALL
SELECT Number_Employee AS 职工编号, Name_Employee AS 职工姓名, SUM(CASE WHEN Wage1 IS NULL THEN 0 WHEN ISNUMERIC(Wage1) = 1 AND Wage1 NOT LIKE '%%' THEN CAST(Wage1 AS DECIMAL(10,2)) ELSE 0 END) AS 合计金额, CONVERT(varchar(100), Date, 23) AS 日期, Number_Order AS 订单编号, SUM(Amount1) AS 数量 FROM dbo. a WHERE CONVERT(varchar(100), Date, 23) = '${finddate}' GROUP BY Number_Employee, Name_Employee, CONVERT(varchar(100), Date, 23), Number_Order
UNION ALL
SELECT 职工编号, 职工姓名, SUM(CASE WHEN 合计金额 IS NULL THEN 0 WHEN ISNUMERIC(合计金额) = 1 AND 合计金额 NOT LIKE '%%' THEN CAST(合计金额 AS DECIMAL(10,2)) ELSE 0 END) AS 合计金额, CONVERT(varchar(100), 日期, 23) AS 日期, a.订单编号 AS 订单编号, SUM(计件数量) AS 数量 FROM dbo. a WHERE CONVERT(varchar(100), 日期, 23) = '${finddate}' GROUP BY 职工编号, 职工姓名, CONVERT(varchar(100), 日期, 23), a.订单编号)SELECT a.*, d.部门名称FROM aLEFT JOIN 部门信息 d ON a.职工编号 = d.职工编号WHERE ${if(len(department) == 0, '1=1', "d.部门名称 IN ('" + replace(department, ",", "','") + "')")}
