Need help with select within select - daterange

[Former Member]

I use Crystal Reports v12.3.0.601 - I am a beginner.

Problem:

TABLE: ACCOUNTBILLFEE

Columns are   

FOLDERRSN

STAMPDATE

BILLNUMBER

PAYMENTAMOUNT

There are over 500,000 rows/ records...

And I need to report the FOLDERRSN which has at least one {ACCOUNTBILLFEE.STAMPDATE} in DateTime
(2014, 05, 01, 00, 00, 01) to DateTime (2014, 05, 31, 23, 59, 59)

Out-put required is:

FOLDERSN | STAMPDATE | BILLNUMBER   | PAYMENTAMOUNT

Group by FOLDERRSN

1010234               May01,2014                 1111                      25000

                              May25, 2014                1112                       5000

                              Jan 05, 2013                  998                          500

1034567                May5, 2014                11325                       5000

1033999                May15, 2014               6752                       15000

                              Dec5 , 2011                1132                       25000

Please help -

The critical part for me, is to display  payments not within the date range in 'select expert' statement.

Currenlty my report reflects only payments for FOLDERRSN, where {ACCOUNTBILLFEE.STAMPDATE} in DateTime

(2014, 05, 01, 00, 00, 01) to DateTime (2014, 05, 31, 23, 59, 59) and not other payments outside the date range specified.

Thank you for your time.

[abhilash_kumar · Accepted solution]

Hi Sridhar,

What database are you reporting against?

-Abhilash

[Former Member · Accepted solution]

Backend is Oracle Thanks Abilash

[abhilash_kumar · Accepted solution]

Easiest way to do this is to create the report against a SQL Query via the 'Add Command' option. The SQL would be:

Select * from ACCOUNTBILLFEE A Where Exists

(Select * from ACCOUNTBILLFEE B where B.FOLDERSN = A.FOLDERSN AND B.STAMPDATE IN (TO_DATE('2014/05/01 00:00:01', 'YYYY/MM/DD HH24:MI:SS' TO TO_DATE('2014/05/31 23:59:59', 'YYYY/MM/DD HH24:MI:SS'))

-Abhilash

[Former Member · Accepted solution]

Somehow, I cannot use 'Add Command' in Crystal --- I guess our IT Dept has locked it down for me.

I only have read access to the database.

[Former Member · Accepted solution]

Hello Abhilash,

In my Crystal report interface, I see like this in my 'Database Expert'. Please refer below....

amp8

   *Add Command

     + DATABASE_NAME1

               + TABLES

               + STORED PROCEDURES

     + DATABASE_NAME2 (not my data)

     + DATABASE_NAME3 (not my data)

If I hit ADD COMMAND as you suggested, how do I reference the DATABASE_NAME1 ?

Regs,

Sridhar Lam

NB: The table ACCOUNTBILLFEE is under DATABASE_NAME1 >>>TABLES

[abhilash_kumar · Accepted solution]

1) Open the existing report that points to the table above > Go to the Database option on the top > Click 'Show SQL' > Copy the SQL.

2) Go to create new report > Database Expert > Click the 'Add Command' option > Paste the SQL Query > Modify the query and include the Where Exists Subquery along with the tables alias.

The existing sql should have the schema and table qualifier  - if it doesn't, that's OK.

-Abhilash

[Former Member · Accepted solution]

Hi Abilash,

This worked !!!

My brother helped me with it here....ofcourse you have intiated the intial idea.

It worked when i used the following SQL at 'Add Command'

Select * from DATABASE_NAME.ACCOUNTBILLFEE A

Where A.FOLDERSN = any ( select B.FOLDERSN from DATABASE_NAME.ACCOUNTBILLFEE B

where B.STAMPDATE >= TO_DATE('20140501', 'YYYYMMDD')

AND  B.STAMPDATE <= TO_DATE('20140531', 'YYYYMMDD'))

Excellent support - Thank you so much for your immediate attention and response.

I know, how hard it is to understand someones requirement and suggest solutions.

You are the best and most helpful I have ever come across in my life.

Thank you for your kind heart and extending help to me.

Regs,

Sridhar Lam