Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2006
    Posts
    14

    Unanswered: Multi date column report using range

    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
    Last edited by loquin; 10-04-06 at 17:00.

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    I'll bite.

    What database?
    What scripting language?

    There's only a few hundred of each...
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Oct 2006
    Posts
    14
    I am using Oracle 10.1 database and the scrpting language is PL/SQL. I iwsh to excute this report later iRepoert jasperViewer once successfully executed.

    Thanks for your quick response.

    Hope this information would be suffice to sort out this issue.

    Jayesh


    Edit: Moved the thread to the appropriate (Oracle) forum... Lou
    Last edited by loquin; 10-05-06 at 15:11.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •