Home » SQL & PL/SQL » SQL & PL/SQL » records within a quarter (oracle 10.1.2)
records within a quarter [message #345259] Tue, 02 September 2008 14:25 Go to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Hi all
I have the following query which gives me null and non-null records, but is there anway where i can get the null and non-null records for each quarter?

SELECT  calc_type,
        CASE WHEN DAYS IS NULL AND NON_NULL_RECORDS > (TOTAL_RECORDS)/2 THEN MAX(DAYS) OVER (PARTITION BY TO_CHAR(SELECTION_DATE,'yy"Q"q')ORDER BY NULL) ELSE DAYS END DAYS,
        contract_number,
        NTPACT,
        SELECTION_DATE,
        AMENDREQ,
        RECPROP,
        AMEND_STATUS_CODE,
        AMENDMENT_NUMBER,
        SELECTION_DATE AS PARAM_DATE,
        TO_CHAR(SELECTION_DATE,'yy"Q"q') quarter,
        T2.NULL_RECORDS,
        T2.NON_NULL_RECORDS,
        T2.TOTAL_RECORDS
FROM (SELECT DISTINCT ' Project Specific/Other' calc_Type
     ,TRUNC (NTPACT - Selection_Date) Days
     ,Contract_Number
     ,NTPACT
     ,SELECTION_DATE
     ,AMENDREQ
     ,RECPROP
     ,AMEND_STATUS_CODE
     ,AMENDMENT_NUMBER
     ,SELECTION_DATE AS PARAM_DATE
FROM contract_vw2

     WHERE ( Contract_Type = 'Project Specific' OR contract_type = 'Other')
     AND AMENDMENT_NUMBER=0
     AND AMEND_STATUS_CODE NOT IN (4,33,34,35,36,37))T1,
     (SELECT
      COUNT(*) AS TOTAL_RECORDS,
      SUM(CASE WHEN NTPACT IS NULL THEN 1 END) AS NULL_RECORDS,
      SUM(CASE WHEN NTPACT IS NOT NULL THEN 1 END) AS NON_NULL_RECORDS
      FROM contract_vw2
      WHERE ( Contract_Type = 'Project Specific' OR contract_type = 'Other')
      AND AMENDMENT_NUMBER=0
      AND AMEND_STATUS_CODE NOT IN (4,33,34,35,36,37)
      ) T2
	  
	 

Thank You
Re: records within a quarter [message #345263 is a reply to message #345259] Tue, 02 September 2008 14:31 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Can you describe the tables used and along with it post some test data and the output of your query which you have tried?

Regards,
Jo

icon1.gif  Re: records within a quarter [message #345267 is a reply to message #345263] Tue, 02 September 2008 14:37 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
I'm using a view which contains all these fields and i'm getting the data as in the attachement.

Thanks
joicejohn wrote on Tue, 02 September 2008 14:31
Can you describe the tables used and along with it post some test data and the output of your query which you have tried?

Regards,
Jo



Re: records within a quarter [message #345271 is a reply to message #345259] Tue, 02 September 2008 15:05 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
> i'm getting the data as in the attachement.
Attachment? what attachment?
Re: records within a quarter [message #345277 is a reply to message #345271] Tue, 02 September 2008 15:15 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Sorry about that , but how do i attach a .xls OR .mdb files as the data is in excel?

Thanks
Re: records within a quarter [message #345278 is a reply to message #345259] Tue, 02 September 2008 15:17 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Too bad for you that you refuse to read & follow posting Guidelines

You're On Your Own (YOYO)!
Re: records within a quarter [message #345282 is a reply to message #345267] Tue, 02 September 2008 15:28 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
I think only plain TXT files are acceptable as attachments. Its better you give insert statements of the test data along with the description of the view you are using. Also post the results you are getting while you ran your query...

Good Luck finding answers.... Smile


Regards,
Jo
Re: records within a quarter [message #345283 is a reply to message #345278] Tue, 02 September 2008 15:34 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Sorry about all that. Please see the attached file.

Thanks
  • Attachment: prospec.txt
    (Size: 29.06KB, Downloaded 106 times)
Re: records within a quarter [message #345331 is a reply to message #345283] Wed, 03 September 2008 02:09 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
You are asked to post test case.

1)Create Table
1)Insert Statements

Here is a sample how to do that.

create table test
(
id number,
name varchar2(20)
);

insert into test values(1,'Test1');
insert into test values(2,'Test2');


If you need quick answers then always post a testcase.

Regards,
Rajat
Re: records within a quarter [message #345387 is a reply to message #345283] Wed, 03 September 2008 05:22 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@aarti81,

Are you sure that was the result of your query? I am finding columns like contract_number, AMEND_STATUS_CODE, AMENDMENT_NUMBER which you have used in the query missing in the attached text file.

Quote:

SELECT calc_type,
CASE WHEN DAYS IS NULL AND NON_NULL_RECORDS > (TOTAL_RECORDS)/2 THEN MAX(DAYS) OVER (PARTITION BY TO_CHAR(SELECTION_DATE,'yy"Q"q')ORDER BY NULL) ELSE DAYS END DAYS,
contract_number,
NTPACT,
SELECTION_DATE,
AMENDREQ,
RECPROP,
AMEND_STATUS_CODE,
AMENDMENT_NUMBER,




Cant help until you post test case and the proper output of the query you have posted... Also to be frank, I am a bit confused about what exactly are the problems you face with your query.

Regards,
Jo
Re: records within a quarter [message #345408 is a reply to message #345387] Wed, 03 September 2008 07:48 Go to previous message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Some of the fileds that are not seen because they are just redundant, as i'm just trying to get the null and non-null records for each quarter.

Thanks
Previous Topic: lag function
Next Topic: trigger issue
Goto Forum:
  


Current Time: Sun Dec 11 00:05:32 CST 2016

Total time taken to generate the page: 0.04304 seconds