CODEWITHSUNDEEP
sqlsql-serverlagrow-numberunbound
Vignesh Kumar A
Vignesh Kumar A

Reputation: 28413

Calculate row values dynamically based on previous row result

I have table with project allocation for each users based on resource Id. We are creating some repot which needs to get the over allocation values by deducting the Staffed value from Demand value.

The demand value is unique based on combination of 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      |
+-----------------------------------------------------------------------------------------------------+

Expected Output:

+-----------------------------------------------------------------------------------------------------------------------------------------+
| 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      |
+-----------------------------------------------------------------------------------------------------------------------------------------+

I have to derive OverStaffed01 = Demand01 - Staffed01 but there is once constrain, if we have duplicate Number and ResourceId, We have to adjust the demand values based on previous row.

For Example:

Combination of Number:RR-00000002 and ResourceId:1019096 has three rows, so we can't directly calculate the value.

For First row,(OverStaffed01 = Demand01 - Staffed01) 0.40 - 0.30 is greaten than 0 so which is not over staffed and we considered as 0.

For Second row (OverStaffed01 = Demand01 - Staffed01) 0.10 - 0.70 (Because we already reduced 0.30 from first row, so we have Demand01 as 0.10) and OverStaffed01 is -0.60

Combination of Number:RR-00000001 and ResourceId:1019094 has two rows, so we can't directly calculate the value.

For First row,(OverStaffed01 = Demand01 - Staffed01) 0.01 - 0.30 is -0.29.

For Second row (OverStaffed01 = Demand01 - Staffed01) 0.00 - 0.40 (Because we already reduced 0.01 from first row, so we have Demand01 as 0) and OverStaffed01 is -0.40

For third row (OverStaffed01 = Demand01 - Staffed01) 0.00 - 0.30 (Because we already reduced 0.01 from first row, so we have Demand01 as 0) and OverStaffed01 is -0.30

For other rows , we have unique Number and ResourceId, so we can directly deduct and calculate OverStaffed01

Query tried so far:

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

Update

I have posted my recent updated query, which gives somehow correct result but still not expected result.

Upvotes: 0

Views: 648

Answers (1)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15905

Schema:

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')

Query:

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

Output:

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 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
RR-00000002 1025136 0.00 0.10 0.00 0.20 0.00 -0.10 Naveen RR/B
RR-00000003 1020258 0.01 0.01 0.90 0.90 -0.89 -0.89 Kumar RR/C

db<>fiddle here

Upvotes: 2

Related Questions