Home » SQL & PL/SQL » SQL & PL/SQL » Multi date column report using range
Multi date column report using range [message #196213] Wed, 04 October 2006 09:17 Go to next message
jaydba
Messages: 86
Registered: September 2006
Location: UK
Member

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


Re: Multi date column report using range [message #196388 is a reply to message #196213] Thu, 05 October 2006 03:40 Go to previous messageGo to next message
jaydba
Messages: 86
Registered: September 2006
Location: UK
Member

Hi,

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
Re: Multi date column report using range [message #196452 is a reply to message #196388] Thu, 05 October 2006 07:02 Go to previous messageGo to next message
jaydba
Messages: 86
Registered: September 2006
Location: UK
Member

Hi,

Can anyone please treat it on the most top priority basis?

Thanks
Jayesh
Re: Multi date column report using range [message #196456 is a reply to message #196452] Thu, 05 October 2006 07:19 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I'm sure you got a long way underway in the last day?
Show us how far you got and what's missing.

Do NOT cross-post this same post to other forums on this site.
Re: Multi date column report using range [message #196457 is a reply to message #196452] Thu, 05 October 2006 07:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In general, if you're not getting any responses, it means that your problem looks too complex / difficult to understand for the amount of information you've provided.
Insisting that it is our top priority doesn't really help.

If you can get us a create table statment, some inserts, and a second attempt to explain what the problem is, as I don't understand the first one, then I'll try to take a look at it.
Re: Multi date column report using range [message #196460 is a reply to message #196452] Thu, 05 October 2006 07:43 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
jaydba wrote on Thu, 05 October 2006 08:02

Hi,

Can anyone please treat it on the most top priority basis?

Thanks
Jayesh


Well, I was going to save a few lives at the hospital here today, but what the heck, I'll just let them wait. Maybe they will live. Let me move your question to the top of my to-do list instead.
Re: Multi date column report using range [message #196629 is a reply to message #196457] Fri, 06 October 2006 05:12 Go to previous messageGo to next message
jaydba
Messages: 86
Registered: September 2006
Location: UK
Member

Hi,

I have specified the structure of table, current query, current output and required output in the
attachment file for more easier to understand the complexity of query.

Hope this information would suffice to fulfil my requirement.

Please let me know if you need any more information.

Thanks
Jayesh


Re: Multi date column report using range [message #196644 is a reply to message #196629] Fri, 06 October 2006 06:04 Go to previous messageGo to next message
jaydba
Messages: 86
Registered: September 2006
Location: UK
Member

Hi,
I would like to update more on it

Right now this report produces, for a determined range of 4 days, a list of numbers that have played only 1 day, 2 days, 3 days and 4 days.
The changes we need to do are:

1) We don't really care about the specific numbers, so we only want to know how many different numbers played 1 day, how many played 2 days, etc.

2) Instead of using 4 specific dates, we need to enter only 2 dates that will act as a data range. So if we enter 2006-10-01 and 2006-10-08, the report should produce a list of how many numbers played 1 day, how many played 2 days, 3 days, 4 days, 5 days and so up to 8 days.

For example, the query should return something like this:

Times_played Qty
1 5234
2 4256
3 3567
4 2846
5 2412
6 1428
7 823
8 45


Thanks in advance.

your help would highly be appreciated for your quick response.

Jayesh
Re: Multi date column report using range [message #196650 is a reply to message #196644] Fri, 06 October 2006 06:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you clarify some of your terminology for us.

What do you mean when you say 'Played 1 day' or 'played 3 days'.
Do you mean 'This CLI has 1 (or 3) rows with a CALL_DATE between the dates specified'

If so, you can simply do this (you didn't supply any sample data, as I asked you to, so I've had to make my own up. Thanks.)
create table cli_temp (cli  varchar2(10), cli_date date);

insert into cli_temp values( 'CLI 1',  to_date('01-10-2006','dd-mm-yyyy'));
insert into cli_temp values( 'CLI 1',  to_date('02-10-2006','dd-mm-yyyy'));
insert into cli_temp values( 'CLI 1',  to_date('03-10-2006','dd-mm-yyyy'));
insert into cli_temp values( 'CLI 1',  to_date('04-10-2006','dd-mm-yyyy'));
insert into cli_temp values( 'CLI 1',  to_date('05-10-2006','dd-mm-yyyy'));
insert into cli_temp values( 'CLI 2',  to_date('01-10-2006','dd-mm-yyyy'));
insert into cli_temp values( 'CLI 2',  to_date('02-10-2006','dd-mm-yyyy'));
insert into cli_temp values( 'CLI 2',  to_date('10-10-2006','dd-mm-yyyy'));
insert into cli_temp values( 'CLI 2',  to_date('11-10-2006','dd-mm-yyyy'));
insert into cli_temp values( 'CLI 2',  to_date('12-10-2006','dd-mm-yyyy'));
insert into cli_temp values( 'CLI 3',  to_date('01-09-2006','dd-mm-yyyy'));
insert into cli_temp values( 'CLI 3',  to_date('01-10-2006','dd-mm-yyyy'));
insert into cli_temp values( 'CLI 4',  to_date('02-10-2006','dd-mm-yyyy'));
insert into cli_temp values( 'CLI 5',  to_date('02-10-2006','dd-mm-yyyy'));
insert into cli_temp values( 'CLI 6',  to_date('03-10-2006','dd-mm-yyyy'));

SQL> select cnt Played, count(*) quantity
  2  from  (select cli,count(*) cnt
  3         from   cli_temp
  4         where  cli_date between to_date('01-10-2006','dd-mm-yyyy') and to_date('10-10-2006','dd-mm-yyyy')
  5         group by cli)
  6  group by cnt;

    PLAYED   QUANTITY
---------- ----------
         1          4
         3          1
         5          1
Re: Multi date column report using range [message #196949 is a reply to message #196650] Mon, 09 October 2006 05:47 Go to previous messageGo to next message
jaydba
Messages: 86
Registered: September 2006
Location: UK
Member

Hello,

I'm grateful to you. However, I'll test it an ouput and discuss with my developer about the achived result and get back to you as soon as possible.

Many thanks

Jayesh
Re: Multi date column report using range [message #196953 is a reply to message #196949] Mon, 09 October 2006 05:58 Go to previous messageGo to next message
jaydba
Messages: 86
Registered: September 2006
Location: UK
Member

Hello,

I would like to update more in details as per the discussion with developer as below:

Churn Show By Show

Right now this report produces, for a determined range of 4 days, a list of numbers that have played only 1 day, 2 days, 3 days and 4 days.
The changes we need to do are:

1) We don't really care about the specific numbers, so we only want to know how many different numbers played 1 day, how many played 2 days, etc.

2) Instead of using 4 specific dates, we need to enter only 2 dates that will act as a data range. So if we enter 2006-10-01 and 2006-10-08, the report should produce a list of how many numbers played 1 day, how many played 2 days, 3 days, 4 days, 5 days and so up to 8 days.

For example, the query should return something like this:

Times_played Qty
1 5234
2 4256
3 3567
4 2846
5 2412
6 1428
7 823
8 45

Ideally, the date range should be dynamic, (can be passed as startDate and endDate parameters). If this makes the query too complex and it takes too long to achieve, we'll leave that part out for the moment.

Awaiting the desired script at the earliest.

Many thanks in advance.

Jayesh
Re: Multi date column report using range [message #196966 is a reply to message #196953] Mon, 09 October 2006 06:46 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
And this is different from the query that I just provided you in what fashion?
Previous Topic: Deletion of a BLOB data type
Next Topic: Timestamp of DML and DDL staatements
Goto Forum:
  


Current Time: Sat Dec 10 13:12:43 CST 2016

Total time taken to generate the page: 0.09554 seconds