Multi date column report using range ...

Multi date column report using range - pl/sql

Posted in the Oracle Corporation Forum

jayesh

London, UK

#1 Oct 4, 2006
Hi,

I would like to know the solution for writing the new script of the required output of the below query:

The multi-date column like day1,day2, day3 is hard-coded in the query. It prompts the date for each day or date column while execution of the query. Instead of it, I require to prompt
only start date and end date for the mentioned column. At the same time, I also require to display the output of each column as mentioned in the specified date range as per the condition given.

Means, if there are six days between start and end date then it should display six date column with my applied condition and other columns.

Current Query:

SELECT CLI,
CASE WHEN (count(d27) > 0) THEN 1 ELSE 0 END as day1,
TO_NUMBER(CASE WHEN (count(d28) > 0) THEN 1 ELSE 0 END) as day2,
TO_NUMBER(CASE WHEN (count(d29) > 0) THEN 1 ELSE 0 END) as day3,
TO_NUMBER(CASE WHEN (count(d30) > 0) THEN 1 ELSE 0 END) as day4
FROM (
SELECT c.CLI,
DECODE( TRUNC(c.CALL_DATE), TO_DATE($P{day1},'YYYY-MM-DD') , 1) d27,
DECODE( TRUNC(c.CALL_DATE), TO_DATE($P{day2},'YYYY-MM-DD') , 1) d28,
DECODE( TRUNC(c.CALL_DATE), TO_DATE($P{day3},'YYYY-MM-DD') , 1) d29,
DECODE( TRUNC(c.CALL_DATE), TO_DATE($P{day4},'YYYY-MM-DD') , 1) d30
FROM calls c
WHERE c.CALL_DATE BETWEEN
TO_DATE($P{day1},'YYYY-MM-DD') AND
TO_DATE($P{day4}||' 23:59:59','YYYY-MM-DD HH24:MI:SS')
$P!{filterBy}
)
GROUP BY CLI
ORDER BY CLI

Current Output:

CLI Day1 Day2 Day3 Day4 Total
441132394629 0 1 0 0 1
441132533793 0 0 0 1 1
441142373223 0 0 0 1 1
441162611449 0 0 1 0 1
441207566886 0 1 0 0 1
441227763301 0 1 0 1 2
441233637736 0 0 0 1 1
441252874882 0 0 1 0 1
441273861500 0 0 1 0 1
441274504470 0 0 1 0 1
441282699900 0 0 0 1 1
441291421019 0 0 1 0 1
441295272411 0 0 1 0 1
441296582599 0 0 1 0 1
441305812817 0 0 1 0 1
441315380271 0 0 0 1 1
441315562413 0 0 1 0 1

The required Output should be based on the date given as start and end date. It may be 2 or 4 or 6 days.

Your help would be highly appreciated.

Thanks in advance.

Jayesh

Tell me when this thread is updated:

Subscribe Now Add to my Tracker

Add your comments below

Characters left: 4000

Please note by submitting this form you acknowledge that you have read the Terms of Service and the comment you are posting is in compliance with such terms. Be polite. Inappropriate posts may be removed by the moderator. Send us your feedback.

Oracle Corporation Discussions

Title Updated Last By Comments
News Rimini Street Named to Inc. Magazine's List of ... (Aug '15) Aug '15 rajani 1
News A Rare Glimpse Inside The Life And Mind Of Orac... (Jan '15) Jan '15 Kid_Tomorrow 1
News SaaS and Cloud ERP Observations: Is Cloud ERP R... (Nov '13) Jun '14 Dimitar 2
News Oregon Governor Seeks Lawsuit Over Health Exchange (May '14) May '14 United Clowns 1
News Breaking (Bad?) Sap Hana Boss Vishal Sikka Resigns (May '14) May '14 Berblinger 1
News Oracle buys San Bruno-based Responsys for $1 bi... (Dec '13) Feb '14 Nature 4
News Enrollment in Obamacare very small in first day... (Nov '13) Nov '13 Space Cowboy 35
More from around the web