View previous topic :: View next topic
|
Author |
Message |
vinu78
Active User
Joined: 02 Oct 2008 Posts: 179 Location: India
|
|
|
|
Hi All,
I have 2 tables with data spread as follows:
TABLE A
Code: |
Acct ID Item State Pol Eff Date
1111 100 FL 2012-01-01
2222 200 FL 2011-01-01
3333 300 FL 2012-04-01
4444 500 KY 2012-05-01 |
TABLE B
Code: |
Acct ID Item Code
1111 100 10
2222 200 10
3333 300 20
4444 500 10 |
Table A and Table B has Acct and Item as common field
I wanted to extract all the accounts from Table A joining with Table B but should skip the accounts whose State is FL having Code 10 with effective date greater than or equal to 2012-01-01. That means I should exclude the above mentioned accounts but include FL accounts with code 10 prior to 2012-01-01 or FLORIDA accounts with code other than 10 irrespective of eff date. This condition is only for FL state. Accounts of other state should be included without any condition.
SQL in my mind whch is not working is
Code: |
SELECT A.ACCT_ID,A.ITEM,A.STATE,A.POL_EFF_DATE,B.CODE
FROM TABLE A
INNER JOIN
TABLE B
ON A.ACCT_ID = B.ACCT_ID
AND A.ITEM = B.ITEM
AND ....... |
Result should be
Code: |
Acct ID Item State Pol Eff Date Code
2222 200 FL 2011-01-01 10
3333 300 FL 2012-04-01 20
4444 500 KY 2012-05-01 10 |
Can somebody please guide me here ?
Thanks
Vinu |
|
Back to top |
|
|
vinu78
Active User
Joined: 02 Oct 2008 Posts: 179 Location: India
|
|
|
|
I think I got the solution.
I believe the following will work
Code: |
SELECT A.ACCT_ID,A.ITEM,A.STATE,A.POL_EFF_DATE,B.CODE
FROM TABLE A
INNER JOIN
TABLE B
ON A.ACCT_ID = B.ACCT_ID
AND A.ITEM = B.ITEM
AND NOT (A.STATE = FL AND B.CODE = 10
AND A.POL_EFF >='2012-01-01') |
Sorry, I didn't think of the NOT function |
|
Back to top |
|
|
|