Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2009
    Posts
    46

    Unanswered: How Can I Get DB2 to Look at Multiple Dates?

    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!

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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
    ;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •