| sub query logic [message #411485] |
Sat, 04 July 2009 04:36  |
avrillavinge
Messages: 57
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: 57
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 #411515 is a reply to message #411485] |
Sat, 04 July 2009 09:37 |
Michel Cadot
Messages: 28991
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).
Post a working Test case: create table and insert statements along with the result you want with these data.
Regards
Michel
|
|
|
| Re: sub query logic [message #411665 is a reply to message #411495] |
Mon, 06 July 2009 05:08 |
ayush_anand
Messages: 229
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
|
|
|
] 
Members
Search
Help
Register
Login
Home
