finding number sequence gap problem 1

[h0gg1e]

I'm having a difficult time figuring out how I can find out the first gap in a numeric col. I tried the faq and I just can't get it to work with my data. I have a table set up like this

schedID (bigint id), lineID (varchar), schedStatus (varchar), prodSeq (int)

I need to find the first gap in prodSeq (which is NOT the pkey), but I need to find it based on a certain status and line... IE:

schedID lineID schedStatus prodSeq
1000 W99 READY 1
1001 W99 COMPLETE 1
1002 W01 READY 1
1003 W99 READY 2
1004 W99 READY 5
1005 W99 COMPELTE 3

so I want to pull the first numeric "hole" for line W99 with a status of 'READY'
so the result should be 3

or if I want to pull for line W99 status COMPLETE
it should return 2

or line W01 status READY
should return 2

finally line W01 status COMPLETE
should return 1 (because there is nothing there)

I've been playing with a couple different queries and I just can't get them to work...but here they are...

SELECT a.ProductionSequence AS GapAfterSeq
FROM tblProductionSchedules a
WHERE NOT EXISTS( SELECT * FROM tblProductionSchedules b
WHERE b.ProductionSequence = a.ProductionSequence + 1 ) and
a.ProductionSequence < ( SELECT MAX( ProductionSequence ) FROM tblProductionSchedules )
AND a.CompletedStatus = 'READY' and a.ProductionLineID='W99'


****I know my real table has different names than my above examples...the examples are just smaller and easier to read***

thanks for any help

 

[rac2]

SELECT a.ProductionSequence AS GapAfterSeq
FROM tblProductionSchedules a
WHERE NOT EXISTS( SELECT *
                  FROM tblProductionSchedules b
                  WHERE b.ProductionSequence  = a.ProductionSequence + 1
                 )
  and a.ProductionSequence < (
                              SELECT MAX( ProductionSequence )
                              FROM tblProductionSchedules
                             )
  AND a.CompletedStatus = 'READY'
  and a.ProductionLineID='W99'

Lets break it down.

SELECT MAX( ProductionSequence )
FROM tblProductionSchedules

That yields the highest ProductionSequence value from the tblProductionSchedules table. Which would be 5 from your example.

Is it possible that we really want

SELECT MAX( ProductionSequence )
FROM tblProductionSchedules 
WHERE CompletedStatus = 'READY'
  AND ProductionLineID='W99'

That yields 5 also, but if you change the status and line id it will give other values. The previous query always gives 5. This subquery tells us when to stop looking for succeeding values. If we dont find a missing number before that, there aint any. And it is not a correlated subquery, so there is no need to reference table a. Lets just call that value @hiSeqReadyW99 for the moment.

SELECT *
FROM tblProductionSchedules b
WHERE b.ProductionSequence  = a.ProductionSequence + 1

Here it is as if we are working through table a one-row-at-a-time, each row gives us a value for a.ProductionSequence. The criterion is that we find the succeeding value. For example if a.ProductionSequence is 1 then do we find a 2? This is what EXISTS means. In this case we want to know when there is not a successor.

This brings us to

SELECT a.ProductionSequence AS GapAfterSeq
FROM tblProductionSchedules a
WHERE NOT EXISTS (
                  SELECT *
                  FROM tblProductionSchedules b
                  WHERE b.ProductionSequence  = a.ProductionSequence + 1
                  )
  AND a.ProductionSequence < @hiSeqReadyW99
  AND CompletedStatus = 'READY'
  AND ProductionLineID='W99'

These last three conditions ensure that we are only looking at (READY, W99)s and that we are not looking at the highest numbered one.

Now substitute that subquery for @hiSeqReadyW99 and we are home free.

SELECT a.ProductionSequence AS GapAfterSeq
FROM tblProductionSchedules a
WHERE NOT EXISTS (
                  SELECT *
                  FROM tblProductionSchedules b
                  WHERE b.ProductionSequence  = a.ProductionSequence + 1
                  )
  AND a.ProductionSequence < (
                               SELECT MAX( ProductionSequence )
                               FROM tblProductionSchedules 
                               WHERE CompletedStatus = 'READY'
                               AND ProductionLineID='W99'
                              )
  AND CompletedStatus = 'READY'
  AND ProductionLineID='W99'

At least that is how I would approach the problem.

I cant help but notice that somewhere along the way there we discovered the meaning of existence!

 

[ESquared]

SELECT A.lineID, A.schedStatus, MIN(A.ProdSeq)
FROM tblProductionSchedules A LEFT JOIN tblProductionSchedules A2 ON A.ProdSeq +1 = A2.ProdSeq
   AND A.schedStatus = A2.schedStatus
   AND A.lineID = A2.lineID
WHERE A2.ProdSeq IS NULL
   AND A.lineID = 'W99' AND A.schedStatus = 'COMPLETE'
   AND A2.lineID = 'W99' AND A2.schedStatus = 'COMPLETE' --You can try this because it can speed up queries sometimes
GROUP BY A.lineID, A.schedStatus

 

[ESquared]

Well, I forgot two things:

You need the number of the hole, not the number of the last one. Just add one to the result to get it.

You don't care to see anything at all when there is no hole, right? So you'll need another join (I sure prefer joins to WHERE clauses with a subquery ).

SELECT Q.*, FROM 
   (SELECT A.lineID, A.schedStatus, MIN(A.ProdSeq) AS LastGood
      FROM tblProductionSchedules A LEFT JOIN tblProductionSchedules A2 ON A.ProdSeq +1 = A2.ProdSeq
         AND A.schedStatus = A2.schedStatus
         AND A.lineID = A2.lineID
      WHERE A2.ProdSeq IS NULL
         AND A.lineID = 'W99' AND A.schedStatus = 'COMPLETE'
         AND A2.lineID = 'W99' AND A2.schedStatus = 'COMPLETE' --You can try this because it can speed up queries sometimes
      GROUP BY A.lineID, A.schedStatus
   ) AS Q LEFT JOIN
   (SELECT B.lineID, B.schedStatus, MAX(B.ProdSeq)
      FROM tblProductionSchedules B
      GROUP BY B.lineID, B.schedStatus
   ) AS X ON Q.lineID = X.lineID AND A.ProdSeq = X.ProdSeq AND Q.schedStatus = X.schedStatus
   WHERE X.ProdSeq IS NULL

It looks messy but I bet it runs faster than using a row-specific subquery in a WHERE clause. Unless I am wrong, and I'd sure like to know if I am, they have to run once for each row. Derived tables definitely only have to be built once...

 

[ESquared]

In bed last night I figured it out! In fact, I had already gotten out of bed once to come post and I wasn't going to do it again.

I think this does the trick:

   SELECT A.lineID, A.schedStatus, MIN(A.ProdSeq+1) AS FirstGap, MAX(A.ProdSeq) AS LastSeq
      FROM tblProductionSchedules A LEFT JOIN tblProductionSchedules A2 ON A.ProdSeq+1 = A2.ProdSeq
         AND A.schedStatus = A2.schedStatus
         AND A.lineID = A2.lineID
      WHERE A2.ProdSeq IS NULL
         AND A.lineID = 'W99' AND A.schedStatus = 'COMPLETE'
         AND A2.lineID = 'W99' AND A2.schedStatus = 'COMPLETE' --You can try this because it can speed up queries sometimes
      GROUP BY A.lineID, A.schedStatus
      HAVING FirstGap < LastSeq + 1

 

[ESquared]

Yes, I blew it, you can't use an alias in the HAVING clause. I thought you could because it is after the aggregation. Anyway, it can be fixed:

   SELECT A.lineID, A.schedStatus, MIN(A.ProdSeq+1) AS FirstGap, MAX(A.ProdSeq+1) AS LastSeq
      FROM tblProductionSchedules A LEFT JOIN tblProductionSchedules A2 ON A.ProdSeq+1 = A2.ProdSeq
         AND A.schedStatus = A2.schedStatus
         AND A.lineID = A2.lineID
      WHERE A2.ProdSeq IS NULL
         AND A.lineID = 'W99' AND A.schedStatus = 'COMPLETE'
         AND A2.lineID = 'W99' AND A2.schedStatus = 'COMPLETE' --You can try this because it can speed up queries sometimes
      GROUP BY A.lineID, A.schedStatus
      HAVING MIN(A.ProdSeq+1), MAX(A.ProdSeq+1)

 

[h0gg1e]

all the above code still returns nothing, I've been messing around with them and I still can't get anything.

so far they all return 0 records back, I've gone through the data and verified that is all correct and if I filter down by line='W99' and status='COMPLETE' it should return 2, but I just get nothing at all returned.

I'm totally lost on this one ???

thanks for the help thus far

sample data, which is the same as my real data
schedID lineID schedStatus prodSeq
1000 W99 READY 1
1001 W99 COMPLETE 1
1002 W01 READY 1
1003 W99 READY 2
1004 W99 READY 5
1005 W99 COMPELTE 3

 

[ESquared]

You have COMPLETE misspelled.

 

[ESquared]

You're right, I had more mistakes. When I tried it here I simplified it. So much for not testing my answers, I apologize!

Fix #1: less than, not comma

Fix #2: get rid of the last WHERE condition.
You can't have conditions here on A2 because that does not intersect with the IS NULL earlier. I was being too clever for my britches!

Edit #1: you can get rid of the max phrase from the select because you don't need this value, the HAVING clause doesn't need it.

SELECT A.lineID,
      A.schedStatus,
      MIN(A.ProdSeq+1) AS FirstGap[b][COLOR=red],
      MAX(A.ProdSeq+1) AS LastSeq -- Deleted[/color][/b]
      FROM tblProductionSchedules A LEFT JOIN tblProductionSchedules A2 ON A.ProdSeq+1 = A2.ProdSeq
         AND A.schedStatus = A2.schedStatus
         AND A.lineID = A2.lineID
      WHERE A2.ProdSeq IS NULL
         AND A.lineID = 'W99' AND A.schedStatus = 'COMPLETE'
[b][COLOR=red]-- Line Deleted: AND A2.lineID = 'W99' AND A2.schedStatus = 'COMPLETE'[/color red][/b]
      GROUP BY A.lineID, A.schedStatus
      HAVING MIN(A.ProdSeq+1) [b][COLOR=red]<[/color][/b] MAX(A.ProdSeq+1)

 

[h0gg1e]

AH! that last one worked!!! thanks I can finally see what's its trying to do and it makes sense...

sorry about the 'COMPLETE' thing...Iwas actually testing against 'READY' but either way it works...

thanks again

a star coming right up

 

[ESquared]

You're welcome! I enjoyed this problem, it obviously stretched me to come up with the solution.

You can incidentally remove the +1s from the last line.