OraFAQ Forum: SQL & PL/SQL » Staff Lft Company and display his pervisos recoreds

Home » SQL & PL/SQL » SQL & PL/SQL » Staff Lft Company and display his pervisos recoreds

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Staff Lft Company and display his pervisos recoreds [message #645968]

Thu, 17 December 2015 03:33

skorpion
Messages: 1
Registered: December 2015

Junior Member

I have a table more thn 6 table join where you can use Employees to search for
who have working the company and who left ... who left the company we
use the "XYZ" code
but there is a problem when i use this query it show me the last base
the employee worked at if someone left the company and we give him code
"XYZ".

For Example if i book an advanced Annual Leave for November 2015 but I
leave the company in August 2015 . The Query shows me the output of my
annual holidays of November and does not display that i have already
left the company IN August 2015.

So basically when "XYZ" code on the Base. it should not show me the result.

but who use the Annual leave in November and then Leave the Company it must Show under 'XYZ'

In this query its taking All XYZ out

Sorry for bad English

select * from ( select q.*, Rank() over (partition by "Staff Number" order by FROMDATE desc) rn from (SELECT
RM_LIVE.EMPLOYEE.EMPNO AS "Staff Number",
RM_LIVE.EMPNAME.FIRSTNAME || ' ' || RM_LIVE.EMPNAME.LASTNAME AS "Employee Name",
RM_LIVE.CRWGNDACTTIME.GNDACTSTART AS "Fatigue Date",

               RM_LIVE.CRWGNDACTTIME.DEP AS "BASE",                     
               RM_LIVE.CRWCAT.crwcat AS "Rank",      
        RM_LIVE.CRWGNDACT.GNDACTCODE AS "Code",

        RM_LIVE.EMPNAME.FROMDATE
        FROM  RM_LIVE.CRWGNDACT,
              RM_LIVE.CRWGNDACTTIME,
              RM_LIVE.CRWGNDACTCAT,
              RM_LIVE.EMPASSIGN,
              RM_LIVE.EMPLOYEE,
              RM_LIVE.EMPNAME,
              RM_LIVE.CRWCAT
        WHERE RM_LIVE.CRWGNDACTTIME.IDGNDACTCODE=RM_LIVE.CRWGNDACT.IDGNDACTCODE
          and RM_LIVE.EMPASSIGN.IDGNDACTTIME=RM_LIVE.CRWGNDACTTIME.IDGNDACTTIME
          AND RM_LIVE.CRWGNDACT.IDGNDACTCAT=RM_LIVE.CRWGNDACTCAT.IDGNDACTCAT
          and RM_LIVE.EMPASSIGN.IDEMPNO=RM_LIVE.EMPLOYEE.IDEMPNO
          And RM_LIVE.EMPLOYEE.IDEMPNO = RM_LIVE.EMPNAME.IDEMPNO
          And RM_LIVE.EMPASSIGN.idcrwcat=RM_LIVE.CRWCAT.idcrwcat
          AND  RM_LIVE.CRWGNDACT.GNDACTCODE IN ('VAC','VAS')
          AND RM_LIVE.CRWCAT.crwcat IN ('CP','FO','CM','MC')
          AND RM_LIVE.CRWGNDACTTIME.GNDACTSTART BETWEEN '01-NOV-15' AND '30-NOV-15'
      AND NOT RM_LIVE.employee.IDEMPNO IN (select em2.IDEMPNO from RM_LIVE.CRWGNDACTTIME c2 INNER JOIN RM_LIVE.EMPASSIGN e2 ON 
                  c2.IDGNDACTTIME =e2.IDGNDACTTIME INNER JOIN RM_LIVE.employee em2 on e2.IDEMPNO=em2.IDEMPNO
                 WHERE  c2.DEP  = 'XYZ'

          Order by RM_LIVE.CRWGNDACTTIME.GNDACTSTART,RM_LIVE.CRWGNDACT.GNDACTCODE
          ) q) WHERE RN =1
          ;

[Updated on: Thu, 17 December 2015 03:36]

Report message to a moderator