CREATE PROCEDURE ADMINISTRATOR.GetHoursForAppStatus (  )
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN

    DECLARE v_appid Char(10);
    DECLARE v_appdescription varchar(255);
    DECLARE v_createdate date;
    DECLARE v_openclosed varchar(20);
    DECLARE v_statusid integer;
    DECLARE v_enterdatetime date;
    DECLARE v_exitdatetime date;
    DECLARE v_duration bigint;
    DECLARE v_p1 varchar(50);
    DECLARE v_p2 varchar(50);

    DECLARE c1 CURSOR FOR
        SELECT app.appid, app.appdescription, app.createdate, app.openclosed, appSt.statusid, appSt.enterdatetime, appSt.exitdatetime
        FROM emerchapplication app
        left join eMerchApplicationStatus appSt on appSt.appId = app.appId
        WHERE openclosed = 'Open'
        ORDER BY appid;

    declare global temporary table Temp1
     (appid char(10), appdescription  varchar(255), createdate date, openclosed varchar(20), statusid integer,
     statusduration varchar (20), v_indicator varchar(10))
     on commit preserve rows not logged ;

    OPEN c1;
    fetch_loop:
    LOOP
        FETCH c1 INTO
                v_appid, v_appdescription, v_createdate, v_openclosed, v_statusid, v_enterdatetime, v_exitdatetime;

                 SET v_p1 = cast(year(v_enterdatetime) as char) || '-' || cast(month(v_enterdatetime) as char) || '-' || cast((DAY(v_enterdatetime) + 1) as char) || ' 00:00:00';
	         SET v_p2 = cast(year(v_exitdatetime) as char) || '-' || cast(MONTH(v_exitdatetime) as char) || '-' || cast(DAY(v_exitdatetime) as char) || ' 00:00:00';

	IF (DAY(v_enterdatetime) = DAY(v_exitdatetime) and MONTH(v_enterdatetime) = MONTH(v_exitdatetime) and YEAR(v_enterdatetime) = YEAR(v_exitdatetime)) THEN
		SET v_duration = 0;
	ELSE
	--	SET v_duration = SELECT SUM(nc.workingHours) AS tempDuration from NABCalendar nc where nc.calDate>cast(v_p1 as date) and nc.calDate<cast(v_p2 as date);
		SET v_duration = (SELECT SUM(nc.workingHours) AS tempDuration from NABCalendar nc where nc.calDate>cast(v_p1 as date) and nc.calDate<cast(v_p2 as date));
	END IF;

        insert into SESSION.Temp1 (appid, appdescription, createdate, openclosed, statusid, statusduration, v_indicator)
        values(v_appid, v_appdescription, v_createdate, v_openclosed, v_statusid, v_duration, v_indicator);
    END LOOP fetch_loop;
    CLOSE c1;

    DECLARE SP_SQL1 CURSOR WITH RETURN FOR
    SELECT * FROM SESSION.TEMP1;
    OPEN SP_SQL1;

END P1