How Can I Get DB2 to Look at Multiple Dates?

rockdave35 · #1 (**permalink**) 06-05-09, 16:00

Hey guys,

I wrote the following query which runs just fine:

Code:

SELECT   PR0.MBR_SSN_NBR, YEAR(PR0.MBR_BIRTH_DT) as "BIRTH YEAR", 
         PR02.MBR_TOT_SVC_YY_CT as "YEARS of SERVICE"
FROM     DSNP.PR01_T_MBR PR0, 
         DSNP.PR01_T_MBR_HIST PR01, 
         DSNP.PR01_T_MBR_SYS PR02, 
         DSNP.PR01_T_MBR_ADDR PR03, 
         DSNP.PR01_T_MBR_CITY PR04 
WHERE    PR0.MBR_SSN_NBR=PR01.MBR_SSN_NBR 
AND      PR0.MBR_SSN_NBR=PR02.MBR_SSN_NBR 
AND      PR0.MBR_SSN_NBR=PR03.MBR_SSN_NBR 
AND      PR0.MBR_SSN_NBR=PR04.MBR_SSN_NBR 
AND      PR01.EMPR_PLAN_CD = 'N' 
AND      PR02.MBR_STAT_CD = '1' 
AND      PR02.MBR_SYS_CD = 'TAPERS' 
AND      PR01.MBR_HIST_SVC_CR_DT = '2009-04-30'

Now, they want to run the same query but add in the employee's total salary for 2008.

I wrote this query:

Code:

PR0.MBR_SSN_NBR, YEAR(PR0.MBR_BIRTH_DT) as "BIRTH YEAR", 
         PR02.MBR_TOT_SVC_YY_CT as "YEARS of SERVICE",
         SUM(PR01.MBR_SAL_AMT) AS "YEARLY SALARY"
         
FROM     DSNP.PR01_T_MBR PR0, 
         DSNP.PR01_T_MBR_HIST PR01, 
         DSNP.PR01_T_MBR_SYS PR02, 
         DSNP.PR01_T_MBR_ADDR PR03, 
         DSNP.PR01_T_MBR_CITY PR04 
WHERE    PR0.MBR_SSN_NBR=PR01.MBR_SSN_NBR 
AND      PR0.MBR_SSN_NBR=PR02.MBR_SSN_NBR 
AND      PR0.MBR_SSN_NBR=PR03.MBR_SSN_NBR 
AND      PR0.MBR_SSN_NBR=PR04.MBR_SSN_NBR 
AND      PR01.EMPR_PLAN_CD = 'N' 
AND      PR02.MBR_STAT_CD = '1' 
AND      PR02.MBR_SYS_CD = 'APERS' 
AND      YEAR(PR01.MBR_HIST_SVC_CR_DT) = 2008 
GROUP BY PR0.MBR_SSN_NBR, 
         YEAR(PR0.MBR_BIRTH_DT),
         PR02.MBR_TOT_SVC_YY_CT

Course the problem now is that I no longer am looking at my 04-30-2009 data and it is pulling members who were N status in 2008.

I can't seem to figure out how to get the query to examine two different dates. I've tried sub-queries, but the logic isn't working. Would Union work??Can anyone help? Thanks!

tonkuma · #2 (**permalink**) 06-06-09, 20:59

Quote:

Now, they want to run the same query but add in the employee's total salary for 2008.

Code:

SELECT PR0.MBR_SSN_NBR
     , YEAR(PR0.MBR_BIRTH_DT) as "BIRTH YEAR", 
       PR02.MBR_TOT_SVC_YY_CT as "YEARS of SERVICE"
     , (SELECT SUM(PR01_Y.MBR_SAL_AMT)
          FROM DSNP.PR01_T_MBR_HIST PR01_Y
         WHERE PR0.MBR_SSN_NBR = PR01_Y.MBR_SSN_NBR
           AND YEAR(PR01_Y.MBR_HIST_SVC_CR_DT) = 2008
       ) AS "YEARLY SALARY"
FROM   DSNP.PR01_T_MBR PR0, 
       DSNP.PR01_T_MBR_HIST PR01, 
       DSNP.PR01_T_MBR_SYS  PR02, 
       DSNP.PR01_T_MBR_ADDR PR03, 
       DSNP.PR01_T_MBR_CITY PR04 
WHERE  PR0.MBR_SSN_NBR = PR01.MBR_SSN_NBR 
  AND  PR0.MBR_SSN_NBR = PR02.MBR_SSN_NBR 
  AND  PR0.MBR_SSN_NBR = PR03.MBR_SSN_NBR 
  AND  PR0.MBR_SSN_NBR = PR04.MBR_SSN_NBR 
  AND  PR01.EMPR_PLAN_CD = 'N' 
  AND  PR02.MBR_STAT_CD  = '1' 
  AND  PR02.MBR_SYS_CD   = 'TAPERS' 
  AND  PR01.MBR_HIST_SVC_CR_DT = '2009-04-30'
;

or

Code:

SELECT PR0.MBR_SSN_NBR
     , MAX(YEAR(PR0.MBR_BIRTH_DT)) as "BIRTH YEAR"
     , MAX(PR02.MBR_TOT_SVC_YY_CT) as "YEARS of SERVICE"
     , SUM(PR01_Y.MBR_SAL_AMT)     as "YEARLY SALARY"
FROM   DSNP.PR01_T_MBR PR0, 
       DSNP.PR01_T_MBR_HIST PR01, 
       DSNP.PR01_T_MBR_SYS  PR02, 
       DSNP.PR01_T_MBR_ADDR PR03, 
       DSNP.PR01_T_MBR_CITY PR04,
       DSNP.PR01_T_MBR_HIST PR01_Y
WHERE  PR0.MBR_SSN_NBR = PR01.MBR_SSN_NBR 
  AND  PR0.MBR_SSN_NBR = PR02.MBR_SSN_NBR 
  AND  PR0.MBR_SSN_NBR = PR03.MBR_SSN_NBR 
  AND  PR0.MBR_SSN_NBR = PR04.MBR_SSN_NBR 
  AND  PR01.EMPR_PLAN_CD = 'N' 
  AND  PR02.MBR_STAT_CD  = '1' 
  AND  PR02.MBR_SYS_CD   = 'TAPERS' 
  AND  PR01.MBR_HIST_SVC_CR_DT = '2009-04-30'
  AND  PR01_Y.MBR_SSN_NBR = PR0.MBR_SSN_NBR
  AND  YEAR(PR01_Y.MBR_HIST_SVC_CR_DT) = 2008
GROUP BY
       PR0.MBR_SSN_NBR
;

Internet Services

Web Development

Digital Marketing

Consumer Tech