Query Help Needed!

[lrdave36]

Hey guys,

I am stuck trying to work out the logic for this query request. The request has two criterias:


1. Find all members who have a credit date of '1998-06-30' or later.


2. Out of those results, only display members who have a contribution amount in their entire history that is greater than zero.


Example of table (dummy data):


SSN Credit Date Credit Amount


11122 1955-04-04 0.00
11122 1966-05-02 123.00
11122 1998-06-30 0.00

22344 1978-06-04 23.00
22344 1988-06-03 0.00


As you can see, there are multiple credit dates and amounts for each social. 22344 would not be included in my results because he does have a 1998 date.

I came up with this, but I'm not confident that it is right:

SELECT   A.MBR_SSN_NBR, A.MBR_HIST_SVC_CR_DT, A.HIST_CATEG_CD, 
         A.MBR_CNTRB_AMT 
FROM     DSNP.PR01_T_MBR_HIST A 
WHERE    A.AGTY_ID_CD = '00499' 
AND      A.HIST_CATEG_CD = '10' 
AND      A.MBR_CNTRB_AMT > 0.0
AND      EXISTS     (SELECT 1     FROM DSNP.PR01_T_MBR_HIST Z    WHERE Z.MBR_SSN_NBR   = A.MBR_SSN_NBR    AND Z.MBR_HIST_SVC_CR_DT >= '1998-06-30' )

What do you guys think? Thanks!

 

[r937]

i would do it the other way around

SELECT a.mbr_ssn_nbr
     , a.mbr_hist_svc_cr_dt
     , a.hist_categ_cd
     , a.mbr_cntrb_amt 
  FROM dsnp.pr01_t_mbr_hist AS a 
 WHERE a.agty_id_cd = '00499' 
   AND a.hist_categ_cd = '10' 
   AND a.mbr_hist_svc_cr_dt >= '1998-06-30'
   AND EXISTS
       ( SELECT 1
           FROM dsnp.pr01_t_mbr_hist AS z
          WHERE z.mbr_ssn_nbr = a.mbr_ssn_nbr
            AND z.mbr_cntrb_amt > 0.0 )

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[markros]

Alternatively you can do an inner join instead of Exists subquery - the benefit is that you can add a Total amount to the same query. It also would probably perform better.

 

[markros]

SELECT 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  
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'

 

[lrdave36]

Hey Markros,

Your query was great. I didn't think about using a sub-query as a virtual table like that.

I do need to make another change that makes this query more challenging. I need the query to only return people where the mbr_cntrb_amt amount > 0.00 before the date of 06-30-1998.

Is there any way to do this by modifying your query above?

 

[markros]

Just add this condition into derived table, e.g.

SELECT 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 z.mbr_hist_svc_cr_dt >= '1998-06-30'   <'06-30-1998'
 group by z.mbr_ssn_nbr
having sum(z.mbr_cntrb_amt) > 0.0 ) Xon 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'

 

[lrdave36]

Would it look like this?

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'

I tried this before posting today, and unfortunately, the query is still pulling in people who have contribution amounts occuring after 06-30-1998. This is fine as long as they have contribution amounts > 0.00 before 06-30-1998 as well.

 

[markros]

Strange - I don't see an error in this query - to me it looks like it has to do the job.

Try to run the derived table query alone first and verify that these are the people indeed with the correct amounts.

 

[lrdave36]

I double checked and it appears to be working now. I did notice that I referenced a field without the z alias in my derived table. Thanks for all the help!

 

[markros]

No problem, you can mark it, though