Sql – Help with Correlated Queries in SQL

proc-sqlsassqlsubquery

Please help!! I am brand new to SQL programming and am teaching myself everything as I go along. I’ve finally run into a problem I can’t seem to tackle. Hopefully someone can help. I have the following tables. What I need to do is use the RECDATE in FROISEDI and count all the distinct dates (I4C7DZ) prior to and including that date in WEI4CPP. I’ve used the code below and it seems to work until I have a record that has the same CACLAIM value. I also have instances where two records have the same CACLAIM and DOI so my code doesn’t work then either. Somehow, I need to use AGCLAIM from FROISEDI but I don't know how to pull it into my Counts table. Oh, and I’m using SAS so it’s not exactly SQL but really close. Thanks for any help!


TABLE: FROISEDI
AGCLAIM    RECDATE   CACLAIM     DOI
09073589   1/29/09   09 41615    1/28/09  
09115390   3/01/09   00012HR09   2/23/09  
09234567   4/20/09   003140010   1/2/09  
09154826   5/01/09   003140010   4/28/09  

TABLE: WEI4CPP  
 I4C7DZ     I4X6TX      I4YWTX    I4YFTX  
1/28/09               1/28/09    09 41615  
1/29/09   09073589    1/28/09    09 41615  
1/30/09   09073589    1/28/09    09 41615  
2/24/09               2/23/09    00012HR09  
2/28/09               2/23/09    00012HR09    
3/01/09   09115390    2/23/09    00012HR09  
3/15/09   09115390    2/23/09    00012HR09  
1/15/09               1/02/09    003140010  
1/20/09               1/02/09    003140010  
2/08/09               1/02/09    003140010  
3/19/09               1/02/09    003140010  
4/20/09   09234567    1/02/09    003140010  
5/01/09   09154826    4/28/09    003140010  

TABLE I NEED TO PRODUCE: COUNTS  
AGCLAIM     CACLAIM   DOI      SUBMITS  
09073589    09 41615     1/28/09    2  
09115390    00012HR09    2/23/09    3  
09234567    003140010    1/02/09    5  
09154826    003140010    4/28/09    1  

CODE I'VE USED:

PROC SQL;
CREATE TABLE COUNTS AS
SELECT I4YWTX AS DOI3,
I4YFTX AS CLMNUM2,
COUNT(DISTINCT I4C7DZ) AS SUBMITS
FROM WAREHOUS.WEI4CPP A
WHERE I4C7DZ<=(SELECT RECDATE
         FROM FROISEDI 
         WHERE FROISEDI.CACLAIM=A.I4YFTX
         AND FROISEDI.DOI=A.I4YWTX) 
GROUP BY WEI4CPP.I4YFTX, WEI4CPP.I4YWTX;
QUIT;

Best Answer

Didn't test it. try this

SELECT 
    AGCLAIM, 
    CACLAIM,     
    DOI, T.cnt + 1 AS SUBMITS
FROM 
    FROISEDI 
    INNER JOIN 
    (
    SELECT 
        COUNT(*) cnt,
        I4YFTX ,
            I4YWTX     
    FROM 
        WEI4CPP   
    WHERE 
        ISNULL(I4X6TX,0) = 0 
    GROUP BY 
        I4YFTX, I4YWTX    
    ) T 
    ON FROISEDI.CACLAIM = T.I4YFTX