Get rows without a specific date

[Ams16]

Hi,

I have a DB2 table with below structure.

Table has a column for accountno and another column for storing payment date.For the same account,every month multiple/single payments can happeni.e.,there will be multiple entries for the same accountno.

I need to filter out the rows for which payment for a particular month is not present(payment date column).

how can I frame the query for this?

Thank you

 

[mikrom]

Hi Ams16,

For example if you want to get all accounts which doesn't have payment on April 2018,
you can try something like this:

select t1.ACCOUNT_NO from   
(select distinct ACCOUNT_NO 
 from PAYMENT_TABLE                  
 where year(PAYMENT_DATE) != 2018 or month(PAYMENT_DATE) != 4
) t1  
exception join                       
(select distinct ACCOUNT_NO          
 from PAYMENT_TABLE                  
 where year(PAYMENT_DATE) = 2018 and month(PAYMENT_DATE) = 4
) t2   
on t1.ACCOUNT_NO = t2.ACCOUNT_NO  
order by t1.ACCOUNT_NO

 

[johnherman]

Here's another take:

select distinct (t1.ACCOUNT_NO) from PAYMENT_TABLE t1 where
t1.ACCOUNT_NOT IN (
(select distinct (t2.ACCOUNT_NO)
from PAYMENT_TABLE t2
where year(t2.PAYMENT_DATE) = 2018 and month(t2.PAYMENT_DATE) = 4
)

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity