| sub query logic [message #411485] |
Sat, 04 July 2009 04:36  |
avrillavinge
Messages: 98
Registered: July 2007
|
Member |
|
|
i need to get a unique count of applicaation who do not have the same fupcode .
i have applications from start of this bank till date and every application is assigned a FUpcode
i have to get the count of applications in previous month who have a new fupcode.
i mean these applications should not have been assigned this fup code before in their lifetime.
as one application can have many fup codes assigned depnding on the business logic.
so i need to track the applications with new fup codes only in the last month.
here is the query i tried but the result doesnt match and i fail to understand how to get this logic done.
My Query :
select count(*) from (
select count(fup_code) from fup_detl where trim(fup_date8) < '20080630' and fup_code in
(select distinct fupcode from fup_detl where trim(fup_date8) between '20080601' and '20080630' ) group by substr(appl_id,1,12) having count(fup_code)=1)
|
|
|
|
|
|
| Re: sub query logic [message #411489 is a reply to message #411485] |
Sat, 04 July 2009 05:06  |
avrillavinge
Messages: 98
Registered: July 2007
|
Member |
|
|
i hope you have understood my problem and the logic am trying to implement.
please let me know il expalin again if you require.
i need to remove the application from previous months count if the applications had been the same fupcode in their lifetime before previous month.
i get more than 1 lakhs records whic isnt correct.
can you let me know if you need anything else.
i cannot disclose the data here.apologies for the same.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: sub query logic [message #411665 is a reply to message #411495] |
Mon, 06 July 2009 05:08 |
ayush_anand
Messages: 417
Registered: November 2008
|
Senior Member |
|
|
if all are in Varchar2 how come you apply the code
WHERE TRIM (fup_date8) BETWEEN '20080601' AND '20080630')
convert all the varchars to date with TO_DATE function then try to filter
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
