ansaurus

Question

Answer 1

+1 A:

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

THEn 2009-06-18 19:27:02

Will this work if I have instances like the last two in my example above where they have the same CACLAIM (aka I4YFTX) but different DOIs (I4YWTX)? And what about when they have the same CACLAIM and DOI and the only thing differentiating them is the AGCLAIM which is not on every record in WEI4CPP?

2009-06-18 19:59:42

I think you cloud add condition. I will check and update it.

THEn 2009-06-19 15:31:39

Question is when I4X6TX is empty how do you know which one it belongs to?

THEn 2009-06-19 16:39:56

I4YFTX is supposed to be a unique number for each "group" of records, which is how you'd know that those records go together. I'm trying to get around some system errors that allowed the same number in that field mulitple times for what should be different "groups" by using other variables such as dates.

2009-06-19 18:33:03

I updated the query. just add I4YWTX in group by. That should work.

THEn 2009-06-23 17:05:57

Answer 2

A:

I haven't gone through your logic in detail but make sure that both dates are being stored as numeric values. I've seen more than once a date stored as a char so GT and LT then don't work the way you think.

CTKeane 2009-06-19 13:01:00

Answer 3

A:

It looks like the WEI4CPP.I4YFTX is the FROISEDI.CACLAIM linking value, and WEI4CPP.I4YWTX is FROISEDI.DOI. Is it as simple as this?

SELECT fr.AGCLAIM, fr.CACLAIM, fr.DOI, COUNT(we.I4C7DZ) as SUBMITS
FROM    FROISEDI fr
     INNER JOIN WEI4CPP we
      ON we.I4YFTX = fr.CACLAIM
      AND  we.I4YWTX = fr.DOI
      AND  ISNULL(we.I4X6TX, fr.AGCLAIM) = fr.AGCLAIM
WHERE   we.I4C7DZ <= fr.RECDATE
GROUP BY fr.AGCLAIM, fr.CACLAIM, fr.DOI

Note there will be an issue with this if rows have the same DOI and CACLAIM, but the WEI4CPP.I4X6TX column is empty. In this case, I do not know your business rules about resolving which AGCLAIM they belong to. I can reject them if they are filled in and don't match, but I would need some kind of date-related or other information to match them otherwise.

breitak67 2009-06-23 19:28:05

ansaurus

tags:

views:

answers:

Help with Correlated Queries in SQL

related questions