April 12, 2018 at 11:39 am
I have a table that looks like this
Batch ID | Batch Number | Batch Description | Archive
1 | MASTER | COLOR RED |
2 | 0001 | COLOR RED |
3 | 0002 | COLOR RED |
4 | 0012 | COLOR RED |
5 | 10/123 | COLOR RED |
6 | 10/211 | COLOR RED |
7 | MASTER | COLOR BLUE123 |
8 | 0001 | COLOR BLUE123 |
9 | 0002 | COLOR BLUE123 |
10 | 0012 | COLOR BLUE123 |
11 | 10/123 | COLOR BLUE123 |
12 | 10/211 | COLOR BLUE123 |
13 | MASTER | COLOR YELLOW 23 |
14 | 09/23 | COLOR YELLOW 23 |
15 | 10/01 | COLOR YELLOW 23 |
16 | MASTER | COLOR ORANGE 23 |
I would like to update the Archive column to "Yes" (see below, those marked in Red) for each subsequent records, (Based on unique Batch Description)
In other words All Batch Numbers that = Master DO NOT Get updated to Archive = Yes
Nor do the TOP 2 records for each unique Batch Description Get updated to Archive = Yes
ONLY the reaming, or would I would call OLDER records (Those with MIN BatchID is Less than the Max BatchID Top 2
Where is gets tricky is in the last example. As you can see there are only 2 subsequent Batches after the master record was created. I do not want to Archive them based on the fact there are only two. In some cases there may only be One Master Records and No Subsequent records for any given unique Batch Description In that case NOTHING gets updated to Archive = Yes
Batch ID | Batch Number | Batch Description | Archive
1 | MASTER | COLOR RED |
2 | 0001 | COLOR RED | YES
3 | 0002 | COLOR RED | YES
4 | 0012 | COLOR RED | YES
5 | 10/123 | COLOR RED |
6 | 10/211 | COLOR RED |
7 | MASTER | COLOR BLUE123 |
8 | 0001 | COLOR BLUE123 | YES
9 | 0002 | COLOR BLUE123 | YES
10 | 0012 | COLOR BLUE123 | YES
11 | 10/123 | COLOR BLUE123 |
12 | 10/211 | COLOR BLUE123 |
13 | MASTER | COLOR YELLOW 23 |
14 | 09/23 | COLOR YELLOW 23 |
15 | 10/01 | COLOR YELLOW 23 |
16 | MASTER | COLOR ORANGE 23 |
April 12, 2018 at 12:01 pm
netguykb - Thursday, April 12, 2018 11:39 AMI have a table that looks like this
Batch ID | Batch Number | Batch Description | Archive
1 | MASTER | COLOR RED |
2 | 0001 | COLOR RED |
3 | 0002 | COLOR RED |
4 | 0012 | COLOR RED |
5 | 10/123 | COLOR RED |
6 | 10/211 | COLOR RED |7 | MASTER | COLOR BLUE123 |
8 | 0001 | COLOR BLUE123 |
9 | 0002 | COLOR BLUE123 |
10 | 0012 | COLOR BLUE123 |
11 | 10/123 | COLOR BLUE123 |
12 | 10/211 | COLOR BLUE123 |13 | MASTER | COLOR YELLOW 23 |
14 | 09/23 | COLOR YELLOW 23 |
15 | 10/01 | COLOR YELLOW 23 |16 | MASTER | COLOR ORANGE 23 |
I would like to update the Archive column to "Yes" (see below, those marked in Red) for each subsequent records, (Based on unique Batch Description)
In other words All Batch Numbers that = Master DO NOT Get updated to Archive = Yes
Nor do the TOP 2 records for each unique Batch Description Get updated to Archive = Yes
ONLY the reaming, or would I would call OLDER records (Those with MIN BatchID is Less than the Max BatchID Top 2
Where is gets tricky is in the last example. As you can see there are only 2 subsequent Batches after the master record was created. I do not want to Archive them based on the fact there are only two. In some cases there may only be One Master Records and No Subsequent records for any given unique Batch Description In that case NOTHING gets updated to Archive = YesBatch ID | Batch Number | Batch Description | Archive
1 | MASTER | COLOR RED |
2 | 0001 | COLOR RED | YES
3 | 0002 | COLOR RED | YES
4 | 0012 | COLOR RED | YES
5 | 10/123 | COLOR RED |
6 | 10/211 | COLOR RED |7 | MASTER | COLOR BLUE123 |
8 | 0001 | COLOR BLUE123 | YES
9 | 0002 | COLOR BLUE123 | YES
10 | 0012 | COLOR BLUE123 | YES
11 | 10/123 | COLOR BLUE123 |
12 | 10/211 | COLOR BLUE123 |13 | MASTER | COLOR YELLOW 23 |
14 | 09/23 | COLOR YELLOW 23 |
15 | 10/01 | COLOR YELLOW 23 |16 | MASTER | COLOR ORANGE 23 |
This is not as difficult as it may appear. If you are able to provide sample DDL and corresponding INSERT statements to create the necessary test data, someone will solve it for you within minutes.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 12, 2018 at 12:02 pm
Please post your sample data in a consumable format (script including create table statement with inserts).
April 13, 2018 at 8:51 am
netguykb - Thursday, April 12, 2018 11:39 AMI have a table that looks like this
Batch ID | Batch Number | Batch Description | Archive
1 | MASTER | COLOR RED |
2 | 0001 | COLOR RED |
3 | 0002 | COLOR RED |
4 | 0012 | COLOR RED |
5 | 10/123 | COLOR RED |
6 | 10/211 | COLOR RED |7 | MASTER | COLOR BLUE123 |
8 | 0001 | COLOR BLUE123 |
9 | 0002 | COLOR BLUE123 |
10 | 0012 | COLOR BLUE123 |
11 | 10/123 | COLOR BLUE123 |
12 | 10/211 | COLOR BLUE123 |13 | MASTER | COLOR YELLOW 23 |
14 | 09/23 | COLOR YELLOW 23 |
15 | 10/01 | COLOR YELLOW 23 |16 | MASTER | COLOR ORANGE 23 |
I would like to update the Archive column to "Yes" (see below, those marked in Red) for each subsequent records, (Based on unique Batch Description)
In other words All Batch Numbers that = Master DO NOT Get updated to Archive = Yes
Nor do the TOP 2 records for each unique Batch Description Get updated to Archive = Yes
ONLY the reaming, or would I would call OLDER records (Those with MIN BatchID is Less than the Max BatchID Top 2
Where is gets tricky is in the last example. As you can see there are only 2 subsequent Batches after the master record was created. I do not want to Archive them based on the fact there are only two. In some cases there may only be One Master Records and No Subsequent records for any given unique Batch Description In that case NOTHING gets updated to Archive = YesBatch ID | Batch Number | Batch Description | Archive
1 | MASTER | COLOR RED |
2 | 0001 | COLOR RED | YES
3 | 0002 | COLOR RED | YES
4 | 0012 | COLOR RED | YES
5 | 10/123 | COLOR RED |
6 | 10/211 | COLOR RED |7 | MASTER | COLOR BLUE123 |
8 | 0001 | COLOR BLUE123 | YES
9 | 0002 | COLOR BLUE123 | YES
10 | 0012 | COLOR BLUE123 | YES
11 | 10/123 | COLOR BLUE123 |
12 | 10/211 | COLOR BLUE123 |13 | MASTER | COLOR YELLOW 23 |
14 | 09/23 | COLOR YELLOW 23 |
15 | 10/01 | COLOR YELLOW 23 |16 | MASTER | COLOR ORANGE 23 |
Try this:CREATE TABLE #Batch (
BatchID int NOT NULL PRIMARY KEY CLUSTERED,
BatchNumber varchar(10),
BatchDescription varchar(25),
Archive varchar(3)
);
INSERT INTO #Batch (BatchID, BatchNumber, BatchDescription, Archive)
VALUES (1, 'MASTER', 'COLOR RED', NULL),
(2, '0001', 'COLOR RED', 'YES'),
(3, '0002', 'COLOR RED', 'YES'),
(4, '0012', 'COLOR RED', 'YES'),
(5, '10/123', 'COLOR RED', NULL),
(6, '10/211', 'COLOR RED', NULL),
(7, 'MASTER', 'COLOR BLUE123', NULL),
(8, '0001', 'COLOR BLUE123', 'YES'),
(9, '0002', 'COLOR BLUE123', 'YES'),
(10, '0012', 'COLOR BLUE123', 'YES'),
(11, '10/123', 'COLOR BLUE123', NULL),
(12, '10/211', 'COLOR BLUE123', NULL),
(13, 'MASTER', 'COLOR YELLOW 23', NULL),
(14, '09/23', 'COLOR YELLOW 23', NULL),
(15, '10/01', 'COLOR YELLOW 23', NULL),
(16, 'MASTER', 'COLOR ORANGE 23', NULL);
UPDATE B
SET B.Archive = 'YES'
FROM (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY BatchDescription ORDER BY BatchNumber DESC) - 1 AS RowNum
FROM #Batch
) AS B
WHERE B.BatchNumber <> 'MASTER'
AND B.RowNum NOT IN (1,2);
SELECT *
FROM #Batch;
DROP TABLE #Batch;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 16, 2018 at 10:18 am
I noticed that in your code your ordering by ORDER BY BatchNumber DESC. The problem with that is the data I have in some cases may not be just numbers but also letters. SO I changed it to the following: ORDER BY BatchID DESC BECAUSE when records are duplicated from a Master Batch Record the most recent BatchID (I.E. the higher number) top 2 are the ones we do not update BUT the records prior are the ones we do update to Archive = Y.
I have uploaded the entire data file so you could see the various anomalies in the data. I also changed the BatchNumber for All Master records from "Master" to "000000" so obviously this also changes your script. (Looks like CSV has them as "0")
Also here is table script
Any help would be great! Thank you!
CREATE TABLE BatchMasterV3 (
BatchID int NULL,
ProductCategory nvarchar(1) NULL,
BatchNumber nvarchar(16) NULL,
ProductDesc nvarchar(32) NULL,
DateRequired nvarchar(8) NULL,
CodeNumber nvarchar(16) NULL,
Customer_Number int NULL,
SpecialInstructions nvarchar(512) NULL,
Viscometer nvarchar(16) NULL,
TotLbsInBatch nvarchar(8) NULL,
WeightPerGallon nvarchar(4) NULL,
CostPerPound int NULL,
Master int NULL,
Archive nvarchar(1) NULL
);
Your code changed based on above mentioned:
UPDATE B
SET B.Archive = 'Y'
FROM (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY ProductDesc ORDER BY BatchID DESC) - 1 AS RowNum
FROM BatchMasterV3
) AS B
WHERE B.BatchNumber <> '000000'
AND B.RowNum NOT IN (1,2);
April 16, 2018 at 10:54 am
So If the following is ran:
SELECT *
--SET B.Archive = 'Y'
FROM (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY ProductDesc ORDER BY BatchID DESC) - 1 AS RowNum
FROM BatchMasterV3
) AS B
WHERE (B.BatchNumber <> '000000' AND B.ProductDesc = '032 RED CV INK')
AND (B.RowNum NOT IN (1,2));
SELECT * FROM BatchMasterV3 WHERE ProductDesc = '032 RED CV INK'
The records that should not be Updated are: BatchID 2069 and 2098 Since both of them have the highest BatchID's in the bunch.
2069,1,110,032 RED CV INK
2098,1,111,032 RED CV INK
... and the rest of them should be updated HOWEVER after running the update query BatchNumber 109 and 110 are not updated But 111 is updated and it should not be.
Here's the update query for this specific record as an example:
UPDATE B
SET B.Archive = 'Y'
FROM (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY ProductDesc ORDER BY BatchID DESC) - 1 AS RowNum
FROM BatchMasterV3
) AS B
WHERE (B.BatchNumber <> '000000' AND B.RowNum NOT IN (1,2)) AND (B.ProductDesc = '032 RED CV INK');
April 16, 2018 at 11:25 am
netguykb - Monday, April 16, 2018 10:54 AMSo If the following is ran:
SELECT *
--SET B.Archive = 'Y'
FROM (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY ProductDesc ORDER BY BatchID DESC) - 1 AS RowNum
FROM BatchMasterV3
) AS B
WHERE (B.BatchNumber <> '000000' AND B.ProductDesc = '032 RED CV INK')
AND (B.RowNum NOT IN (1,2));SELECT * FROM BatchMasterV3 WHERE ProductDesc = '032 RED CV INK'
The records that should not be Updated are: BatchID 2069 and 2098 Since both of them have the highest BatchID's in the bunch.
2069,1,110,032 RED CV INK
2098,1,111,032 RED CV INK
... and the rest of them should be updated HOWEVER after running the update query BatchNumber 109 and 110 are not updated But 111 is updated and it should not be.Here's the update query for this specific record as an example:
UPDATE B
SET B.Archive = 'Y'
FROM (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY ProductDesc ORDER BY BatchID DESC) - 1 AS RowNum
FROM BatchMasterV3
) AS B
WHERE (B.BatchNumber <> '000000' AND B.RowNum NOT IN (1,2)) AND (B.ProductDesc = '032 RED CV INK');
I suggest that you modify the setup script already provided by Steve to include the problematic data, to give people something to work with.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 16, 2018 at 2:56 pm
netguykb - Monday, April 16, 2018 10:54 AMSo If the following is ran:
SELECT *
--SET B.Archive = 'Y'
FROM (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY ProductDesc ORDER BY BatchID DESC) - 1 AS RowNum
FROM BatchMasterV3
) AS B
WHERE (B.BatchNumber <> '000000' AND B.ProductDesc = '032 RED CV INK')
AND (B.RowNum NOT IN (1,2));SELECT * FROM BatchMasterV3 WHERE ProductDesc = '032 RED CV INK'
The records that should not be Updated are: BatchID 2069 and 2098 Since both of them have the highest BatchID's in the bunch.
2069,1,110,032 RED CV INK
2098,1,111,032 RED CV INK
... and the rest of them should be updated HOWEVER after running the update query BatchNumber 109 and 110 are not updated But 111 is updated and it should not be.Here's the update query for this specific record as an example:
UPDATE B
SET B.Archive = 'Y'
FROM (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY ProductDesc ORDER BY BatchID DESC) - 1 AS RowNum
FROM BatchMasterV3
) AS B
WHERE (B.BatchNumber <> '000000' AND B.RowNum NOT IN (1,2)) AND (B.ProductDesc = '032 RED CV INK');
I'm not prepared to open a spreadsheet that's been posted due to work security requirements, so here's the best I can do:CREATE TABLE #Batch (
BatchID int NOT NULL PRIMARY KEY CLUSTERED,
BatchNumber varchar(10),
BatchDescription varchar(25),
Archive varchar(1)
);
INSERT INTO #Batch (BatchID, BatchNumber, BatchDescription, Archive)
VALUES (1, '000000', 'COLOR RED', NULL),
(2, '0001', 'COLOR RED', 'Y'),
(3, '0002', 'COLOR RED', 'Y'),
(4, '0012', 'COLOR RED', 'Y'),
(5, '10/123', 'COLOR RED', NULL),
(6, '10/211', 'COLOR RED', NULL),
(7, '000000', 'COLOR BLUE123', NULL),
(8, '0001', 'COLOR BLUE123', 'Y'),
(9, '0002', 'COLOR BLUE123', 'Y'),
(10, '0012', 'COLOR BLUE123', 'Y'),
(11, '10/123', 'COLOR BLUE123', NULL),
(12, '10/211', 'COLOR BLUE123', NULL),
(13, '000000', 'COLOR YELLOW 23', NULL),
(14, '09/23', 'COLOR YELLOW 23', NULL),
(15, '10/01', 'COLOR YELLOW 23', NULL),
(16, '000000', 'COLOR ORANGE 23', NULL);
SELECT *
FROM #Batch;
UPDATE B
SET B.Archive = 'Y'
FROM (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY BatchDescription ORDER BY BatchID DESC) AS RowNum
FROM #Batch
--ORDER BY BatchID
) AS B
WHERE B.BatchNumber <> '000000'
AND B.RowNum NOT IN (1,2);
SELECT *
FROM #Batch;
DROP TABLE #Batch;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply