| Author |
 Topic  |
|
|
andrew a
Starting Member
United Kingdom
6 Posts |
 Posted - 04/11/2012 : 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
United Kingdom
6 Posts |
Posted - 04/11/2012 : 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
Flowing Fount of Yak Knowledge
Germany
8513 Posts |
Posted - 04/11/2012 : 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
India
47023 Posts |
Posted - 04/12/2012 : 00:56:06
|
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
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
andrew a
Starting Member
United Kingdom
6 Posts |
Posted - 04/12/2012 : 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
United Kingdom
6 Posts |
Posted - 04/12/2012 : 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
India
47023 Posts |
Posted - 04/13/2012 : 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/
|
|
|
| |
Topic |
|
|
|
Finding duplicates
