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

    Unanswered: How Can I Query Two Conditions on the Same Date?

    Hey guys,

    I am trying to write a DB2 query that will pull results based on these conditions:


    Show all 00499 members who have a service date = 06-30-1998

    Show only 00499 members whose contribution amount is great than 0.00 with a service date occuring before 06-30-1998.


    I am having trouble structuring a query that can pull these results. I've tried a derived table and both sub-query:

    Code:
    SELECT distinct a.mbr_ssn_nbr, 
          -- a.mbr_hist_svc_cr_dt, 
           a.hist_categ_cd, 
           a.mbr_cntrb_amt, 
           X.TotalAmount   
           
           FROM dsnp.pr01_t_mbr_hist AS a 
                  
           INNER JOIN ( SELECT z.mbr_ssn_nbr, sum(z.mbr_cntrb_amt)
           
            as TotalAmount 
            
            FROM dsnp.pr01_t_mbr_hist AS z  
           where mbr_hist_svc_cr_dt < '1998-06-30' 
           group by z.mbr_ssn_nbr
            having sum(z.mbr_cntrb_amt) > 0.0 ) X 
            on A.mbr_ssn_nbr = X.mbr_ssn_nbr
            WHERE a.agty_id_cd = '00499'   
            AND a.hist_categ_cd = '10'
           AND a.mbr_hist_svc_cr_dt = '1998-06-30'

    This query does work for my first criteria. However, it pulls in people who have contribution amount occuring after the 1998 date. I don't need to see these. For example:


    SSN DATE AMOUNT


    1122 06-30-1998 0.00
    1122 06-30-1998 112.00


    This SSN, while meeting the first criteria of having the 1998 date, it fails the 2nd one because there are no contribution amounts before 1998.

    Can anyone tell me why my logic is failing? I can't see it.

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Do you maybe want a union or a full outer join? Seems to me you may have some that fall into one category and not the other. Also, shouldn't this be within the summation where clause as well?
    a.agty_id_cd = '00499'
    AND a.hist_categ_cd = '10'

Posting Permissions

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