Hi All,
I need a query which can get leaves of an employee of days in each month.
Example:
1.
If an employee A has leave from from date_start = '15-jan-2014' , date_end = '30-jan-2014' which is in the same month then it will be
duration = 30 - 15 + 1 = 16 days
Total_days_in_jan = 31 days
Leave salary calculation will be Amount = (:basic + :housing)* (:duration)/(Total_days_in_jan)
= (1000 + 2000) * 16/31
2. If its two month date_start = '15-jan-2014' , date_end = '10-feb-2014'
I wrote the below code which is working perfect
if to_char(:date_start,'mm') = to_char(:date_end,'mm') then
v_mon_days := TO_CHAR(last_day(TO_DATE(:date_start,'DD-MON-YYYY')),'DD') ;
v_tot := ( nvl(:BASIC1,0) + nvl(:FUJ_LIVING,0) )*(:Duration_)/v_mon_days;
elsif to_char(:date_start,'mm') <> to_char(:date_end,'mm') then
v_mon_days_st_dt := TO_CHAR(last_day(:date_start),'DD');
v_mon_days_en_dt := TO_CHAR(last_day(:date_end),'DD');
v_lv_days_st_dt := last_day(:date_start) - :date_start + 1;
v_lv_days_en_dt := :date_end - trunc(:date_end,'mm') + 1;
v_tot_st_dt := (nvl(:BASIC1,0) + nvl(:FUJ_LIVING,0))*(v_lv_days_st_dt)/v_mon_days_st_dt;
v_tot_en_dt := (nvl(:BASIC1,0) + nvl(:FUJ_LIVING,0))*(v_lv_days_en_dt)/v_mon_days_en_dt;
v_tot := nvl(v_tot_st_dt,0) + nvl(v_tot_en_dt,0);
end if;
return round(v_tot);
3. If the leave in 3 months, let say date_start = 24-may-2014 date_end = 02-july-2014 ,
I need help here...what if the leave in 4 months also..
Kindly suggest.
Regards,
Afzal.