March 31, 2022 at 11:58 am
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_id varchar(300),
customerId varchar(300),
IsOwner varchar(300),
IsAuth varchar(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;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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
April 4, 2022 at 1:49 pm
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;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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