-- Function: public.calculateexperiencenew(integer)-- DROP FUNCTION public.calculateexperiencenew(integer);CREATEORREPLACEFUNCTIONpublic.calculateexperiencenew(integer)RETURNSintegerAS$BODY$Declaretotalnumeric(14,4):=0;r_ExperienceRECORD;starttimedate;endtimedate;mindatedate;maxdatedate;olddatedate;subtractvalueINT4:=0;diffINT4:=0;tevta_resumeINT4:=0;Beginselectintomindatestart_datefromexperiencewhereresume_id=$1orderbystart_datelimit1;selectintomaxdateend_datefromexperiencewhereresume_id=$1orderbyend_datedesclimit1;ifmaxdateisnullthenselectintodiffcurrent_date-mindate;total=diff;elseselectintodiffmaxdate-mindate;total=diff;endif;forr_ExperienceINselectstart_date,end_datefromexperiencewhereresume_id=$1orderbystart_dateloopendtime=r_Experience.end_date;starttime=r_Experience.start_date;tevta_resume=$1;ifendtimeisnullthenendtime=current_date;endif;ifolddateisnullthenolddate=endtime;endif;if(starttime>olddate)thenselectintodiffstarttime-olddate;subtractvalue=subtractvalue+diff;endif;olddate=endtime;ENDLOOP;total=total-subtractvalue;total=total/365;updateresumesetexperience=totalwhereresume_id=$1;returntevta_resume;End;$BODY$LANGUAGEplpgsqlVOLATILECOST100;ALTERFUNCTIONpublic.calculateexperiencenew(integer)OWNERTOjawad;
Comments (0)
HTTPSSSH
You can clone a snippet to your computer for local editing.
Learn more.