Help With Complex query

[paulnlk4]

Hi,

This is related to Financial Industry. In actual business, a person may have opened a account in a particular branch/office, then due to some personal reason transferred the account to a new branch/office. After some months/years, the account was again transferred to the branch where the account was first opened.

The user wants me to retrieve only those accounts which were transferred the second time and check in another table whether these accounts previously existed.

Example:

Table A:

ACCOUNT_NO DECIMAL(10,0)
SNAP_DATE DATE
TRANSFER_ACCT_NO DECIMAL(10,00
TRANSFER_DATE

The table consists of records duplicated month over month based on SNAP_DATE

TRANSFER_DATE is populated only after a account is transferred and is retained until the account is closed or transferred again.

TRANSFER_ACCT_NO contains the old account number, when a transfer occurs.

Table B:

ACCOUNT_NO DECIMAL (10,0)
PRODUCT DECIMAL (10,0)
SNAP_DT DATE

Values in Table A:

ACCOUNT_NO SNAP_DATE TRANSFER_ACCT_NO TRANSFER_DATE
1234567890 2002-12-31
1234567890 2003-01-31
1111111122 2003-02-28 1234567890 2003-02-10
1234567890 2003-03-31 1111111122 2003-03-06

Values in Table B:

ACCOUNT_NO PRODUCT SNAP_DT
1234567890 400500600 2002-12-31
1234567890 400500600 2003-01-31
1111111122 400500600 2003-02-28
1234567890 400500600 2003-03-31

I created the following query;

SELECT DISTINCT(ACCOUNT_NO) FROM TABLE B
WHERE ACCOUNT_NO IN
(SELECT ACCOUNT_NO IN TABLE A
WHERE TRANSFER_DATE IS NOT NULL);

I got the following result:
1111111122 - which is the first step of what I want. I also want to be able to retrieve the record occurring in March SNAP_DT. How do I do this?

Your help will be greatly appreciated.

Thanks,
Paul

 

[paulnlk4]

Hi,

I have found the solution.

Thanks,
Paul

 

[dnoeth]

Hi Paul,
it's always nice to share your solution if you found it ;-)

Dieter