DB2 Query Assistance



IBM's flagship relational database management system

DB2 Query Assistance

Postby wood1985 » Tue Nov 22, 2011 3:42 pm

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.
wood1985
 
Posts: 3
Joined: Tue Nov 22, 2011 3:26 pm
Has thanked: 0 time
Been thanked: 0 time

Re: DB2 Query Assistance

 

Re: DB2 Query Assistance

Postby GuyC » Tue Nov 22, 2011 4:10 pm

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
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: DB2 Query Assistance

Postby wood1985 » Wed Nov 23, 2011 4:07 pm

Thanks.

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

Much appreciated.
wood1985
 
Posts: 3
Joined: Tue Nov 22, 2011 3:26 pm
Has thanked: 0 time
Been thanked: 0 time

Re: DB2 Query Assistance

Postby wood1985 » Thu Nov 24, 2011 4:31 pm

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.
wood1985
 
Posts: 3
Joined: Tue Nov 22, 2011 3:26 pm
Has thanked: 0 time
Been thanked: 0 time

Re: DB2 Query Assistance

Postby GuyC » Thu Nov 24, 2011 6:23 pm

thanks for the feedback
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post