Staff Lft Company and display his pervisos recoreds

Other Tech Topics
1

I have a table (Employees) 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.

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'


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

AND NOT EXISTS (SELECT 1 FROM RM_LIVE.EMPLOYEE e2 WHERE RM_LIVE.EMPLOYEE.EMPNO = e2.EMPNO AND e2.code = 'XYZ')

3

do i use this after

) q) WHERE RN =1

5

AND NOT EXISTS (SELECT 1 FROM RM_LIVE.EMPLOYEE e2,RM_LIVE.CRWGNDACTTIME e2 WHERE RM_LIVE.EMPLOYEE.EMPNO = e2.EMPNO
AND RM_LIVE.CRWGNDACTTIME.DEP = e2.DEP = 'XYZ')

giving error

ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"
*Cause:
*Action:
Error at Line: 32 Column: 31

6

its worked but same answer im getting which giving me before

7

You are using Oracle, so I'm unable to help. SQLTeam.com is for Microsoft SQL Server. I would suggest posting your question on a site that specializes in Oracle. Though the SQL languages are similar, there are differences, often significant differences.

To answer your question for TSQL (SQL Server flavor of SQL), I would put it after:
AND RM_LIVE.CRWGNDACTTIME.GNDACTSTART BETWEEN '01-NOV-15' AND '30-NOV-15'