Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Summarise the results based on start and end dates

cdprasadJul 15 2010 — edited Jul 15 2010
Hi All,

Can some body tells how to summarise a table results like in the below example?
I need help to get results as poc_dates_result from poc_dates table.
The data is supplied below to create the above tables.
Please provide the queries to convert poc_dates to poc_dates_result?

DROP TABLE poc_dates;

create table poc_dates
(
pid number
,start_dte date
,end_dte date
,prod char(3)
);

insert into poc_dates values(1,'01-JAN-2000','01-FEB-2000','PD1');
insert into poc_dates values(1,'02-FEB-2000','01-MAR-2000','PD1');
insert into poc_dates values(1,'02-MAR-2000','01-APR-2000','PD1');

insert into poc_dates values(1,'02-APR-2000','15-APR-2000','PD2');
insert into poc_dates values(1,'16-APR-2000','24-APR-2000','PD2');

insert into poc_dates values(1,'25-APR-2000','01-MAY-2000','PD3');
insert into poc_dates values(1,'02-MAY-2000','16-MAY-2000','PD3');

insert into poc_dates values(1,'17-MAY-2000','18-MAY-2000','PD1');
insert into poc_dates values(1,'19-MAY-2000','25-MAY-2000','PD1');

insert into poc_dates values(2,'01-JAN-2000','01-FEB-2000','PD1');
insert into poc_dates values(2,'02-FEB-2000','01-MAR-2000','PD1');

insert into poc_dates values(2,'02-MAR-2000','01-APR-2000','PD2');

insert into poc_dates values(2,'02-APR-2000','15-APR-2000','PD1');

insert into poc_dates values(2,'16-APR-2000','25-APR-2000','PD3');

insert into poc_dates values(3,'01-JAN-2000','01-FEB-2000','PD1');
insert into poc_dates values(3,'02-FEB-2000','01-MAR-2000','PD1');

insert into poc_dates values(3,'02-MAR-2000','01-APR-2000','PD2');

insert into poc_dates values(4,'01-JAN-2000','01-FEB-2000','PD1');

insert into poc_dates values(4,'02-FEB-2000','01-MAR-2000','PD2');

insert into poc_dates values(5,'01-JAN-2000','01-FEB-2000','PD1');

commit;

drop table poc_dates_result;

create table poc_dates_result
(
pid number
,start_dte date
,end_dte date
,prod char(3)
);

insert into poc_dates_result values(1,'01-JAN-2000','01-APR-2000','PD1');
insert into poc_dates_result values(1,'02-APR-2000','24-APR-2000','PD2');
insert into poc_dates_result values(1,'25-APR-2000','16-MAY-2000','PD3');
insert into poc_dates_result values(1,'17-MAY-2000','25-MAY-2000','PD1');
insert into poc_dates_result values(2,'01-JAN-2000','01-MAR-2000','PD1');
insert into poc_dates_result values(2,'02-MAR-2000','01-APR-2000','PD2');
insert into poc_dates_result values(2,'02-APR-2000','15-APR-2000','PD1');
insert into poc_dates_result values(2,'16-APR-2000','25-APR-2000','PD3');
insert into poc_dates_result values(3,'01-JAN-2000','01-MAR-2000','PD1');
insert into poc_dates_result values(3,'02-MAR-2000','01-APR-2000','PD2');
insert into poc_dates_result values(4,'01-JAN-2000','01-FEB-2000','PD1');
insert into poc_dates_result values(4,'02-FEB-2000','01-MAR-2000','PD2');
insert into poc_dates_result values(5,'01-JAN-2000','01-FEB-2000','PD1');

commit;

select * from poc_dates;

PID START_DTE END_DTE PROD
1 1/01/2000 1/02/2000 PD1
1 2/02/2000 1/03/2000 PD1
1 2/03/2000 1/04/2000 PD1
1 2/04/2000 15/04/2000 PD2
1 16/04/2000 24/04/2000 PD2
1 25/04/2000 1/05/2000 PD3
1 2/05/2000 16/05/2000 PD3
1 17/05/2000 18/05/2000 PD1
1 19/05/2000 25/05/2000 PD1
2 1/01/2000 1/02/2000 PD1
2 2/02/2000 1/03/2000 PD1
2 2/03/2000 1/04/2000 PD2
2 2/04/2000 15/04/2000 PD1
2 16/04/2000 25/04/2000 PD3
3 1/01/2000 1/02/2000 PD1
3 2/02/2000 1/03/2000 PD1
3 2/03/2000 1/04/2000 PD2
4 1/01/2000 1/02/2000 PD1
4 2/02/2000 1/03/2000 PD2
5 1/01/2000 1/02/2000 PD1


select * from poc_dates_result;

PID START_DTE END_DTE PROD
1 1/01/2000 1/04/2000 PD1
1 2/04/2000 24/04/2000 PD2
1 25/04/2000 16/05/2000 PD3
1 17/05/2000 25/05/2000 PD1
2 1/01/2000 1/03/2000 PD1
2 2/03/2000 1/04/2000 PD2
2 2/04/2000 15/04/2000 PD1
2 16/04/2000 25/04/2000 PD3
3 1/01/2000 1/03/2000 PD1
3 2/03/2000 1/04/2000 PD2
4 1/01/2000 1/02/2000 PD1
4 2/02/2000 1/03/2000 PD2
5 1/01/2000 1/02/2000 PD1


Thanks,
CD

Comments

780914
What is your required output?The data you have posted is just selected of what you inserted. What is the transformation required and what i sthe role of start and end date?
706888
Hi,

I guess what you want is this: 
insert into poc_dates_result
select pid,min(start_dte),max(end_dte),prod from poc_dates
group by pid,prod
Everything is fine except that this select will produce theses 2 different lines, for me: 
1,1/1/2000,5/25/2000,PD1
2,1/1/2000,4/15/2000,PD1
While you have :
PID,START_DTE,END_DTE,PROD
*1,1/1/2000*,4/1/2000,PD1
1,5/17/2000,*5/25/2000*,PD1

*2,1/1/2000*,3/1/2000,PD1
2,4/2/2000,*4/15/2000*,PD1

Did you make a mistake or there's another condition?
cdprasad
Hi buddies,

There is not mistake in what I have mentioned.

Input is poc_dates table data

Output is poc_dates_result table data


Start_dte and end_dte represents product start date and Product end date

For
eg

For PID = 1
Input table has 9 records for PID = 1
PD1 was the product till 01-04-2000
then it changed to PD2 till 24-04-2000
then it changed to PD3 till 16-05-2000
then it again changed to PD1 till 25-05-2000

so I want four records for PID =1 in the output where end date of each record will be last end date of that product before the product changes.




Thanks.

Edited by: cdprasad on Jul 15, 2010 12:28 PM

Edited by: cdprasad on Jul 15, 2010 12:29 PM
Frank Kulash
Hi,

Thanks for providing the CREATE TABLE and INSERT statemenets; that's very helpful.

Are you saying that you don't actually have a table like poc_date_results, but that you want a query, based entirely on poc_dates, that will give the same output as "SELECT * FROM poc_date_results"?
If so, that's very confusing. It would be much better if you just posted the statements for poc_dates, and the results you want.

Try this: 
WITH	got_grp		AS
(
	SELECT	pid, start_dte, end_dte, prod
	,	ROW_NUMBER () OVER ( PARTITION BY  pid
				     ORDER BY	   start_dte
				   )
	      -	ROW_NUMBER () OVER ( PARTITION BY  pid
				     ,		   prod
				     ORDER BY	   start_dte
				   )	AS grp
	FROM	poc_dates
)
SELECT	  pid
,	  MIN (start_dte)	AS start_dte
,	  MAX (end_dte)		AS end_dte
,	  prod
FROM	  got_grp
GROUP BY  pid
,	  prod
,	  grp
ORDER BY  pid
,	  start_dte
;
This assumes that no two rows in poc_dates with the same pid overlap; that is, given that there is a row 
insert into poc_dates values(1,'01-JAN-2000','01-FEB-2000','PD1');
there can be no other row with pid=1 and either a start_dte or an end_dte between 01-Jan-2000 and 01-Feb-2000 inclusive.

If you do have a table like poc_date_results, and you want to populate it from poc_dates, then use the query above in an INSERT statement.

If you do not have a table like poc_date_results, but you want to create one, then use the query above in a CREATE TABLE AS statement.
1 person found this helpful
cdprasad
Sorry Guys I shouldn't have provided the poc_data_results table. I though it would be handy for you check the results.


Thanks all for trying to helping me

Special thanks to Frank. I think now the query is answered.
cdprasad
Thanks for the help guyz.

Little enhancement in the requirement

Delete second row in the table poc_dates.

delete poc_dates where pid = '1' and start_dte = '02-FEB-2000';

I want the output as below if I delete that row(i.e second row in the table poc_dates)



PID START_DTE END_DTE PROD
1 1/01/2000 1/02/2000 PD1 => Change in the end date coz in the input the next record start date is not (current date + 1)
1 2/03/2000 1/04/2000 PD1 => This row added
1 2/04/2000 24/04/2000 PD2
1 25/04/2000 16/05/2000 PD3
1 17/05/2000 25/05/2000 PD1
2 1/01/2000 1/03/2000 PD1
2 2/03/2000 1/04/2000 PD2
2 2/04/2000 15/04/2000 PD1
2 16/04/2000 25/04/2000 PD3
3 1/01/2000 1/03/2000 PD1
3 2/03/2000 1/04/2000 PD2
4 1/01/2000 1/02/2000 PD1
4 2/02/2000 1/03/2000 PD2
5 1/01/2000 1/02/2000 PD1

Thanks,
CD
Frank Kulash
Hi,

If there are two (or more) criteria for determining when a new group starts, then it's probably simpler to use two sub-queries: one the see if a new group is starting, and another to count how many new groups have started up to now, which gives a group number. These have to be in separate sub-queries, because analytic functions can't be nested (that is, the argument of the anlytic SUM can't involve the analytic LAG function). 
WITH	got_new_grp	AS
(
	SELECT	pid, start_dte, end_dte, prod
	,	CASE
			WHEN  LAG (prod)    OVER ( PARTITION BY  pid
			      	  	    	   ORDER BY  	 start_dte
						 ) != prod
			OR    LAG (end_dte) OVER ( PARTITION BY  pid
			      	  	    	   ORDER BY  	 start_dte
						 ) < start_dte - 1
			THEN  1
		END	AS new_grp
	FROM	poc_dates
)
,	got_grp		AS
(
	SELECT  pid, start_dte, end_dte, prod
	,	SUM (new_grp) OVER ( PARTITION BY  pid
		    	      	     ORDER BY  	   start_dte
				   ) AS grp
	FROM	got_new_grp
)
SELECT	  pid
,	  MIN (start_dte)	AS start_dte
,	  MAX (end_dte)		AS end_dte
,	  prod
FROM	  got_grp
GROUP BY  pid
,     	  prod
,	  grp
ORDER BY  pid
,	  start_dte
;
Aketi Jyuuzou
This is rival solution of Tabibitosan method B-)
1007478
with poc_dates(pid,start_dte,end_dte,prod) as(
select 1,date '2000-01-01',date '2000-02-01','PD1' from dual union
select 1,date '2000-03-02',date '2000-04-01','PD1' from dual union
select 1,date '2000-04-02',date '2000-04-15','PD2' from dual union
select 1,date '2000-04-16',date '2000-04-24','PD2' from dual union
select 1,date '2000-04-25',date '2000-05-01','PD3' from dual union
select 1,date '2000-05-02',date '2000-05-16','PD3' from dual union
select 1,date '2000-05-17',date '2000-05-18','PD1' from dual union
select 1,date '2000-05-19',date '2000-05-25','PD1' from dual union
select 2,date '2000-01-01',date '2000-02-01','PD1' from dual union
select 2,date '2000-02-02',date '2000-03-01','PD1' from dual union
select 2,date '2000-03-02',date '2000-04-01','PD2' from dual union
select 2,date '2000-04-02',date '2000-04-15','PD1' from dual union
select 2,date '2000-04-16',date '2000-04-25','PD3' from dual union
select 3,date '2000-01-01',date '2000-02-01','PD1' from dual union
select 3,date '2000-02-02',date '2000-03-01','PD1' from dual union
select 3,date '2000-03-02',date '2000-04-01','PD2' from dual union
select 4,date '2000-01-01',date '2000-02-01','PD1' from dual union
select 4,date '2000-02-02',date '2000-03-01','PD2' from dual union
select 5,date '2000-01-01',date '2000-02-01','PD1' from dual)
select pid,to_char(min(start_dte),'yyyy-mm-dd') as start_dte,
to_char(max(end_dte),'yyyy-mm-dd') as end_dte,prod
from (select pid,start_dte,end_dte,prod,
      sum(willSum) over(partition by pid
                        order by start_dte) as GID
      from (select pid,start_dte,end_dte,prod,
            case when start_dte-1
                     =lag(end_dte) over(partition by pid,prod
                                        order by start_dte)
                 then 0 else 1 end as willSum
            from poc_dates))
group by pid,GID,prod
order by pid,GID;

PID  START_DTE   END_DTE     PRO
---  ----------  ----------  ---
  1  2000-01-01  2000-02-01  PD1
  1  2000-03-02  2000-04-01  PD1
  1  2000-04-02  2000-04-24  PD2
  1  2000-04-25  2000-05-16  PD3
  1  2000-05-17  2000-05-25  PD1
  2  2000-01-01  2000-03-01  PD1
  2  2000-03-02  2000-04-01  PD2
  2  2000-04-02  2000-04-15  PD1
  2  2000-04-16  2000-04-25  PD3
  3  2000-01-01  2000-03-01  PD1
  3  2000-03-02  2000-04-01  PD2
  4  2000-01-01  2000-02-01  PD1
  4  2000-02-02  2000-03-01  PD2
  5  2000-01-01  2000-02-01  PD1
1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 12 2010
Added on Jul 15 2010
8 comments
1,185 views