我有一个表，根据资源Id为每个用户分配项目。我们正在创建一些报表，需要通过从Demand值中减去Staffed值来获得超额分配值。
需求值基于Number、ResourceId的组合是唯一的。
+-----------------------------------------------------------------------------------------------------+
| Number | ResourceId | Demand01 | Demand02 | Staffed01 | Staffed02 | AssociateName | GroupName |
+-----------------------------------------------------------------------------------------------------+
| RR-00000001 | 1019094 | 0.40 | 0.40 | 0.30 | 0.30 | Raja | RR/A |
| RR-00000001 | 1019094 | 0.40 | 0.40 | 0.70 | 0.70 | Praveen | RR/A |
| RR-00000001 | 1020688 | 0.00 | 0.00 | 0.12 | 1.00 | Bala | RR/A |
| RR-00000002 | 1025136 | 0.00 | 0.00 | 0.00 | 0.00 | Naveen | RR/B |
| RR-00000003 | 1020258 | 0.01 | 0.01 | 0.90 | 0.90 | Kumar | RR/C |
| RR-00000002 | 1019096 | 0.01 | 0.01 | 0.30 | 0.30 | Arun | RR/D |
| RR-00000002 | 1019096 | 0.01 | 0.01 | 0.70 | 0.70 | BBB | RR/E |
| RR-00000002 | 1019096 | 0.01 | 0.01 | 0.30 | 0.30 | CCC | RR/E |
+-----------------------------------------------------------------------------------------------------+
预期输出：
+-----------------------------------------------------------------------------------------------------------------------------------------+
| Number | ResourceId | Demand01 | Demand02 | Staffed01 | Staffed02 | OverStaffed01 | OverStaffed02 | AssociateName | GroupName |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| RR-00000001 | 1019094 | 0.40 | 0.40 | 0.30 | 0.30 | 0.00 | 0.00 | Raja | RR/A |
| RR-00000001 | 1019094 | 0.40 | 0.40 | 0.70 | 0.70 | -0.60 | -0.60 | Praveen | RR/A |
| RR-00000001 | 1020688 | 0.00 | 0.00 | 0.12 | 1.00 | -0.12 | -1.00 | Bala | RR/A |
| RR-00000002 | 1025136 | 0.00 | 0.10 | 0.00 | 0.20 | 0.00 | -0.20 | Naveen | RR/B |
| RR-00000003 | 1020258 | 0.01 | 0.01 | 0.90 | 0.90 | -0.89 | -0.89 | Kumar | RR/C |
| RR-00000002 | 1019096 | 0.01 | 0.01 | 0.30 | 0.30 | -0.29 | -0.29 | Arun | RR/D |
| RR-00000002 | 1019096 | 0.01 | 0.01 | 0.40 | 0.40 | -0.40 | -0.40 | BBB | RR/E |
| RR-00000002 | 1019096 | 0.01 | 0.01 | 0.30 | 0.30 | -0.30 | -0.30 | CCC | RR/E |
+-----------------------------------------------------------------------------------------------------------------------------------------+
我必须导出OverStaffed01 = Demand01 - Staffed01，但有一个约束，如果我们有重复的Number和ResourceId，我们必须根据前一行调整需求值。
例如：
Number:RR-00000002 and ResourceId:1019096的组合有三行，所以我们不能直接计算值。
对于第一行，(OverStaffed01=Demand01-Staffed01)0.40-0.30为大于0，所以这并不是人员过剩，我们认为是0。
对于第二行(OverStaffed01=需求01-员工01)0.10-0.70(因为我们已经从第一排减少了0.30，所以我们有Demand01为0.10)，OverStaffed01为-0.60
Number:RR-00000001 and ResourceId:1019094的组合有两行，所以我们不能直接计算值。
对于第一行，(OverStaffed01=Demand01-Staffed01)0.01-0.30为-0.29。
对于第二行(OverStaffed01=需求01-员工01)0.00-0.40(因为我们已经从第一排减少了0.01，所以我们有Demand01为0)，OverStaffed01为-0.40
对于第三行(OverStaffed01=需求01-员工01)0.00-0.30(因为我们已经从第一排减少了0.01，所以我们有Demand01为0)，OverStaffed01为-0.30
对于其他行，我们有唯一的Number和ResourceId，因此我们可以直接推导和计算OverStaffed01
迄今为止已尝试查询：
CREATE TABLE table1
(
Number varchar(100),
ResourceId varchar(100),
Demand01 decimal(18,2),
Demand02 decimal(18,2),
Staffed01 decimal(18,2),
Staffed02 decimal(18,2),
AssociateName varchar(100),
GroupName varchar(100)
)
INSERT INTO table1 VALUES('RR-00000001','1019094', '0.40', '0.40', '0.30', '0.30', 'Raja', 'RR/A')
INSERT INTO table1 VALUES('RR-00000001','1019094', '0.40', '0.40', '0.70', '0.70', 'Praveen', 'RR/A')
INSERT INTO table1 VALUES('RR-00000001','1020688', '0.00', '0.00', '0.12', '1.00', 'Bala', 'RR/A')
INSERT INTO table1 VALUES('RR-00000002','1025136', '0.00', '0.10', '0.00', '0.20', 'Naveen', 'RR/B')
INSERT INTO table1 VALUES('RR-00000003','1020258', '0.01', '0.01', '0.90', '0.90', 'Kumar', 'RR/C')
INSERT INTO table1 VALUES('RR-00000002','1019096', '0.01', '0.01', '0.30', '0.30', 'Arun', 'RR/D')
INSERT INTO table1 VALUES('RR-00000002','1019096', '0.01', '0.01', '0.40', '0.40', 'BBB', 'RR/E')
INSERT INTO table1 VALUES('RR-00000002','1019096', '0.01', '0.01', '0.30', '0.30', 'CCC', 'RR/E')
SELECT Number, ResourceId, Demand01, Staffed01, AssociateName, GroupName,
ROW_NUMBER() OVER (PARTITION BY Number, ResourceId ORDER BY (Demand01 - Staffed01) DESC, (Demand02 - Staffed02) DESC) RN,
Demand01- SUM (Staffed01) OVER (PARTITION BY Number, ResourceId ORDER BY (SELECT NULL) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS OverStaffed01,
Demand02- SUM (Staffed02) OVER (PARTITION BY Number, ResourceId ORDER BY (SELECT NULL) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS OverStaffed02
FROM table1
更新
我已经发布了我最近更新的查询，它在某种程度上给出了正确的结果，但仍然不是预期的结果。
模式：
CREATE TABLE table1 (
Number varchar(100),
ResourceId varchar(100),
Demand01 decimal(18,2),
Demand02 decimal(18,2),
Staffed01 decimal(18,2),
Staffed02 decimal(18,2),
AssociateName varchar(100),
GroupName varchar(100)
)
INSERT INTO table1 VALUES('RR-00000001','1019094', '0.40', '0.40', '0.30', '0.30', 'Raja', 'RR/A')
INSERT INTO table1 VALUES('RR-00000001','1019094', '0.40', '0.40', '0.70', '0.70', 'Praveen', 'RR/A')
INSERT INTO table1 VALUES('RR-00000001','1020688', '0.00', '0.00', '0.12', '1.00', 'Bala', 'RR/A')
INSERT INTO table1 VALUES('RR-00000002','1025136', '0.00', '0.10', '0.00', '0.20', 'Naveen', 'RR/B')
INSERT INTO table1 VALUES('RR-00000003','1020258', '0.01', '0.01', '0.90', '0.90', 'Kumar', 'RR/C')
INSERT INTO table1 VALUES('RR-00000002','1019096', '0.01', '0.01', '0.30', '0.30', 'Arun', 'RR/D')
INSERT INTO table1 VALUES('RR-00000002','1019096', '0.01', '0.01', '0.40', '0.40', 'BBB', 'RR/E')
INSERT INTO table1 VALUES('RR-00000002','1019096', '0.01', '0.01', '0.30', '0.30', 'CCC', 'RR/E')
查询：
with cte as (
SELECT Number, ResourceId, Demand01, Demand02, Staffed01, Staffed02, AssociateName, GroupName,RN,
coalesce(sum(staffed01)over (partition by number,resourceid order by rn rows between unbounded preceding and 1 preceding) ,0) PreviousStaffed01,
coalesce(sum(staffed02)over (partition by number,resourceid order by rn rows between unbounded preceding and 1 preceding) ,0) PreviousStaffed02
FROM
(
SELECT Number, ResourceId, Demand01, Demand02, Staffed01, Staffed02, AssociateName, GroupName,
ROW_NUMBER() OVER (PARTITION BY Number, ResourceId ORDER BY (SELECT NULL)) RN
FROM table1
) X
)
select Number, ResourceId, Demand01, Demand02, Staffed01, Staffed02
, (case when ((case when (demand01-previousstaffed01) <0 then 0 else (demand01-previousstaffed01)end) -staffed01)<0
then ((case when (demand01-previousstaffed01) <0 then 0 else (demand01-previousstaffed01)end) -staffed01) else 0 end) OverStaffed01
, (case when ((case when (demand02-previousstaffed02) <0 then 0 else (demand02-previousstaffed02)end) -staffed02)<0
then ((case when (demand02-previousstaffed02) <0 then 0 else (demand02-previousstaffed02)end) -staffed02) else 0 end) OverStaffed02
, AssociateName, GroupName
from cte
输出：
| 编号 | 员工过多01 | AssociateName | 组名 | ||
|---|---|---|---|---|---|
| RR-000001 | 0.00 | Raja | RR/A | ||
| RR-000000 1 | 1019094 | 0.40 | 0.70 | >td style="text align:right；">0.70Praveen | RR/A |
| RR-000000 1 | Bala | RR/A | |||
| RR-000002 | Arun | RR/D | |||
| RR-000002 | BBB；">RR/E | ||||
| RR-000002 | CCC | RR/E | |||
| RR-000002 | 导航 | ||||
| RR-000000 3 | Kumar="text align=left；">RR/C |