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.
dBforums
>
How Can I Query Two Conditions on the Same Date?
01-07-10, 15:40
Linear Mode
