|
micnie_2020
Posting Yak Master
232 Posts |
 Posted - 2013-06-10 : 23:57:17
|
| Hi Sir,I got table below:-Create table testLeave( UserID varchar(4), DateJoin Datetime null, LeaveType varchar(3), [Start_Date] Datetime null, EnDate Datetime null, LeaveBFDate_End Datetime null, startday int null, rulesincrementyr int null, increday int null, maxday int null )insert into testLeave ('A001','01/Aug/2008','ANN','01/Apr/1990','31/Mar/1991','30/Jun/1991',10,'2','2','18')insert into testLeave ('A002','01/Feb/2009','ANN','01/Apr/1990','31/Mar/1991','30/Jun/1991',10,'2','2','18')insert into testLeave ('A003','01/Mar/2013','ANN','01/Apr/1990','31/Mar/1991','30/Jun/1991',10,'2','2','18')Create LeaveEntitlement( UserID varchar(4), LeaveType varchar(3), LeaveYr DateTime null, EntitlementDay float null, BFDay float null, ForfeitDay float null LeaveTaken float null)How to create the SP to populate the LeaveEntitlement for each fiscal year, Join Date 2 year get 2 day add on leave from startday but Max only up to 18? Example my fiscal year set on the testLeave is 01/Apr ~ 31/Mar each year. The SP will trigger on 01/Apr every year @ morning 12.01am based on local timezone.Example output for LeaveEntitlement:-UserID, LeaveType, LeaveYr,EntitlementDay, BFDay, ForfeithDay, Leave TakenA001,ANN,01/Apr/2008,ProrateCalculation(DateJoin '01/Aug/2008'),0,0,3A001,ANN,01/Apr/2009,10,?,?,null::Please advise.Thank you. |
|
Leave Entitlement Calculation