Problems Obtaining Count

[lrdave36]

Hey guys,


I have the following query:

SELECT A.DISABILITIES
      
FROM (
SELECT count(A.recip_ssn_nbr) as "DISABILITIES"  FROM 
(SELECT A.recip_ssn_nbr FROM 
 dsnp.pr01_t_anty_pymt A,
     dsnp.pr01_t_recip_sys B
where A.recip_ssn_nbr = B.recip_ssn_nbr
and right(B.voucher_id_cd,1) = 'D'
group by A.recip_ssn_nbr
having min(A.anty_pymt_dt) = '2010-05-01') as A ) as A

This runs just fine, but I need to add more to it.
I want to break down my total count by agency category which is stored in the field AGTY_SYS_CD located in my dsnp.pr01_t_recip_sys.

I just can't see to get it to work especially since I'm dealing with a 3 layered query here. I've tried adding the field to my group by clause, but it returns zero records.


Can anyone help? I want the end result to look like this:


AGENCY DISABILITIES


BBF 13
CAM 223

 

[bborissov]

Why you need so many derived tables in simple count query?

SELECT B.AGTY_SYS_CD AS Agency,
       COUNT(A.recip_ssn_nbr) AS Desabilities
FROM dsnp.pr01_t_anty_pymt A
INNER JOIN dsnp.pr01_t_recip_sys B
      ON A.recip_ssn_nbr = B.recip_ssn_nbr AND 
         RIGHT(B.voucher_id_cd,1) = 'D'
----WHERE A.anty_pymt_dt = '20100501'
group by B.AGTY_SYS_CD

The HAVING min(A.anty_pymt_dt) = '2010-05-01' is not very clear for me. WHat you want from that date?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[lrdave36]

Hey Boris,

The min part is used to return only people whose first payment date in the table is 05-10-2010. If I just said anty_pymt_dt = '20100501', it would pull everyone since all retirees are paid on the same date.

 

[bborissov]

SELECT B.AGTY_SYS_CD AS Agency,
       COUNT(A.recip_ssn_nbr) AS Disabilities
FROM dsnp.pr01_t_anty_pymt A
INNER JOIN dsnp.pr01_t_recip_sys B
      ON A.recip_ssn_nbr = B.recip_ssn_nbr AND
         RIGHT(B.voucher_id_cd,1) = 'D'
INNER JOIN (SELECT recip_ssn_nbr,
                   MIN(anty_pymt_dt)
            FROM dsnp.pr01_t_anty_pymt
            GROUP BY recip_ssn_nbr) A1
      ON A.recip_ssn_nbr = A1.recip_ssn_nbr AND
         A1.anty_pymt_dt = '20100501'
group by B.AGTY_SYS_CD

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.