How can i do cumulative subtraction for the table based on multiple col's

• aggy_02_20

  SSC Enthusiast

  Points: 176

  More actions

  March 30, 2021 at 9:19 am

  #3863530

  Hi

  I have a leave table where there are RL (rem leaves),AL (allocated leaves) as follows

  DECLARE @E_LEAVE TABLE (LID VARCHAR(20),EID VARCHAR(20),DAYSS DECIMAL(18,2),LTYP VARCHAR(20),ALLO DECIMAL(18,2),RL DECIMAL(18,2))
  INSERT INTO @E_LEAVE VALUES ( 'L001','E001' ,'4.00','AL','21.00','17.00')
  INSERT INTO @E_LEAVE VALUES ( 'L002','E001' ,'2.00','AL','21.00','0.00')
  INSERT INTO @E_LEAVE VALUES ( 'L007','E003' ,'5.00','AL','21.00','0.00')
  INSERT INTO @E_LEAVE VALUES ( 'L005','E002' ,'6.00','AL','21.00','0.00')
  INSERT INTO @E_LEAVE VALUES ( 'L006','E002' ,'4.00','ML','14.00','0.00')
  INSERT INTO @E_LEAVE VALUES ( 'L008','E003' ,'3.00','ML','14.00','0.00')
  INSERT INTO @E_LEAVE VALUES ( 'L009','E003' ,'2.00','ML','14.00','0.00')
  INSERT INTO @E_LEAVE VALUES ( 'L003','E001' ,'2.00','ML','14.00','0.00')
  INSERT INTO @E_LEAVE VALUES ( 'L004','E001' ,'1.00','ML','14.00','0.00')
  
  select * from  @E_LEAVE
  

  how can i calculate cumulative remaining leaves by employee and leave type per leave as shown

  Eg: let say i have 2 days AL for 1st employee for the first time i have ALLOCATED LEAVE i need to substract from 21 =21-2 =19 which is RL for the first record, Now this 19 will be ALLOCATED LEAVE for the 1st employee  and so on..

  DECLARE @req_tab_LEAVE TABLE (LID VARCHAR(20),EID VARCHAR(20),DAYSS DECIMAL(18,2),LTYP VARCHAR(20),ALLO DECIMAL(18,2),RL DECIMAL(18,2))
  INSERT INTO @req_tab_LEAVE VALUES ( 'L001','E001' ,'4.00','AL','21.00','17.00')
  INSERT INTO @req_tab_LEAVE VALUES ( 'L002','E001' ,'2.00','AL','17.00','15.00')
  INSERT INTO @req_tab_LEAVE VALUES ( 'L007','E003' ,'5.00','AL','21.00','17.00')
  INSERT INTO @req_tab_LEAVE VALUES ( 'L005','E002' ,'6.00','AL','21.00','17.00')
  INSERT INTO @req_tab_LEAVE VALUES ( 'L006','E002' ,'4.00','ML','14.00','10.00')
  INSERT INTO @req_tab_LEAVE VALUES ( 'L008','E003' ,'3.00','ML','14.00','11.00')
  INSERT INTO @req_tab_LEAVE VALUES ( 'L009','E003' ,'2.00','ML','11.00','9.00')
  INSERT INTO @req_tab_LEAVE VALUES ( 'L003','E001' ,'2.00','ML','14.00','12.00')
  INSERT INTO @req_tab_LEAVE VALUES ( 'L004','E001' ,'1.00','ML','12.00','11.00')
  
  select * from  @req_tab_LEAVE

  Kindly help me with this...Thanks in Advance

• Steve Collins

  SSCrazy

  Points: 2068

  More actions

  March 30, 2021 at 1:57 pm

  #3863606

  Here's something close but not matching because maybe there's an issue with the example results?  Could you explain for LID='L005' and EID='E002' how ALLO='21.00' and DAYSS='6.00' and then the RL ends up being '17.00'?  If the RL should've been '15.00' then maybe something like this.  Please note an ORDER BY doesn't seem possible to match the apparent ordering of the rows.

  with 
  rn_cte as (
      select *, 
             row_number() over (partition by EID, LTYP order by LID) rn
      from @E_LEAVE)
  select LID, EID, DAYSS, LTYP, ALLO, 
         sum((case when rn=1 then allo else 0 end)-dayss)
              over (partition by EID, LTYP order by LID) RL
  from rn_cte
  order by LTYP;

  LIDEIDDAYSSLTYPALLORL
  L001E0014.00AL21.0017.00
  L002E0012.00AL21.0015.00
  L005E0026.00AL21.0015.00
  L007E0035.00AL21.0016.00
  L008E0033.00ML14.0011.00
  L009E0032.00ML14.009.00
  L006E0024.00ML14.0010.00
  L003E0012.00ML14.0012.00
  L004E0011.00ML14.0011.00

  • This reply was modified 3 years ago by  Steve Collins. Reason: Added ORDER BY and simplified query

  Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

• April 2, 2021 at 8:21 am

  This was removed by the editor as SPAM