How to exclude same rows in SELECT

Question

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

Answer 1

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');

Answer 2

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.