RE: [sqr-users] PS Query

["Alexander, Steven" <Steven.Alexander@sanjoseca.gov>]

You didn't specify D.EMPLID = C.EMPLID in the "not exists" subquery.  You
are including terminated employees if Payroll doesn't terminate their direct
deposit (our Payroll department doesn't).  Also, if PS_PERSON_NAME gives you
performance problems, you could consider using PS_PERSONAL_DATA.

-----Original Message-----
From: veena kolanu [mailto:veena_kolanu@hotmail.com] 
Sent: Friday, April 07, 2006 1:35 PM
To: sqr-users@sqrug.org
Subject: [sqr-users] PS Query

Hi All,
  I am new to this group and wondering someone can help me with the query I
am working on.
I need to develop a query that shows any people off assignment for 90 days
or more that have an direct deposit.
 This is what my sql looks like and let me  know if this is correct or not.
 
Thanks
 
SELECT C.NAME, C.EMPLID  FROM PS_PERSON_NAME C, PS_PERS_SRCH_QRY C1,
PS_DIRECT_DEPOSIT B  WHERE C.EMPLID = C1.EMPLID    AND C1.ROWSECCLASS =
'DPALL'    AND ( C.EMPLID = B.EMPLID     AND B.EFFDT =        (SELECT
MAX(B_ED.EFFDT) FROM PS_DIRECT_DEPOSIT B_ED        WHERE B.EMPLID =
B_ED.EMPLID          AND B_ED.EFFDT <= SYSDATE)     AND B.EFF_STATUS = 'A'
AND NOT EXISTS (SELECT 'X'  FROM PS_OA_PAY_CHECK D  where D.PAY_END_DT >
SYSDATE-90))  order by 1;
_________________________________________________________________
Because e-mail on your cell phone should be easy:  Try Windows Live Mail for
Mobile beta
http://www2.imagine-msn.com/minisites/mail/Default.aspx?locale=en-us

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users