Help
in [Programming SQL]
|
From: John Wright on 21 Jul 2008 15:43 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
From: Roy Harvey (SQL Server MVP) on 21 Jul 2008 17:24 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 On Mon, 21 Jul 2008 13:43:25 -0600, "John Wright" <riley_wright(a)hotmail.com> wrote: >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 >
|
Pages: 1 Prev: select count of rows per group Next: Need Ideas for SQL Query |