How to exclude same rows in SELECT

user_9188324 0
Viewed 72

I have this script that creates a table and store Sale Order information. The scenario behind it is that once and Item is added into Sale Order it's Status is 'A' means Add. Later somehow customer wants that item to be removed so we add a new row with same details but Status as 'D' means Delete. Now I want to get only active Sale Order Items which should not include that item which was Added and then Removed from Order. Here's my script.

CREATE TABLE [dbo].[SALE_DETAIL](
    [ORDER_NUMBER] [varchar](50) NULL,
    [ITEM_NAME] [varchar](250) NULL,
    [QUANTITY] [int] NULL,
    [PRICE] [numeric](18, 0) NULL,
    [Status] [varchar](50) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[SALE_DETAIL] ([ORDER_NUMBER], [ITEM_NAME], [QUANTITY], [PRICE], [Status]) VALUES (N'SO-100-ORD-19', N'Double Bed', 5, CAST(70000 AS Numeric(18, 0)), N'A')
GO
INSERT [dbo].[SALE_DETAIL] ([ORDER_NUMBER], [ITEM_NAME], [QUANTITY], [PRICE], [Status]) VALUES (N'SO-100-ORD-19', N'Sofa', 5, CAST(10000 AS Numeric(18, 0)), N'A')
GO
INSERT [dbo].[SALE_DETAIL] ([ORDER_NUMBER], [ITEM_NAME], [QUANTITY], [PRICE], [Status]) VALUES (N'SO-100-ORD-19', N'Dining Table', 1, CAST(50000 AS Numeric(18, 0)), N'A')
GO
INSERT [dbo].[SALE_DETAIL] ([ORDER_NUMBER], [ITEM_NAME], [QUANTITY], [PRICE], [Status]) VALUES (N'SO-100-ORD-19', N'Sofa', 5, CAST(10000 AS Numeric(18, 0)), N'D')   
GO

The expected output I'm looking for should be something like this as Item 'Sofa' was cancelled from Order.

ORDER_NUMBER    ITEM_NAME       QTY PRICE
SO-100-ORD-19   Dining Table    1   50000
SO-100-ORD-19   Double Bed      5   70000

Query: 

SELECT ORDER_NUMBER, ITEM_NAME, QUANTITY, PRICE FROM [dbo].[SALE_DETAIL]
WHERE Status <> 'D'
GROUP BY ORDER_NUMBER, ITEM_NAME, QUANTITY, PRICE
sql-server
4 Answers

I would use NOT EXISTS:

SELECT SD.ORDER_NUMBER,
       SD.ITEM_NAME,
       SD.QUANTITY,
       SD.PRICE
FROM dbo.[SALE_DETAIL] SD
WHERE NOT EXISTS (SELECT 1
                  FROM dbo.[SALE_DETAIL] e
                  WHERE e.ORDER_NUMBER = SD.ORDER_NUMBER
                    AND e.ITEM_NAME = SD.ITEM_NAME
                    AND e.[Status] = 'D');
user_20299830

Logically, the set-based answer is to use EXCEPT:

declare @SALE_DETAIL table([ORDER_NUMBER] [varchar](50) NULL,
    [ITEM_NAME] [varchar](250) NULL,
    [QUANTITY] [int] NULL,[PRICE] [numeric](18, 0) NULL,[Status] [varchar](50) NULL)
INSERT @SALE_DETAIL ([ORDER_NUMBER], [ITEM_NAME], [QUANTITY], [PRICE], [Status]) VALUES
(N'SO-100-ORD-19', N'Double Bed', 5, CAST(70000 AS Numeric(18, 0)), N'A'),
(N'SO-100-ORD-19', N'Sofa', 5, CAST(10000 AS Numeric(18, 0)), N'A'),
(N'SO-100-ORD-19', N'Dining Table', 1, CAST(50000 AS Numeric(18, 0)), N'A'),
(N'SO-100-ORD-19', N'Sofa', 5, CAST(10000 AS Numeric(18, 0)), N'D')

select Order_number,Item_name,Quantity,Price
from @SALE_DETAIL
where Status = 'A'
except
select Order_number,Item_name,Quantity,Price
from @SALE_DETAIL
where Status = 'D'

Which produces the results you've asked for. However, note that for whetever reason, this often seems to perform poorly in practice, in which case something like Larnu's Answer may be preferred.

user_154980

In words: select all 'A'-records for which no matching 'D'-record exists.

In SQL:

SELECT ORDER_NUMBER, ITEM_NAME, QUANTITY, PRICE
FROM [dbo].[SALE_DETAIL] X
WHERE Status = 'A'
AND NOT EXISTS (
  SELECT 1
  FROM [dbo].[SALE_DETAIL] Y
  WHERE Y.Status       = 'D'
  AND   Y.ORDER_NUMBER = X.ORDER_NUMBER
  AND   Y.ITEM_NAME    = X.ITEM_NAME
)
user_12205500

You can try get expected result using not in operator as shown below.

SELECT DISTINCT A.ORDER_NUMBER, A.ITEM_NAME,A.QUANTITY, A.PRICE, A.Status
FROM SALE_DETAIL A
    where A.ITEM_NAME not in (select s.ITEM_NAME from SALE_DETAIL s
    where s.[Status] = 'D')

The output is as shown below

ORDER_NUMBER    ITEM_NAME   QUANTITY    PRICE   Status
------------------------------------------------------
SO-100-ORD-19   Dining Table    1       50000   A       
SO-100-ORD-19   Double Bed      5       70000   A

You can find the live demo Live Demo Here

user_105325000
Related