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  |
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 #196457 is a reply to message #196452] |
Thu, 05 October 2006 07:22  |
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 |
joy_division
Messages: 4963
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 #196644 is a reply to message #196629] |
Fri, 06 October 2006 06:04 |
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 |
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 #196953 is a reply to message #196949] |
Mon, 09 October 2006 05:58 |
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
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Dec 12 09:02:43 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
