RE: [sqr-users] Select statement picking 1 EE twice - EE has beenHired, Termed, Rehired

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

Do you have more than one value of EMPL_RCD for this employee?

-----Original Message-----
From: Bob Stone [mailto:bstone@fastenal.com] 
Sent: Thursday, May 18, 2006 3:04 PM
To: This list is for discussion about the SQR database reporting
languagefrom Hyperion Solutions.
Subject: RE: [sqr-users] Select statement picking 1 EE twice - EE has
beenHired, Termed, Rehired

I don't know your tables data, but I would think that the 2 'f.field =
(select max(field)...)' is where your problem would be.

-----Original Message-----
From: sqr-users-bounces+bstone=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org] On Behalf Of
Joe Johnson
Sent: Thursday, May 18, 2006 4:43 PM
To: 'This list is for discussion about the SQR database
reportinglanguage from Hyperion Solutions.'
Subject: [sqr-users] Select statement picking 1 EE twice - EE has been
Hired,Termed, Rehired

I have an SQR and the main select is:

 

SELECT

 F.LOCATION          

,A.EMPLID            

,A.PLAN_TYPE         

,A.COVERAGE_BEGIN_DT

,A.FLAT_AMOUNT             

,C.BENEFIT_PROGRAM

,B.BIRTHDATE

,BB.SEX

,D.RATE_TBL_ID

from PS_LIFE_ADD_BEN A,

     PS_PERSON B,

     PS_PERS_DATA_EFFDT BB,

     PS_BEN_PROG_PARTIC C,

     PS_BEN_DEFN_OPTN G,

     PS_BEN_DEFN_COST D,

     PS_JOB F,

     PS_RUN_CNTL_HR RC1

WHERE A.PLAN_TYPE = '21'

AND A.EFFDT = (

   SELECT MAX(EFFDT) FROM PS_LIFE_ADD_BEN

    WHERE EMPLID          = A.EMPLID

      AND EMPL_RCD        = A.EMPL_RCD

      AND PLAN_TYPE       = A.PLAN_TYPE

      AND BENEFIT_NBR     = A.BENEFIT_NBR

      AND EFFDT          <= $AsOfToday) 

      AND A.COVERAGE_BEGIN_DT  <= $RUNCTL_DT

AND A.COVERAGE_ELECT      = 'E'

AND B.EMPLID              = A.EMPLID

AND BB.EMPLID             = B.EMPLID

AND BB.EFFDT = (

    SELECT MAX(EFFDT) FROM PS_PERS_DATA_EFFDT

     WHERE EMPLID = BB.EMPLID

      AND EFFDT  <= $AsOfToday)                      

AND C.EMPLID              = B.EMPLID

AND C.EMPL_RCD            = A.EMPL_RCD

AND C.EFFDT = (

    SELECT MAX(EFFDT) FROM PS_BEN_PROG_PARTIC

     WHERE EMPLID         = C.EMPLID

       AND EMPL_RCD       = C.EMPL_RCD

       AND COBRA_EVENT_ID = C.COBRA_EVENT_ID

       AND EFFDT         <= $AsOfToday)                

AND G.BENEFIT_PROGRAM     = C.BENEFIT_PROGRAM

AND G.EFFDT = (

    SELECT MAX(EFFDT) FROM PS_BEN_DEFN_OPTN

     WHERE BENEFIT_PROGRAM   = G.BENEFIT_PROGRAM

       AND EFFDT            <= $AsOfToday)            

AND G.PLAN_TYPE           = A.PLAN_TYPE

AND G.BENEFIT_PLAN        = A.BENEFIT_PLAN

AND D.BENEFIT_PROGRAM     = G.BENEFIT_PROGRAM

AND D.EFFDT               = G.EFFDT

AND D.PLAN_TYPE           = A.PLAN_TYPE

AND D.OPTION_ID           = G.OPTION_ID

AND F.EMPLID              = A.EMPLID

AND F.EMPL_RCD            = A.EMPL_RCD

AND F.EFFDT = (

    SELECT MAX(EFFDT) FROM PS_JOB

     WHERE EMPLID   = F.EMPLID

       AND EMPL_RCD = F.EMPL_RCD

       AND EFFDT   <= $AsOfToday)                     

AND F.EFFSEQ = (

    SELECT MAX(EFFSEQ) FROM PS_JOB

     WHERE EMPLID   = F.EMPLID

       AND EMPL_RCD = F.EMPL_RCD

       AND EFFDT    = F.EFFDT)

AND F.EMPL_STATUS IN ('A','L','P','S')                      

AND RC1.OPRID             = 'joeblow'

AND RC1.RUN_CNTL_ID       = 'jz'

ORDER BY B.BIRTHDATE DESC

 

But it returns both the 'HIR' row and the 'REH' row from JOB for this
EE:

 

 

EMPLID      EMPL_RCD EFFDT         EFFSEQ DEPTID     JOBCODE EMPL_STATUS
ACTION ACTION_DT    ACTION_REASON 

----------- -------- ------------- ------ ---------- ------- -----------
------ ------------ ------------- 

999999      0        2005-07-11    0      XYZ310     Q31001  A
HIR
2005-07-14   HIR           

999999      0        2005-12-31    0      XYZ310     Q31001  T
TER
2005-12-30   QWN           

999999      0        2006-03-17    0      XYZ100     U10010  A
REH
2006-03-20   REH   

 

And it grabs both of the COVG_ELECT = 'E' rows from LIFE_ADD_BEN:

 

EMPLID   EMPL_RCD PLAN_TYPE BEN_NBR EFFDT       DEDUCT_END_DT
COVG_BEG_DT
COVG_END_DT  COVG_ELECT COVG_ELECT_DT  BEN_PLAN FLAT_AMOUNT 

-------- -------- --------- ------- ----------- --------------
------------
------------ ---------- -------------- -------- ----------- 

999999   0        21        0       2005-07-30  NULL
2005-08-01
NULL         E          2005-07-28     SUPLIF   50000       

999999   0        21        0       2005-12-31  NULL
2005-12-31
NULL         T          2006-01-04              0           

999999   0        21        0       2006-04-01  NULL
2006-04-01
NULL         E          2006-04-07     SUPLIF   50000       

 

I know it is a max(effdt) problem with my select (or at least I think it
is).

 

I ran this on 5/18/06 and am using a $RUNCTL_DT = '2006-04-02'

 

TIA,

 

Joe Johnson         

_______________________________________________
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

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