PeopleSoft Enterprise

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?

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?

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

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.

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.

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

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
;

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