Help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John Wright - 21 Jul 2008 20:43 GMT

I have a problem. I just inherited a program that is full of holes and bugs
and I need to do some patching. Just to note everyone, I did not set up
this database and I did not code this program, I am stuck with it as is.
That being said, I have to do some checks on a database to make sure pairs
of material are in the same carton.

For example. In carton 12345 pieces 1A and 1B must be in the carton.

The table is set up as follows:

Piece varchar(12)
InspectedByDate Datetime
InspectedBy varchar(20)
CartonNumber varchar(15)
Traveler varchar(8)

So the data should look like the following:

1A 5/5/2008 RR 081064 55132R
1B 5/5/2008 RR 081064 55132R
2A 5/5/2008 RR 081064 55132R
2B 5/5/2008 RR 081064 55132R
...

I can do a quick check to make sure there are an even number in the carton,
what I need help on is matching up the A and B pairs in the table. I though
of grabbing all the "A" pieces then using a sub-query to do a like search
for "B" pieces, but am stuck. Anyone have any suggestions?

John

Reply to this Message

--CELKO-- - 21 Jul 2008 21:46 GMT

>> I just inherited a program that is full of holes and bugs and I need to do some patching. Just to note everyone, I did not set up this database and I did not code this program, I am stuck with it as is. <<

Don't be ashamed to re-write it if it really stinks -- "No matter how
far you have gone down the wrong road, turn around." -- Turkish
proverb

>> The table is set up as follows: <<

Real DDL would help, so now we are doing wild a.s guessing about keys
and constraints, etc.

CREATE TABLE Inspections -- wild guess at name of table!
(piece_id VARCHAR(12) NOT NULL,
inspection_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
inspector_id VARCHAR(20) NOT NULL,
carton_nbr VARCHAR(15) NOT NULL,
traveler_stuff VARCHAR(8) NOT NULL, --weird data element name ..
PRIMARY KEY (carton_nbr, piece_id));

So the data should look like the following:

1A 5/5/2008 RR 081064 55132R
1B 5/5/2008 RR 081064 55132R
2A 5/5/2008 RR 081064 55132R
2B 5/5/2008 RR 081064 55132R
...

>> I can do a quick check to make sure there are an even number in the carton, what I need help on is matching up the A and B pairs in the table. <<

Since we have no idea how a piece_id is defined from the lack of
proper DDL, let's assume that you can split off the the alpha postfix.

CREATE VIEW MatchedParts (carton_nbr, piece_id_prefix)
AS
SELECT carton_nbr, piece_id_prefix
FROM Inspections AS I1
GROUP BY carton_nbr, piece_id_prefix
HAVING COUNT(*) = 2
AND MIN(piece_id_postfix) = 'A'
AND MAX(piece_id_postfix) = 'B';

The VIEW will always be current and correct.

>> I though of grabbing all the "A" pieces then using a sub-query to do a like search for "B" pieces, but am stuck. Anyone have any suggestions? <<

Wrong approach. You are thinking in procedural steps and not sets.
If I partition the table (set) into subsets (groups) what
characteristics does each well-formed subset have? COUNT(*) = 2 is
one of them, but that might be covered by the DDL -- I will assume
that you need it, given no DDL. What is the range of the subset? 'A'
to 'B'.

Reply to this Message

Roy Harvey (SQL Server MVP) - 21 Jul 2008 22:24 GMT

Are all the pieces in pairs, or are there also singletons (A only or
no suffix), trios (A, B and C suffix), or other variations? Is it
always A and B, or is it sometimes X and Y, etc? Can the same Piece
appear more than once?

Assuming the simplest case of only A and B pairs this should give you
some ideas to get you started.

SELECT X.CartonNumber,
CASE WHEN X.ItemCount <> X.DistinctPieces
THEN 'Duplicate Pieces'
WHEN X.DistinctPieces <> 2 * DistinctShort
THEN 'Pairing Error'
ELSE 'Looks OK to me'
END
FROM (SELECT CartonNumber, count(*) as ItemCount,
COUNT(distinct Piece) as DistinctPieces,
COUNT(distinct LEFT(Piece,LEN(Piece)-1)) as
DistinctShort
FROM TheTable) as X

Roy Harvey
Beacon Falls, CT

>I have a problem. I just inherited a program that is full of holes and bugs
>and I need to do some patching. Just to note everyone, I did not set up
[quoted text clipped - 26 lines]
>
>John

Reply to this Message