April 17, 2018 at 9:20 am
I have a table that has 100's of products in it. I need to identify all unique GROUPs of Products that do not have a Master Indicator = to 1. All NON-Master's have indicators of 2 but ALL Groups of Products MUST HAVE 1 record = to 1. IF not then FLAG for review
The Results based on this data should like the following
Product | Indicator = 1 (Yes or No)
PHOTO WHITE 600I | Yes
PHOTO BLUE 600I | No
PHOTO RED 600I | Yes
PHOTO ORANGE | No
PIC XL63 | N
TRANS 67t | Y
DUL-567Q | N
ACCN-009 FX | Y
OR 12-BX | N
VV-12 | Y
CREATE TABLE Products (
Product nvarchar(32) NULL,
MasterIndicator int NULL
);INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (1,'PHOTO WHITE 600I',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (2,'PHOTO WHITE 600I',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (3,'PHOTO WHITE 600I',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (4,'PHOTO WHITE 600I',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (5,'PHOTO WHITE 600I',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (6,'PHOTO BLACK 600I',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (7,'PHOTO BLACK 600I',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (8,'PHOTO BLACK 600I',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (9,'PHOTO BLACK 600I',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (10,'PHOTO BLACK 600I',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (11,'PHOTO BLACK 600I',1)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (12,'PHOTO BLUE 600I',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (13,'PHOTO BLUE 600I',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (14,'PHOTO BLUE 600I',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (15,'PHOTO BLUE 600I',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (16,'PHOTO RED 600I',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (17,'PHOTO RED 600I',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (18,'PHOTO RED 600I',1)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (19,'PHOTO ORANGE',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (20,'PHOTO ORANGE',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (21,'PHOTO ORANGE',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (22,'PIC XL63',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (23,'PIC XL63',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (24,'PIC XL63',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (25,'PIC XL63',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (26,'TRANS 67t',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (27,'TRANS 67t',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (28,'TRANS 67t',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (29,'TRANS 67t',1)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (30,'DUL-567Q',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (31,'DUL-567Q',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (32,'DUL-567Q',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (33,'DUL-567Q',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (34,'DUL-567Q',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (35,'DUL-567Q',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (36,'ACCN-009 FX',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (37,'ACCN-009 FX',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (38,'ACCN-009 FX',1)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (39,'OR 12-BX',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (40,'OR 12-BX',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (41,'OR 12-BX',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (42,'OR 12-BX',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (43,'VV-12',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (44,'VV-12',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (45,'VV-12',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (46,'VV-12',2)
INSERT INTO Table (ProductID, Product, MasterIndicator) VALUE (47,'VV-12',1)
I
April 17, 2018 at 10:00 am
Not sure you have your sample data in agreement with your expected results. I interpreted your meaning to have the output column named "Indicator - 1 (Yes or No)" as having a Yes when there exists a group member with a MasterIndicator column value of 1, and a value of No otherwise, and you would investigate the No's. Here's code you can look at the results from and compare to your expectations:CREATE TABLE #Products (
ProductID int NOT NULL PRIMARY KEY CLUSTERED,
Product nvarchar(32) NULL,
MasterIndicator int NULL
);
INSERT INTO #Products (ProductID, Product, MasterIndicator)
VALUES (1,'PHOTO WHITE 600I',2),
(2,'PHOTO WHITE 600I',2),
(3,'PHOTO WHITE 600I',2),
(4,'PHOTO WHITE 600I',2),
(5,'PHOTO WHITE 600I',2),
(6,'PHOTO BLACK 600I',2),
(7,'PHOTO BLACK 600I',2),
(8,'PHOTO BLACK 600I',2),
(9,'PHOTO BLACK 600I',2),
(10,'PHOTO BLACK 600I',2),
(11,'PHOTO BLACK 600I',1),
(12,'PHOTO BLUE 600I',2),
(13,'PHOTO BLUE 600I',2),
(14,'PHOTO BLUE 600I',2),
(15,'PHOTO BLUE 600I',2),
(16,'PHOTO RED 600I',2),
(17,'PHOTO RED 600I',2),
(18,'PHOTO RED 600I',1),
(19,'PHOTO ORANGE',2),
(20,'PHOTO ORANGE',2),
(21,'PHOTO ORANGE',2),
(22,'PIC XL63',2),
(23,'PIC XL63',2),
(24,'PIC XL63',2),
(25,'PIC XL63',2),
(26,'TRANS 67t',2),
(27,'TRANS 67t',2),
(28,'TRANS 67t',2),
(29,'TRANS 67t',1),
(30,'DUL-567Q',2),
(31,'DUL-567Q',2),
(32,'DUL-567Q',2),
(33,'DUL-567Q',2),
(34,'DUL-567Q',2),
(35,'DUL-567Q',2),
(36,'ACCN-009 FX',2),
(37,'ACCN-009 FX',2),
(38,'ACCN-009 FX',1),
(39,'OR 12-BX',2),
(40,'OR 12-BX',2),
(41,'OR 12-BX',2),
(42,'OR 12-BX',2),
(43,'VV-12',2),
(44,'VV-12',2),
(45,'VV-12',2),
(46,'VV-12',2),
(47,'VV-12',1);
SELECT P.Product,
CASE MIN(P.MasterIndicator)
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [Indicator = 1 (Yes or No)]
FROM #Products AS P
GROUP BY P.Product
ORDER BY MIN(P.ProductID);
DROP TABLE #Products;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 17, 2018 at 12:58 pm
Great work - you were correct in your logic. NOW My next step is to update those records that = No. For those that = no then we would take the MIN(ProductID) record and update the MasterIndicator to 1.
Can I use part of this query to run that update?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply