?
Solved

cancell duplicate orders

Posted on 2008-11-18
16
Medium Priority
?
225 Views
Last Modified: 2013-12-07
Hello Experts

I have a situation in the CustOrder table, for some reason I have noticed they were duplicate orders have been recorded,
the job which runs every wednesday considered it as new orders and submitted them to the broker to process.

I have loads of orders which I want to set the status from "2 Shared Ordered" to "Cancelled" for all those duplicate records.

in the sample below if you please see for "adam collins" they were 3 orders captured by the system all at "2008-09-10 07:40:03"

They have been sent to the broker for 3 times,

first at "2008-10-29 02:20:02" and then "05/11" and "12/11"

All I wanted to now is to keep the first record as it and set the status="Cancelled" for the other 2.

Please can someone advise me the SQL query for this?

Regards
Kay
0
Comment
Question by:learningnet
  • 6
  • 5
  • 3
  • +1
16 Comments
 

Author Comment

by:learningnet
ID: 22983407

Status		OrderID	RFname		Shareid	BrokerSent		DateOrder
===========================================================================================
2 Share Ordered	161308	Adam Arnold	303	2008-10-29 02:20:03	2008-10-20 10:37:40
2 Share Ordered	161500	Adam Collins	305	2008-10-29 02:20:02	2008-09-10 07:40:03
2 Share Ordered	161698	Adam Collins	305	2008-11-05 02:20:03	2008-09-10 07:40:03
2 Share Ordered	161946	Adam Collins	305	2008-11-12 02:20:02	2008-09-10 07:40:03
2 Share Ordered	161693	adam sherief	305	2008-11-05 02:20:03	2008-10-02 20:30:40
2 Share Ordered	161913	adam sherief	305	2008-11-12 02:20:03	2008-10-02 20:30:40
2 Share Ordered	161464	adam sherief	305	2008-10-29 02:20:03	2008-10-02 20:30:40
2 Share Ordered	138136	ADRIAN  MORRIS	252	2005-11-09 04:00:17	2005-11-05 10:24:30
2 Share Ordered	161564	Adrian Brindley	264	2008-11-05 02:20:05	2008-10-02 15:32:23
2 Share Ordered	161352	Adrian Brindley	264	2008-10-29 02:20:05	2008-10-02 15:32:23
 
 
Desired 
 
Status		OrderID	RFname		Shareid	BrokerSent		DateOrder
===========================================================================================
2 Share Ordered	161308	Adam Arnold	303	2008-10-29 02:20:03	2008-10-20 10:37:40
2 Share Ordered	161500	Adam Collins	305	2008-10-29 02:20:02	2008-09-10 07:40:03
Cancelled	161698	Adam Collins	305	2008-11-05 02:20:03	2008-09-10 07:40:03
Cancelled	161946	Adam Collins	305	2008-11-12 02:20:02	2008-09-10 07:40:03
2 Share Ordered	161693	adam sherief	305	2008-11-05 02:20:03	2008-10-02 20:30:40
Cancelled	161913	adam sherief	305	2008-11-12 02:20:03	2008-10-02 20:30:40
Cancelled	161464	adam sherief	305	2008-10-29 02:20:03	2008-10-02 20:30:40
2 Share Ordered	138136	ADRIAN  MORRIS	252	2005-11-09 04:00:17	2005-11-05 10:24:30
2 Share Ordered	161564	Adrian Brindley	264	2008-11-05 02:20:05	2008-10-02 15:32:23
Cancelled	161352	Adrian Brindley	264	2008-10-29 02:20:05	2008-10-02 15:32:23

Open in new window

0
 
LVL 20

Assisted Solution

by:chaitu chaitu
chaitu chaitu earned 800 total points
ID: 22983493

update table t1 set status='Cancelled' where
shareid=(select  min(shareid) from table t2
where t1.shareid=t2.shareid and t2.RFname in('Adam Arnold','Adam Collins','adam sherief','Adrian Brindley','ADRIAN  MORRIS'));

Open in new window

0
 
LVL 27

Expert Comment

by:sujith80
ID: 22983508
Try this.
update custorder A
set status = 'Cancelled'
where BrokerSent <> ( select min(BrokerSent)
                      from custorder B
		      where B.RFname = A.RFname);

Open in new window

0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 22983515
ignore previous one;
0
 

Author Comment

by:learningnet
ID: 22983580
hello folks,
thanks for your suggestions, will try this now

i just wanted to see using the SELECT statement if this is ok as i am updating live records i just want to take a chance ..

please can you advise me here?

select custorder A
set status = 'Cancelled'
where BrokerSent <> ( select min(BrokerSent)
                      from custorder B
                      where B.RFname = A.RFname);

thanks
0
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 22983598
following records aer eligilble for CANCEL
select OrderID,RFname,Shareid,BrokerSent from customer
where BrokerSent <> ( select min(BrokerSent)
                      from custorder B
                      where B.RFname = A.RFname);
0
 
LVL 27

Expert Comment

by:sujith80
ID: 22983643
>> as i am updating live records i just want to take a chance ..

If you are unsure, create a copy of your table and try the update on it first.
0
 

Author Comment

by:learningnet
ID: 22983821
chaituu
thanks for the SELECT statement

sujith89

ok, i have created a separate table and tried your query, i noticed that only few duplicate records are getting picked and for some reason its ignoring the rest

if you please see in the attached, only "adam sherief" records and rest still appearing it ...

i am unable to figure why its skipping and applying the change on few?

please advise



	2 Share Ordered	161500	Adam Collins	2008-10-29 02:20:02	2008-09-10 07:40:03
	2 Share Ordered	161946	Adam Collins	2008-11-12 02:20:02	2008-09-10 07:40:03
	2 Share Ordered	161698	Adam Collins	2008-11-05 02:20:03	2008-09-10 07:40:03
	2 Share Ordered	161693	adam sherief	2008-11-05 02:20:03	2008-10-02 20:30:40
	2 Share Ordered	161913	adam sherief	2008-11-12 02:20:03	2008-10-02 20:30:40
	2 Share Ordered	161779	Adrian Brindley	2008-11-12 02:20:06	2008-10-02 15:32:23
	2 Share Ordered	161564	Adrian Brindley	2008-11-05 02:20:05	2008-10-02 15:32:23

Open in new window

0
 

Author Comment

by:learningnet
ID: 22983831
does the column type makes any diference? i mean, BrokerSent / DateOrder fields are all having "nvarchar 50" as their data type

0
 
LVL 27

Expert Comment

by:sujith80
ID: 22985159
Isn't it the correct behaviour?
SQL> select * from custorder;
 
STATUS                  ORDERID RFNAME                                             BROKERSENT          DATEORDER
-------------------- ---------- -------------------------------------------------- ------------------- -------------------
2 Share Ordered          161500 Adam Collins                                       2008-10-29 02:20:02 2008-09-10 07:40:03
2 Share Ordered          161946 Adam Collins                                       2008-11-12 02:20:02 2008-09-10 07:40:03
2 Share Ordered          161698 Adam Collins                                       2008-11-05 02:20:03 2008-09-10 07:40:03
2 Share Ordered          161693 adam sherief                                       2008-11-05 02:20:03 2008-10-02 20:30:40
2 Share Ordered          161913 adam sherief                                       2008-11-12 02:20:03 2008-10-02 20:30:40
2 Share Ordered          161779 Adrian Brindley                                    2008-11-12 02:20:06 2008-10-02 15:32:23
2 Share Ordered          161564 Adrian Brindley                                    2008-11-05 02:20:05 2008-10-02 15:32:23
 
7 rows selected.
 
SQL> update custorder A
  2  set status = 'Cancelled'
  3  where BrokerSent <> ( select min(BrokerSent)
  4                        from custorder B
  5                   where B.RFname = A.RFname);
 
4 rows updated.
 
SQL> select * from custorder;
 
STATUS                  ORDERID RFNAME                                             BROKERSENT          DATEORDER
-------------------- ---------- -------------------------------------------------- ------------------- -------------------
2 Share Ordered          161500 Adam Collins                                       2008-10-29 02:20:02 2008-09-10 07:40:03
Cancelled                161946 Adam Collins                                       2008-11-12 02:20:02 2008-09-10 07:40:03
Cancelled                161698 Adam Collins                                       2008-11-05 02:20:03 2008-09-10 07:40:03
2 Share Ordered          161693 adam sherief                                       2008-11-05 02:20:03 2008-10-02 20:30:40
Cancelled                161913 adam sherief                                       2008-11-12 02:20:03 2008-10-02 20:30:40
Cancelled                161779 Adrian Brindley                                    2008-11-12 02:20:06 2008-10-02 15:32:23
2 Share Ordered          161564 Adrian Brindley                                    2008-11-05 02:20:05 2008-10-02 15:32:23
 
7 rows selected.

Open in new window

0
 

Author Comment

by:learningnet
ID: 22985408
No, this has only updated

        2 Share Ordered 161500  Adam Collins    2008-10-29 02:20:02     2008-09-10 07:40:03
        2 Share Ordered 161946  Adam Collins    2008-11-12 02:20:02     2008-09-10 07:40:03
        2 Share Ordered 161698  Adam Collins    2008-11-05 02:20:03     2008-09-10 07:40:03
        Cancelled             161913  adam sherief    2008-11-12 02:20:03     2008-10-02 20:30:40
        2 Share Ordered 161693  adam sherief    2008-11-05 02:20:03     2008-10-02 20:30:40
        2 Share Ordered 161913  adam sherief    2008-11-12 02:20:03     2008-10-02 20:30:40
        2 Share Ordered 161779  Adrian Brindley 2008-11-12 02:20:06     2008-10-02 15:32:23
        2 Share Ordered 161564  Adrian Brindley 2008-11-05 02:20:05     2008-10-02 15:32:23

and other few records from the table... i dont really understand why though !
0
 
LVL 27

Expert Comment

by:sujith80
ID: 22985493
What is the data type of BrokerSent?
I assumed that it is "DATE".
Looks like you have it as varchar2. Hopefully you dont have any spaces before and after the data.

Try the below update.

Bottom line - Use PROPER data types
update custorder A
set status = 'Cancelled'
where to_date(ltrim(rtrim(BrokerSent)),yyyy-mm-dd hh24:mi:ss) <> ( select min(to_date(ltrim(rtrim(BrokerSent)),yyyy-mm-dd hh24:mi:ss))
                      from custorder B
		      where B.RFname = A.RFname);

Open in new window

0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 200 total points
ID: 22987509
See attached.
update.txt
0
 

Author Comment

by:learningnet
ID: 23017904
hello awking00
thanks for the update
i am getting error following error

Error in list of function arguments: 'OVER' not recognized.
Missing FROM clause.
Error in ORDER BY clause.
Unable to parse query text.

when i tried this statement

select OrderID,RFname,Shareid,BrokerSent from customer
where rowid in
(select row_id from
 (select rowid row_id
  ,row_number() over (partition by shareid, dateorder order by dateorder, brokersent) rn
  from custorder)
 where rn > 1);

please advise
0
 
LVL 27

Accepted Solution

by:
sujith80 earned 1000 total points
ID: 23020164
Have you tried this?
update custorder A
set status = 'Cancelled'
where to_date(ltrim(rtrim(BrokerSent)),yyyy-mm-dd hh24:mi:ss) <> ( select min(to_date(ltrim(rtrim(BrokerSent)),yyyy-mm-dd hh24:mi:ss))
                      from custorder B
		      where B.RFname = A.RFname);

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 23027860
The solution I provided was for Oracle since I saw the question in the PL/SQL zone. I don't know if MS SQL has anything similar to the analytical queries of Oracle. Sorry :-(
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question