We help IT Professionals succeed at work.
Troubleshooting Question

How to leave one record and get remaining records of the same status type

46 Views
Last Modified: 2020-10-29
My requirement is if the payment_type='ecommerce'  and status='Active' then I should leave one 'Pending' record get remaining 'Pending' records for that particular guid.


Create Table OrderSubscription(id number(5),guid varchar2(20),payment_type varchar2(20),plan_id varchar2(20),status varchar2(20));

insert into OrderSubscription Values(1,'abcdef','ecommerce','120','Cancelled');
insert into OrderSubscription Values (2,'abcdef','Enterprise','365','Pending');
insert into OrderSubscription Values (3,'abcdef','Enterprise','365','Pending');
insert into OrderSubscription Values (4,'abcdef','ecommerce','Half-120','Active');


insert into OrderSubscription Values(1,'xyzedfv','ecommerce','120','Cancelled');
insert into OrderSubscription Values (2,'xyzedfv','Enterprise','365','Pending');
insert into OrderSubscription Values (3,'xyzedfv','Enterprise','365','Pending');
insert into OrderSubscription Values (4,'xyzedfv','Enterprise','365','Pending');
insert into OrderSubscription Values (5,'xyzedfv','Enterprise','365','Pending');
insert into OrderSubscription Values (6,'xyzedfv','Enterprise','365','Active');


insert into OrderSubscription Values(1,'ghijklmn','ecommerce','120','Cancelled');
insert into OrderSubscription Values (2,'ghijklmn','Enterprise','365','Pending');
insert into OrderSubscription Values (3,'ghijklmn','Enterprise','365','Pending');
insert into OrderSubscription Values (4,'ghijklmn','Enterprise','365','Pending');
insert into OrderSubscription Values (5,'ghijklmn','Enterprise','365','Pending');
insert into OrderSubscription Values (6,'ghijklmn','Enterprise','365','Pending');
insert into OrderSubscription Values (7,'ghijklmn','Enterprise','365','Pending');
insert into OrderSubscription Values (8,'ghijklmn','Enterprise','365','Pending');
insert into OrderSubscription Values (9,'ghijklmn','ecommerce','Half-120','Active');


insert into OrderSubscription Values(1,'fghynuj','ecommerce','120','Cancelled');
insert into OrderSubscription Values (2,'fghynuj','Enterprise','365','Pending');
insert into OrderSubscription Values (3,'fghynuj','Enterprise','365','Pending');
insert into OrderSubscription Values (4,'fghynuj','Enterprise','365','Active');




Comment
Watch Question

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
is this oracle?

Author

Commented:
SQL server
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
looks like this is for oracle since number(x,y) and varchar2 is oracle specific...

anyways, from this data, what do you need,
can you create an excel and post here / or screenshot

Create Table OrderSubscription(id int, guid varchar(20), payment_type varchar(20), plan_id varchar(20),status varchar(20));

insert into OrderSubscription Values (1,'abcdef','ecommerce','120','Cancelled');
insert into OrderSubscription Values (2,'abcdef','Enterprise','365','Pending');
insert into OrderSubscription Values (3,'abcdef','Enterprise','365','Pending');
insert into OrderSubscription Values (4,'abcdef','ecommerce','Half-120','Active');

insert into OrderSubscription Values (1,'xyzedfv','ecommerce','120','Cancelled');
insert into OrderSubscription Values (2,'xyzedfv','Enterprise','365','Pending');
insert into OrderSubscription Values (3,'xyzedfv','Enterprise','365','Pending');
insert into OrderSubscription Values (4,'xyzedfv','Enterprise','365','Pending');
insert into OrderSubscription Values (5,'xyzedfv','Enterprise','365','Pending');
insert into OrderSubscription Values (6,'xyzedfv','Enterprise','365','Active');

insert into OrderSubscription Values (1,'ghijklmn','ecommerce','120','Cancelled');
insert into OrderSubscription Values (2,'ghijklmn','Enterprise','365','Pending');
insert into OrderSubscription Values (3,'ghijklmn','Enterprise','365','Pending');
insert into OrderSubscription Values (4,'ghijklmn','Enterprise','365','Pending');
insert into OrderSubscription Values (5,'ghijklmn','Enterprise','365','Pending');
insert into OrderSubscription Values (6,'ghijklmn','Enterprise','365','Pending');
insert into OrderSubscription Values (7,'ghijklmn','Enterprise','365','Pending');
insert into OrderSubscription Values (8,'ghijklmn','Enterprise','365','Pending');
insert into OrderSubscription Values (9,'ghijklmn','ecommerce','Half-120','Active');

insert into OrderSubscription Values (1,'fghynuj','ecommerce','120','Cancelled');
insert into OrderSubscription Values (2,'fghynuj','Enterprise','365','Pending');
insert into OrderSubscription Values (3,'fghynuj','Enterprise','365','Pending');
insert into OrderSubscription Values (4,'fghynuj','Enterprise','365','Active');

select * from OrderSubscription;
above is working script for SQL Server...

Author

Commented:
sorry for that.results should come like this.

if I pass  guid ='abcdef'  then it should leave one Pending record and should come other Pending Record  because it has 'ecommerce' , 'Active'

if I pass  guid ='xyzedfv'  then all Pending Records should be displayed  because it has 'Enterprise'  ,'Active'

if I pass  guid ='ghijklmn'  then 6 Pending Records should be displayed leaving 1 Pending Record  because it has 'ecommerce '  ,'Active'

if I pass  guid ='fghynuj '  then all Pending Records should be displayed  because it has 'Enterprise'  ,'Active'


HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
you want this?

with ea as (
select guid
  from OrderSubscription
 where payment_type='ecommerce' and status='active'
),
pt as (
select os.guid, os.id, row_number() over (partition by os.guid order by id) rn
  from OrderSubscription os 
 inner join ea on ea.guid=os.guid and os.status='pending'
),
p as (select * from pt where rn<>1)
select os.*
  from OrderSubscription os
  left join p on os.guid=p.guid and os.id=p.id
 where p.id is null
 order by os.guid, os.id

Author

Commented:
I need only Pending records for their guid.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
do you need this?

with ea as (
select guid
  from OrderSubscription
 where payment_type='ecommerce' and status='active'
),
t as (
select os.*, 
case when ea.guid is null then 0 
else row_number() over (partition by os.guid order by os.id) end rn
from OrderSubscription os
left join ea on ea.guid=os.guid 
where os.status='pending'
)
select * from t
where rn<=1
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
above query shows first pending...
if you want to show last pending use Line 9
else row_number() over (partition by os.guid order by os.id desc) end rn
+ you can add
order by guid, id
at the end to sort the records properly...

Author

Commented:
results are coming wrong.for example for  guid='ghijklmn' id=6,7 records are not coming.there are total 7 pending records.if you leave one pending then remaining 6 records should come but in the results only 4 pending records are coming
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
results are coming wrong 
can you just create an excel file showing what you need, based on the sample data provided...

Author

Commented:
final records should come like this.



HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
with t as (
select *,
       case when
            os.status='Pending'
            and
            lead(payment_type,1) over (partition by guid order by id)='ecommerce'
            and 
            lead(status,1) over (partition by guid order by id)='Active'
            then 0 else 1 end inc
  from OrderSubscription os
)
select *
  from t where status='Pending' and inc=1
 order by guid, id

Author

Commented:
can you please explain the syntax of lead(payment_type,1) over (partition by guid order by id)='ecommerce'.
lead(status,1) over (partition by guid order by id)='Active'
what it will do ?

Author

Commented:
or Is there any easy way to achieve the result?
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
lead(status,1) over (partition by guid order by id)='Active'
means:

group by guid, order by id.
if status = 'Active' for the next (1 record ahead) record in the group...
 
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
so,
inc=1 if next record is not active/ecommerce
inc=0 if next record id active/ecommerce
in the same group...

Author

Commented:
your query will fail if active ecommerce is first record for that guid.if this record is last one then will work otherwise it will fail.How will you tackle this issue?

insert into OrderSubscription Values (1,'jhgysddd','ecommerce','Half-120','Active');
insert into OrderSubscription Values(2,'jhgysddd','ecommerce','120','Cancelled');
insert into OrderSubscription Values (3,'jhgysddd','Enterprise','365','Pending');
insert into OrderSubscription Values (4,'jhgysddd','Enterprise','365','Pending');
Alex [***Alex140181***]Software Developer
CERTIFIED EXPERT

Commented:
Alex [***Alex140181***]Software Developer
CERTIFIED EXPERT

Commented:
your query will fail if active ecommerce is first record for that guid.if this record is last one then will work otherwise it will fail.How will you tackle this issue?
What should be the result if the 1st record is not 'Cancelled' ?!

Author

Commented:
You can ignore cancelled records
Alex [***Alex140181***]Software Developer
CERTIFIED EXPERT

Commented:
You did not answer my question properly ;-)
HainKurt's query obviously works for the initial setup you posted at the beginning (where all rows start with  'Cancelled' values)!
Now, you come up with
insert into OrderSubscription Values (1,'jhgysddd','ecommerce','Half-120','Active');
insert into OrderSubscription Values(2,'jhgysddd','ecommerce','120','Cancelled');
insert into OrderSubscription Values (3,'jhgysddd','Enterprise','365','Pending');
insert into OrderSubscription Values (4,'jhgysddd','Enterprise','365','Pending');     
and say, this won't work with these values!
So, again: What should be the result, if the 1st value is NOT 'Cancelled'??!!

Author

Commented:
then only this record should come  (3,'jhgysddd','Enterprise','365','Pending');  
As I mentioned we are interested with 'ecommerce','Active';if this record exists then for that guid skip one Pending record.
Information Technology Specialist
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION