>Hi -
>
>I have a table whith monthly payment data, like this:
>
>AccountNo, MonthNo, PaymentAmount
>22,1,33000
>22,2,33000
>22,3,33000
>22,4,33000
>22,5,33000
>22,6,33000
>22,7,33000
>22,8,33000
>22,9,33000
>22,10,33000
>22,11,33000
>22,12,33000
>23,1,34000
>.....
>
>In the above example, for AccountNo 22 and for each month jan to dec, the
>payment is 33000.
>
>For each AccountNo there should be an entry for each month 1 to 12.
>
>Now, I've got a problem where in some cases a monthly entry is missing.
>
>How do I construct a SQL query, that selects the AccountNo's where one of
>the monthly entries is missing?
>
>Thanks for your help!

Hi Jacob!

The way you describe it, the solution could be very simple:

WITH TMP(ACCOUNT_NO, MONTHSPAID)
AS
(SELECT ACCOUNT_NO, COUNT(DISTINCT MONTHNO)
FROM MYTABLE
GROUP BY 1)

SELECT ACCOUNT_NO
FROM TMP
WHERE MONTHSPAID < 12

(alternatively, you do not need a CTE and just use its subselect with an additional HAVING COUNT(DISTINCT MONTHNO) < 12, the CTE just makes more complex calculations simpler)

The solution would be more tricky if not everybody should pay once every month or if you wanted the result to report if one of the months had a different payment than the other. Add to that the possibility of someone paying two months at a time, e.g. where you have 22,10,66000 and no entry for November at all, and you will need a complex query, probably involving EXECUTE BLOCK or a stored procedure.

HTH,
Set