Page 1 of 1

DB2 Query Assistance

PostPosted: Tue Nov 22, 2011 3:42 pm
by wood1985
Hello,

I am looking for some help and I'm new to the forum so please be gentle!

I am trying to compare two tables and need the following.

The Key value is a policy that will be used to tie the tables together.
An account number must be tied together on each table.
The most recent date value in a particular field from each table.
The most recent dates must not match (there are no issues with comparing these two dates)
One of the tables has a financial field value which must equal 0 on the most recent date row.


Table A Requirements.

Policy number
Account number
Most recent date (within the last 30 days)


Table B requirements

Policy number
Account number
Most recent date (in field)
Financial value on the row must be 0.


I have tried using MAX statements, exists/not exists and I'm having real trouble comparing the two. Please help!

This seems like it should be easy but I've tried all sorts of combinations and can't quite get it nailed down. I'm still returning thousands of rows and it's too much to go through manually.

Thanks in advance for any help.

Re: DB2 Query Assistance

PostPosted: Tue Nov 22, 2011 4:10 pm
by GuyC
select *
from Table_A A
  , Table_B B
where A.date = (select max(a1.date) from table_A A1 where a1.policy_number = A.policy_number
                                                      and a1.account_number = A.account_number)
   and a.date > current date - 30 days
   and B.date = (select max(B1.date) from table_B B1 where b1.policy_number = b.policy_number
                                                       and b1.account_number = b.account_number)
   and b.financial_field = 0
   and a.account_number = b.account_number
   and a.policy_number = b.policy_number

Re: DB2 Query Assistance

PostPosted: Wed Nov 23, 2011 4:07 pm
by wood1985
Thanks.

I'll give that a try and let you know how I get on.

Much appreciated.

Re: DB2 Query Assistance

PostPosted: Thu Nov 24, 2011 4:31 pm
by wood1985
Hello again.

The SQL worked brilliantly, I refined it a little to get the exact results I needed without issues.

I've always had trouble with the max entries and this will really help me in future.

Thanks again.

Re: DB2 Query Assistance

PostPosted: Thu Nov 24, 2011 6:23 pm
by GuyC
thanks for the feedback