Duplicate account records with different status

[nickdaniels]

I am having a problem generating accurate sql in the following situation.&nbsp;&nbsp;We have accounts which may be have one of a number of statii.&nbsp;&nbsp;An account may be rejected, in which case a single record with a statuscode of 'R' will be held in the table.<br><br>An account may be accepted stage 1, in which case the 'applicationstage' field in the table will be 1.&nbsp;&nbsp;Alternatively, the account may be accepted stage 2, in which case the 'applicationstage' field will be 2.<br><br>So, I can indentify rejections and stage 2s no problem.<br><br>However, due to the fact that the 'history' of the account is held in the table, if I do a straight decode application stage to get the stage1 total, I include within this total any stage 1s that have now moved to stage 2.&nbsp;&nbsp;These should only be counted as stage 2, but my sql is counting them twice.<br><br>Below is the SQL I am using - first checking that it isn't a rejection<br><br>COUNT(DISTINCT(decode(LOANAPPLICATION.STATUSCODE,'R',0,decode(LOANAPPLICATION.APPLICATIONSTAGE,1,LOANAPPLICATION.ACCOUNTID))))<br><br>I don't know how to also get this statement to check for records with the same account number with a status of 2.<br><br>All help appreciated!&nbsp;&nbsp;cheers, Nick

 

[nickdaniels]

Well, I have come up with my own solution to this.&nbsp;&nbsp;however it seems gruesomely complicated - can anyone come up with a neat and tidy way of doing this?<br><br>select distinct t2.accountid<br>from loanapplication t1, loanapplication t2<br>where t1.accountid = t2.accountid<br>and t1.APPLICATIONSTAGE = t2.applicationstage<br>and t2.statuscode &lt;&gt; 'R'<br>and t2.APPLICATIONSTAGE = 1<br>and t1.applicationstage=1<br>MINUS<br>select distinct t2.accountid<br>from loanapplication t1, loanapplication t2<br>where t1.accountid = t2.accountid<br>and t1.APPLICATIONSTAGE &lt;&gt; t2.applicationstage<br>and t2.statuscode &lt;&gt; 'R'<br>

 

[karluk]

I'm glad you found a way that works.&nbsp;&nbsp;If you want to try to simplify your query I would suggest that you keep the &quot;(non rejected stage 1) minus (non rejected stage 2)&quot; strategy while eliminating the joins.&nbsp;&nbsp;As far as I can see the following query should do what you need.<br><br>select distinct accountid<br>from loanapplication<br>where statuscode &lt;&gt; 'R'<br>and applicationstage=1<br>MINUS<br>select distinct accountid<br>from loanapplication<br>and statuscode &lt;&gt; 'R'<br>and applicationstage &lt;&gt; 1<br><br>

 

[nickdaniels]

karluk, you're absolutely right.&nbsp;&nbsp;Incidentally I have now thought of a second way of doing this.&nbsp;&nbsp;I won't bother sticking in the SQL, but basically because I can accurately identify the total number of accounts, total number of stage 2 and total number of rejects just do<br><br>stage 1 = total - stage2 - reject<br><br>cheers, Nick

 

[bigscouse]

SELECT COUNT(a.accountid )<br>&nbsp;&nbsp;FROM loanapplication a<br>&nbsp;WHERE a.statuscode != ‘R’<br>&nbsp;&nbsp;&nbsp;AND a.applicationstage = 1<br>&nbsp;&nbsp;&nbsp;AND NOT EXISTS (<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT *<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FROM loanappliation b<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHERE a.accountid = b.accountid<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AND b.applicationstage = 2 ); <br> <p>Ged Jones<br><a href=mailto:gedejones@hotmail.com>gedejones@hotmail.com</a><br><a href= > </a><br>