Hi
In the example below, I want to filter a table based on the priority of values in two columns. The priory order is Isowner then IsAuth.
I greatly appreciate any help you can provide.
SQL
CREATE TABLE tableName
(
bp_idvarchar(300),
customerIdvarchar(300),
IsOwnervarchar(300),
IsAuthvarchar(300)
);
INSERT INTO tableName (bp_id,customerId,IsOwner,IsAuth)
VALUES
('121', 'John Smith', '1', '0'),
('122', 'Mary Johnson', '1', '0'),
('123', 'Doris Bean', '0', '0'),
('123', 'Doris Bean', '0', '0'),
('123', 'Doris Bean', '1', '0'),
('124', 'Don Olson', '0', '1'),
('125', 'Sam Holder', '0', '0'),
('125', 'Sam Holder', '0', '1'),
('126', 'Lori Morin', '1', '0'),
('127', 'Stan Morris', '0', '1'),
('128', 'Jason Thomas', '1', '0'),
('128', 'Jason Thomas', '1', '0'),
('128', 'John Nell', '1', '0'),
('128', 'Jason Thomas', '0', '1'),
('128', 'Alex trader', '1', '0'),
('128', 'Jason Thomas', '1', '0'),
('129', 'Anna', '0', '1'),
('129', 'Dan James', '0', '1'),
('129', 'Anna', '0', '1'),
('129', 'Anna', '0', '0');
March 31, 2022 at 12:59 pm
Does this help? It uses the ROW_NUMBER function to perform the ordering and partitioning that you need.
DROP TABLE IF EXISTS #HectorSales;
CREATE TABLE #HectorSales
(
Id INT NOT NULL
,SalesID INT NULL
,SalesAgent VARCHAR(30) NULL
,Region VARCHAR(10) NULL
,SalesAmount DECIMAL(10, 2) NULL
,IsOwner BIT NULL
,IsAuth BIT NULL
,IsBen BIT NULL
);
INSERT #HectorSales
(
Id
,SalesID
,SalesAgent
,Region
,SalesAmount
,IsOwner
,IsAuth
,IsBen
)
VALUES
(1, 121, 'John Smith', 'West', 78931.01, 1, 0, 0)
,(2, 122, 'Mary Johnson', 'West', 8723412.61, 1, 0, 0)
,(3, 123, 'Doris Bean', 'West', 2000111.67, 0, 0, 1)
,(4, 123, 'Doris Bean', 'South', 2000111.67, 0, 0, 0)
,(5, 123, 'Doris Bean', 'West', 120834.81, 1, 0, 0)
,(6, 124, 'Don Olson', 'West', 508921.48, 0, 1, 0)
,(7, 125, 'Sam Holder', 'East', 8723412.61, 0, 0, 1)
,(8, 125, 'Sam Holder', 'East', 9834212.87, 0, 1, 0)
,(9, 126, 'Lori Morin', 'North', 2000111.67, 1, 0, 0)
,(10, 127, 'Stan Morris', 'East', 4562341.67, 0, 1, 0)
,(11, 128, 'Jason Thomas', 'East', 13424.51, 1, 0, 0)
,(12, 128, 'Jason Thomas', 'East', 22222.22, 0, 1, 0);
WITH ordered
AS (SELECT hs.Id
,hs.SalesID
,hs.SalesAgent
,hs.Region
,hs.SalesAmount
,hs.IsOwner
,hs.IsAuth
,hs.IsBen
,rn = ROW_NUMBER() OVER (PARTITION BY hs.SalesID ORDER BY hs.IsOwner DESC, hs.IsAuth DESC)
FROM #HectorSales hs)
SELECT ordered.Id
,ordered.SalesID
,ordered.SalesAgent
,ordered.Region
,ordered.SalesAmount
,ordered.IsOwner
,ordered.IsAuth
FROM ordered
WHERE ordered.rn = 1
ORDER BY ordered.Id;
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 4, 2022 at 11:48 am
Thanks Phil, It works perfectly except that sometimes there are multiple SalesIds where IsOwner = 1, but I want only unique values within the duplicate SalesAmounts. See the example below.
SalesId 128 has 6 rows but I only want row 11, 14 and 16 removing duplicate values for SalesAmount 2222 and IsAuth =1
Removing rows 12,13,15
and the same logic would apply to IsAuth also
Thanks
That's a bit trickier, but try this version and see whether it helps:
WITH ordered
AS
(SELECT
hs.Id
, hs.SalesID
, hs.SalesAgent
, hs.Region
, hs.SalesAmount
, hs.IsOwner
, hs.IsAuth
, hs.IsBen
, rn = ROW_NUMBER() OVER (PARTITION BY
hs.SalesID
, hs.SalesAmount
ORDER BY hs.IsOwner DESC
, hs.IsAuth DESC
)
FROM #HectorSales hs)
SELECT
ordered.Id
, ordered.SalesID
, ordered.SalesAgent
, ordered.Region
, ordered.SalesAmount
, ordered.IsOwner
, ordered.IsAuth
FROM ordered
WHERE ordered.rn = 1
AND NOT (ordered.IsOwner = 0 AND ordered.IsAuth = 0)
ORDER BY ordered.Id;
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 4, 2022 at 3:22 pm
It works great, thanks Phil.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply