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
Thanks in Advance