Finding duplicates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Finding duplicates

Author

Topic

andrew a
Starting Member

6 Posts

Posted - 2012-04-11 : 10:07:34

I would welcome advice on the following.
Two columns:
1) order number (int) can be multiple rows
2) order complete (sinit) either 1 (order complete) or 2 (order in progress)

There should be only one record in an order set with ‘1’ in the order complete column i.e.
order number-----------order complete
6745-------------------------2
6745-------------------------2
6745-------------------------1
6745-------------------------2
Unfortunately due to the way the system is currently set up it is possible to have instances where the ‘1’ order complete exists on two or more records i.e.

order number-----------order complete
8945-------------------------1
8945-------------------------2
8945-------------------------1
8945-------------------------2

We have script that go through this file and inserts the data and a validation decision into a validation database. i.e.

In the above case the result in the validation file would be as follows:

order number-------order complete-----order complete validation
8945--------------------1--------------------invalid
8945--------------------2--------------------invalid
8945--------------------1--------------------invalid
8945--------------------2--------------------invalid
This will show up on a report where a visual decision has to be made on which row has the wrong data.

The are other validations in the script that inserts data into the validation table performed by a series of case statements which work fine because they are just confirming a column(field) is within a certain range i.e.
Case
When Qty betweeen(1 and 9)
Then 'Valid'
else 'Invalid'
end QTYCheck

When using the insert command a select within a select will fail(won’t it, becasue they found set of records has changed) so I thought creating a self join (so you could do a conditional test via a relationship) would be the way forward.
Thanks in advance.

andrew a
Starting Member

6 Posts

Posted - 2012-04-11 : 10:15:29

I suppose another subject heading for this would be how to find duplicates without using a select statement with concatinated join keys.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-04-11 : 11:07:34

select orderno, count(*) as cnt
from table
where ordercomplete = 1
group by orderno
having count(*) > 1

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-12 : 00:56:06

[code]
SELECT [order number],[order complete],CASE WHEN Occ >1 THEN 'Invalid' ELSE 'Valid' END
FROM
(
SELECT *,COUNT(CASE WHEN [order complete] =1 THEN 1 END) OVER (PARTITION BY [order number]) AS Occ
FROM Table
)t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

andrew a
Starting Member

6 Posts

Posted - 2012-04-12 : 17:40:57

Thank you webfred, visakh16
I've created some code(if you can call it that :-)) to try and demonstrate what's going on. The first file is the main table.
use LEIS

CREATE TABLE dbo.OrderItems
( OrderItem int not null IDENTITY (1,1)
,OrderNumber int not null
,LastItemOnOrder tinyint not null
,OrderQty tinyint not null
,ItemInStock tinyint not null
,pk_lioo varchar(20) not null
);

INSERT INTO dbo.OrderItems (OrderNumber, LastItemOnOrder, OrderQty, ItemInStock, pk_lioo)
VALUES (7032357 , 1 , 1 , 1 , '7032357:1' )
INSERT INTO dbo.OrderItems (OrderNumber, LastItemOnOrder, OrderQty, ItemInStock, pk_lioo)
VALUES (7032357, 2 , 3 , 1 , '7032357:2' )
INSERT INTO dbo.OrderItems (OrderNumber, LastItemOnOrder, OrderQty, ItemInStock, pk_lioo)
VALUES (7032357 , 1 , 19 , 2 , '7032357:1' )
INSERT INTO dbo.OrderItems (OrderNumber, LastItemOnOrder, OrderQty, ItemInStock, pk_lioo)
VALUES (7034826 , 1 , 2 , 1 , '7034826:1' )
INSERT INTO dbo.OrderItems (OrderNumber, LastItemOnOrder, OrderQty, ItemInStock, pk_lioo)
VALUES (7034826 , 2 , 1 , 0 , '7034826:2' )
INSERT INTO dbo.OrderItems (OrderNumber, LastItemOnOrder, OrderQty, ItemInStock, pk_lioo)
VALUES (7088650 , 2 , 2 , 1 , '7088650:2' )
INSERT INTO dbo.OrderItems (OrderNumber, LastItemOnOrder, OrderQty, ItemInStock, pk_lioo)
VALUES (7088650 , 1 , 1 , 2 , '7088650:1' )
INSERT INTO dbo.OrderItems (OrderNumber, LastItemOnOrder, OrderQty, ItemInStock, pk_lioo)
VALUES (7088650 , 2 , 3 , 0 , '7088650:2' )
INSERT INTO dbo.OrderItems (OrderNumber, LastItemOnOrder, OrderQty, ItemInStock, pk_lioo)
VALUES (7088650 , 1 , 40 , 1, '7088650:1' )
INSERT INTO dbo.OrderItems (OrderNumber, LastItemOnOrder, OrderQty, ItemInStock, pk_lioo)
VALUES (7088650 , 2 , 5 , 2 , '7088650:2' )
INSERT INTO dbo.OrderItems (OrderNumber, LastItemOnOrder, OrderQty, ItemInStock, pk_lioo)
VALUES (7171003 , 1 , 3 , 1 , '7171003:1' )
INSERT INTO dbo.OrderItems (OrderNumber, LastItemOnOrder, OrderQty, ItemInStock, pk_lioo)
VALUES (7171003 , 2 , 1 , 1 , '7171003:2' )
INSERT INTO dbo.OrderItems (OrderNumber, LastItemOnOrder, OrderQty, ItemInStock, pk_lioo)
VALUES (7171003 , 2 , 2 , 2 , '7171003:2' )
INSERT INTO dbo.OrderItems (OrderNumber, LastItemOnOrder, OrderQty, ItemInStock, pk_lioo)
VALUES (7175553 , 1 , 1 , 1 , '7175553:1' )

This is a view to create the validation data. It all works fine until I introduce the 2nd join (which i’ve commented out, so try both states) I can see what's going on but I dont understand why I can’t have two joins in the same select statement. I’m from a Filemaker background so it’s a bit frustrating as this would’ve taken me 2 minutes to set up this database. Thanks in advance and and pointers (apart from do it Filemaker) would be most appreciated. Unfortunately both of your selects statements error if introduced into this create view staement.

USE LEIS
GO
CREATE VIEW dbo.OrderItemsView
AS
SELECT fk1.OrderNumber
,fk1.LastItemOnOrder
,CASE
WHEN fk1.LastItemOnOrder IN(1,2)
THEN 'Valid'
ELSE 'Invalid'
END LIIOcheck
,fk1.OrderQty
,CASE
WHEN fk1.OrderQty BETWEEN 1 AND 9
THEN 'Valid'
ELSE 'Invalid'
END OQcheck
,fk1.ItemInStock
,CASE
WHEN fk1.ItemInStock BETWEEN 1 AND 2
THEN 'Valid'
ELSE 'Invalid'
END lIScheck

,COUNT(pk2.pk_lioo) countE
,CASE
WHEN COUNT(pk2.pk_lioo) > 1
THEN 'Invalid'
ELSE 'Valid'
END lioocheck

--,MAX(pk3.OrderQty) MaxQty

FROM dbo.OrderItems fk1

left join dbo.OrderItems pk2
on CAST(fk1.OrderNumber as varchar) + ':1' = pk2.pk_lioo

--left join dbo.OrderItems pk3
--on fk1.OrderNumber = pk3.OrderNumber

group by fk1.OrderNumber, fk1.LastItemOnOrder, fk1.ItemInStock, fk1.OrderQty
go

andrew a
Starting Member

6 Posts

Posted - 2012-04-12 : 17:45:02

Please note it's the COUNT part of the code that's goin wrong, all the othe case staements straight forward.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-13 : 11:57:35

you're not using count as i suggested. Can you try using it along with PARTITION as in my suggestion?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources