Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    Kuwait
    Posts
    38

    Question Unanswered: Need help [filtering date ranges]

    Hi guys
    I am trying to build a small module which will return the end user on screen display of employees available within a provided date range (01.01.2005-31.12.2005), against the existing records.

    The existing data from the table are like below:
    Code:
    Empcode	             date_in		date_out	Remark
    0331		10.01.2005	20.01.2005	Engaged
    0331		21.06.2005	27.06.2005	Engaged
    0331		18.12.2005	31.12.2005	Engaged
    And I need second block to display the details as following:
    Code:
    Empcode	             avl_date_from	avl_date_till	Remark
    0331		01.01.2005	09.01.2005	Available
    0331		21.01.2005	20.06.2005	Available
    0331		28.06.2005	17.12.2005	Available
    Basically I was a clipper programmer & I wrote a long procedure (around 30+ lines) with my oracle module to filter out the second set of data I provided here. But I strongly believe Oracle can provide the second data set using its sub-query or nested sub-query feature.

    If anybody can suggest me query syntax to derive the second data set without tedious procedure, it will be highly appreciated.

    Thanks in advance
    Regards
    Last edited by rajthampi; 01-27-05 at 01:47. Reason: Spelling Mistake Data/Date
    If it is a door, It will open.

  2. #2
    Join Date
    Feb 2004
    Location
    Kuwait
    Posts
    38

    Red face Procedure part I wrote for filtering the date ranges

    Code:
    PROCEDURE FILLIT IS
    CURSOR TRD_EMPLS IS SELECT EMP_CODE FROM PROJECT_EMPLOYEE_MASTER WHERE
    TRADE=:PRJ_DTLS.PRJ_TRD;
    CURSOR DATES_AVAILABLE IS 
    SELECT EMPLOYEE_CODE,E_IN_DT,E_OUT_DT FROM 
    PRJ_DTLS WHERE 
    E_IN_DT BETWEEN :PRJ_HDR.PRJ_SDT AND :PRJ_HDR.PRJ_EDT	
    AND E_OUT_DT BETWEEN :PRJ_HDR.PRJ_SDT AND :PRJ_HDR.PRJ_EDT		
    AND PRJ_TRD=:PRJ_DTLS.PRJ_TRD ORDER BY EMPLOYEE_CODE,E_IN_DT;	
    E_CODE VARCHAR2(10);
    EI_DT DATE;
    EO_DT DATE;
    E_COUNT NUMBER;
    PR_E_CODE VARCHAR2(10);
    PR_IN_DT DATE;						
    PR_OUT_DT DATE;						
    REC_NO NUMBER:=0;						
    PR_REC_NO NUMBER;						
    L_RECNO NUMBER;					
    B_DATE DATE;					
    CHK_CODE VARCHAR2(10);					
    D_ENTRY NUMBER;					
    L_DT DATE;M_DT DATE;
    BEGIN
    DELETE FROM EMP_DT_AVL_PRJ;
    OPEN DATES_AVAILABLE;
    LOOP
    FETCH DATES_AVAILABLE INTO E_CODE,EI_DT,EO_DT;
    EXIT WHEN DATES_AVAILABLE%NOTFOUND;
    IF EI_DT=:PRJ_HDR.PRJ_SDT AND EO_DT < :PRJ_HDR.PRJ_EDT THEN  
    	REC_NO:=REC_NO+1;
    	INSERT INTO EMP_DT_AVL_PRJ(EMPLOYEE_CODE,E_IN_DT,E_OUT_DT,ENT_SL_NO)
    	VALUES(E_CODE,(EO_DT+1),:PRJ_HDR.PRJ_EDT,REC_NO);
    ELSIF EI_DT >:PRJ_HDR.PRJ_SDT AND EO_DT=:PRJ_HDR.PRJ_EDT THEN
    	SELECT COUNT(*) INTO E_COUNT FROM EMP_DT_AVL_PRJ WHERE EMPLOYEE_CODE=E_CODE;
    		IF E_COUNT > 0 THEN
    			SELECT MAX(E_IN_DT),MAX(ENT_SL_NO) INTO B_DATE,L_RECNO FROM EMP_DT_AVL_PRJ WHERE
     			EMPLOYEE_CODE=E_CODE;
     				IF EI_DT=B_DATE THEN
     				DELETE FROM EMP_DT_AVL_PRJ
     				WHERE ENT_SL_NO=L_RECNO;
     				ELSIF EI_DT > (B_DATE+1) THEN
     				UPDATE EMP_DT_AVL_PRJ
     				SET E_OUT_DT=(EI_DT-1)
     				WHERE ENT_SL_NO=L_RECNO;
     		--	MESSAGE(E_CODE||EI_DT||B_DATE); PAUSE;
     				ELSIF EI_DT=(B_DATE+1) THEN
    				UPDATE EMP_DT_AVL_PRJ
    				SET E_IN_DT=(EO_DT+1)
    				WHERE ENT_SL_NO=L_RECNO;
    				END IF;
     		ELSE
     				REC_NO:=REC_NO+1;
     		  	INSERT INTO EMP_DT_AVL_PRJ(EMPLOYEE_CODE,E_IN_DT,E_OUT_DT,ENT_SL_NO)
     				VALUES(E_CODE,:PRJ_HDR.PRJ_SDT,(EI_DT-1),REC_NO);
     		END IF; 
    		ELSIF EI_DT > :PRJ_HDR.PRJ_SDT AND EO_DT < :PRJ_HDR.PRJ_EDT THEN
    		 	SELECT COUNT(*) INTO E_COUNT FROM EMP_DT_AVL_PRJ WHERE
     			EMPLOYEE_CODE=E_CODE;
     			IF E_COUNT = 0 THEN
     					REC_NO:=REC_NO+1;
     					INSERT INTO EMP_DT_AVL_PRJ (EMPLOYEE_CODE,E_IN_DT,E_OUT_DT,ENT_SL_NO)
     					VALUES(E_CODE,:PRJ_HDR.PRJ_SDT,(EI_DT-1),REC_NO);
     					REC_NO:=REC_NO+1;
     					INSERT INTO EMP_DT_AVL_PRJ (EMPLOYEE_CODE,E_IN_DT,E_OUT_DT,ENT_SL_NO)
     					VALUES(E_CODE,(EO_DT+1),:PRJ_HDR.PRJ_EDT,REC_NO);
     			ELSE
     					SELECT MAX(E_IN_DT),MAX(ENT_SL_NO) INTO B_DATE,L_RECNO FROM EMP_DT_AVL_PRJ WHERE
     					EMPLOYEE_CODE=E_CODE;
     						IF EI_DT > B_DATE THEN
     						UPDATE EMP_DT_AVL_PRJ
     						SET E_OUT_DT=(EI_DT-1) WHERE
     						ENT_SL_NO=L_RECNO;
     						/*LATEST CHANGE ADDED ON 24JAN2005*/
     						INSERT INTO EMP_DT_AVL_PRJ (EMPLOYEE_CODE,E_IN_DT,E_OUT_DT,ENT_SL_NO)
     						VALUES(E_CODE,(EO_DT+1),:PRJ_HDR.PRJ_EDT,REC_NO);
     						/*CHANGES END*/
     						ELSIF EI_DT=B_DATE THEN
     							UPDATE EMP_DT_AVL_PRJ
     							SET E_IN_DT=EO_DT+1 WHERE
     							ENT_SL_NO=L_RECNO; 							
     						END IF;
     			END IF; 
     END IF;
      END LOOP;
     CLOSE DATES_AVAILABLE;
    	OPEN TRD_EMPLS;
    	LOOP
    			FETCH TRD_EMPLS INTO CHK_CODE;
    			REC_NO:=REC_NO+1;
    			EXIT WHEN TRD_EMPLS%NOTFOUND;
    			SELECT COUNT(*) INTO E_COUNT FROM PRJ_DTLS WHERE
    			EMPLOYEE_CODE=CHK_CODE;
    			IF E_COUNT=0 THEN
    				INSERT INTO EMP_DT_AVL_PRJ
    				VALUES(CHK_CODE,:PRJ_HDR.PRJ_SDT,:PRJ_HDR.PRJ_EDT,:PRJ_HDR.PRJ_SDT,:PRJ_HDR.PRJ_EDT,REC_NO);
    			END IF;
    	END LOOP;
    	CLOSE TRD_EMPLS;
    END;
    I know it is quite long, but I need help
    Thanks again
    If it is a door, It will open.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •