Need Help with Filtering Records in this Query

[lrdave36]

Hey guys,

I am trying to write a query that will show me the following:

1. People who went to DD (deceased) status in the date range 07-01-2008 and 06-30-2009. The first problem that I encounter is our data has nothing to tell me exactly when they went DD. I can query for DD people, but it will only show people who are currently DD.

So my next option is to base it on their payment history. If the person didn't receive a full 12 monthly payments for this period range and current status is DD, they probably died in this range.

I don't know how to tell my query to pull records for people with less than 12 monthly payments. I tried this:

SELECT   DISTINCT A.RECIP_SSN_NBR AS SSN, B.MBR_L_NM AS LAST_NAME, 
         B.MBR_F_NM AS FIRST_NAME, C.STATE_ID_CD AS STATE, 
         CASE WHEN A.RECIP_TYPE_CD = '10' THEN 'MEMBER'           END                          AS RECIPIENT_TYPE, 
         CASE WHEN A.BENEF_STAT_CD = 'DD' THEN 'DECEASED'           END                          AS STATUS_CODE, 
         CHAR(A.RECIP_RETIR_DT,USA)   AS RETIRE_DATE 
FROM     DSNP.PR01_T_RECIP_SYS A, 
         DSNP.PR01_T_MBR B, 
         DSNP.PR01_T_MBR_CITY C, 
         DSNP.PR01_T_ANTY_PYMT D 
WHERE    A.RECIP_SSN_NBR=B.MBR_SSN_NBR 
AND      A.RECIP_SSN_NBR=C.MBR_SSN_NBR 
AND      A.RECIP_SSN_NBR=D.RECIP_SSN_NBR 
AND      A.BENEF_STAT_CD = 'DD' 
AND      A.RECIP_TYPE_CD = '10' 
AND      A.RECIP_RETIR_DT < '2009-07-01' 
AND      C.MBR_ADDR_SEQ_NBR = 1 
AND      D.ANTY_PYMT_DT  BETWEEN '2008-07-01' AND '2009-06-30'
AND      count (D.ANTY_PYMT_DT) < 12

But I get an error complaining about illegal use of aggregate function. Can anyone help?

 

[gmmastros]

When you want to filter on an aggregate, you need to use the having clause (not the where clause). You will also need to use a group by clause. This group by will need to include all returned columns that are not in a aggregate function. In this case, it would be all the columns in your select list.

so...

SELECT   DISTINCT A.RECIP_SSN_NBR AS SSN, B.MBR_L_NM AS LAST_NAME,
         B.MBR_F_NM AS FIRST_NAME, C.STATE_ID_CD AS STATE,
         CASE WHEN A.RECIP_TYPE_CD = '10' THEN 'MEMBER'           END                          AS RECIPIENT_TYPE,
         CASE WHEN A.BENEF_STAT_CD = 'DD' THEN 'DECEASED'           END                          AS STATUS_CODE,
         CHAR(A.RECIP_RETIR_DT,USA)   AS RETIRE_DATE
FROM     DSNP.PR01_T_RECIP_SYS A,
         DSNP.PR01_T_MBR B,
         DSNP.PR01_T_MBR_CITY C,
         DSNP.PR01_T_ANTY_PYMT D
WHERE    A.RECIP_SSN_NBR=B.MBR_SSN_NBR
AND      A.RECIP_SSN_NBR=C.MBR_SSN_NBR
AND      A.RECIP_SSN_NBR=D.RECIP_SSN_NBR
AND      A.BENEF_STAT_CD = 'DD'
AND      A.RECIP_TYPE_CD = '10'
AND      A.RECIP_RETIR_DT < '2009-07-01'
AND      C.MBR_ADDR_SEQ_NBR = 1
AND      D.ANTY_PYMT_DT  BETWEEN '2008-07-01' AND '2009-06-30'
[!]Group By A.RECIP_SSN_NBR, 
         B.MBR_L_NM,
         B.MBR_F_NM, 
         C.STATE_ID_CD,
         CASE WHEN A.RECIP_TYPE_CD = '10' THEN 'MEMBER'           END,
         CASE WHEN A.BENEF_STAT_CD = 'DD' THEN 'DECEASED'           END  ,
         CHAR(A.RECIP_RETIR_DT,USA)
Having   count (D.ANTY_PYMT_DT) < 12 [/!]

Unfortunately, I suspect this will not return the correct data, even if it does run (no syntax errors). For example, if someone changes their state, you would get 2 rows for that person. One row might have 10 payments and another might have 2. Total.... 12 rows. Should be filtered out, but may not be.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[lrdave36]

Hi George,

I appreciate the help. I had totally forgot about the Having clause. Looks like you were right about the data not being accurate. I had to completely rewrite the query and go a different direction.