Program for Student Log time calculation..

Hi All,

Today we are going to discuss about the log hours in a school ,


Create table std_logs
(
SID    NUMBER,
IN_TIME    DATE,
OUT_TIME    DATE
);

/

create or replace PROCEDURE std_log_time
(
   P_STD_ID NUMBER
)

AS
v_count_sid number(10) := 0;
v_sid NUMBER;
v_in_time DATE;
v_out_time DATE;
V_RN NUMBER(10);
BEGIN
     SELECT count(SID) INTO v_count_sid FROM std_logs WHERE SID = p_std_id ;


     IF (v_count_sid >0 ) THEN
    
              SELECT A.rn,A.in_time INTO v_rn,v_in_time
           FROM   (SELECT ROWNUM rn,in_time
                   FROM std_logs
                   WHERE SID= p_std_id
                   ORDER BY in_time DESC ) A
           WHERE A.rn =1;
          
         
                 IF ((to_number(to_char(SYSDATE,'dd')) =  to_number(to_char(v_in_time,'dd'))) and (to_number(to_char(SYSDATE,'mm')) =  to_number(to_char(v_in_time,'mm'))) and (to_number(to_char(SYSDATE,'yyyy')) =  to_number(to_char(v_in_time,'yyyy')))) THEN
                 --INSERT INTO std_logs VALUES(p_std_id,SYSDATE,NULL);
                 UPDATE std_logs SET out_time = SYSDATE WHERE SID = p_std_id;
                 ELSE
                 
                    INSERT INTO std_logs VALUES(p_std_id,SYSDATE,NULL);
               END IF;
     ELSE
   
          INSERT INTO std_logs VALUES(p_std_id,SYSDATE,NULL);
     end if;


END;


/
This is how the student information is going to be captured by the Biometric systems.

EXECUTE std_log_time(999);

SELECT s.*,floor((s.out_time-s.in_time)*24)||'Hrs,'
         ||floor(((s.out_time-s.in_time)*24 - floor((s.out_time-s.in_time)*24))*60)||'Mins.' diff_time
FROM std_logs s;