Sequence checking -impossible?

[Muddy_Funster]

Hi all,

I look after a database for a third party program. This program auto updates a sequence of job refference numbers as soon as a transaction is completed.  These jobs are all processed in batches of 20, but the sequence numbering doesn't show that directly. The sequenced field (which I will call ID for arguments sake) is stored in different tables depending on it's status, as well as in a "master" table, so existing in 2 places at once as it is.  The "master" table cross reffrences a batch table which alocates the ID to a Batch generated from the 1st ID in the batch and matched with a Number of ID's in Batch field (normaly 20, supposed to be always 20 but as it's user input can get quite interesting at times)  I have no authority to manipulate the table structures or write any data to these tables, but I can create new ones if the need arrises.  There is an ID list report within the 3rd party program is generated by listing all the ID's that have not been processed.  However this then includes any Batches that have had the 1st of the 20 ID's processed but none of the other ones have been strated yet.

 

I have been asked if there is any way I can check the sequencing of ID's within a Batch to establish if they are missing because of an error (which happens far too often - hence the problem), or if it is the end ID's in a batch that have yet to be processed.  Then, if this can indeed be done Is it possable to make it clever enough to notice if the last ID in a batch is missing by error and not just because it has not been completed.

 

Let me know what you think guys.

[fenway]

Sorry, I don't follow.

[Muddy_Funster]

Ok, I have a sequence of ID's - for example sake say 100001 through 104320.

These ID's are matched against a batch number taken from the first ID in that batch and attached to as many ID's as the user selects (supposed to be 20, but mistakes are made) so say 100001 - 100020 have batch number 100001, 100021 - 100040 have batch number 100021 etc.  I bring up the user error because it meanse that I can't use an absolout value of 20 in any working.

 

Now what happens is that each transaction is tied to an ID. once any ID from a batch is registered as complete the whole batch is then flaged as "In use".  However through system problems and user issues not all transactions register as comlete, so some ID's go missing.  This causes problems with billing and the such.  To combat this there is a report that has been created to show all the ID's that have a batch flaged as "in use" and have not had a completed transaction.  The problem that I am trying to resolve in the first instance is that if ID 100021 has had the most recent completed transaction, the report still shows 100022 through 200040 as being "problem" ID's. I was wondering if there was a way to check for "missing" transactions whilst ignoring what is efectivly pending ID's from the same batch. So if from batch 100001 - 100020 the following is true:

100001 - complete

100002 - complete

100003 - not complete

100004 - complete

100005 - complete

100006 -|

...          | - not used yet so obviously not going to be complete

100020 -|

 

I am looking for a way to identify that 100006 through 100020 are "pending" and not have them included in the report whilst still showing 100003 as being a potential loss of revinue.

As it stands the vast majority of the results in the report are effectivly false, cloging up the true potential issues.

 

That help at all?

[fenway]

If I understand correctly, you know which IDs are associated with a given batch -- always.

 

You simply want to produce a report showing all IDs, and whether they are complete or not.

 

For complete, that's easy.

 

For incomplete, that's not so easy.

 

In your example, 100060 - 10020 you say are "obviously not used" -- why? Because there are no such IDs?

[Muddy_Funster]

no, I said "are not used yet, so obviously can't be complete". 

 

I was trying to figure out something along the lines of

SELECT master.id FROM recieved, master

LEFT JOIN batch ON master.id = batch.batch_id

WHERE

id IS NOT BETWEEN (recieved.MAX(id) AND batch_id + (Count(batch_id)-1 Group BY batch_id) FOR EACH Batch

AND WHERE id IS IN master AND IS NOT IN recieved

It's clearly not code, or even psudo(it's all on my office comp, so I can get what even tomorrow) but it sould help explain how I was thinking about making it work. For some reason I just can't get my head around NOT BETWEEN part. 

[Muddy_Funster]

Well, I have got round the first part of the problem by using a cursor and two memory tables, which needless to say takes some time to run through.  Would still like to know if there is some better way of doing this.

[fenway]

How do you know that they're not used -- like they're not in the table?

[Muddy_Funster]

because each ID relates to a pre-assigned phisical entity bearing that ID number - something like a pre printed invoice book.  I can establish which pages(ID's) have not been used.  Although for this exorcise I am having to assume that anthing that is after the last ID recieved as a transaction and before the last ID in the Batch is unused, whether it is or not.  Which was why I was asking about making it a smarter check, in that it would look for a count value or a date between last ID and today and if more than 5 Days (for arguments sake) flag it up, but the query just now takes about two minutes, so I'm not for complicating it further untill I can get a way to speed it up.

[fenway]

I guess I'm missing what the hard part seems to be.  If you assign N transactions to a batch, and store this somewhere, a simple LEFT JOIN should suffice, no?

[Muddy_Funster]

well this is what I used to make it work (is actualy in MS-SQL but since no one visits that topic very often and I can, for the most part, switch it between MySQL anyway I posted in here)

DECLARE @padList TABLE (batchID int, maxSheet int, lastSheet int, lastActual int)
DECLARE @badSheets TABLE (ID int, batchMax int)
DECLARE @sheet int
DECLARE @batch int
DECLARE @endBatch int
DECLARE @batchIndex int
INSERT INTO @padlist SELECT  tempsheet.padID , max(sheetID), (tempSheet.PadID + 19), (tempSheet.PadID + (count(tempSheet.PadID)-2))
FROM jim.dbo.sheet as tempSheet 
LEFT JOIN jim.dbo.Pad AS tempPad
  ON (tempSheet.padID = tempPad.padID)
RIGHT JOIN documentDirector2.dbo.assignedpattern as tempPattern 
  ON (tempPattern.TUID = convert(varchar, tempSheet.SheetID)) 
WHERE tempPattern.penstrokesReceivedDate IS NULL
GROUP BY tempSheet.sheetID, tempSheet.padID

DECLARE bad_batch CURSOR FOR
SELECT DISTINCT batchID 
FROM @padList 
WHERE batchID > 100000
ORDER BY batchID ASC

OPEN bad_batch
FETCH NEXT FROM bad_batch
INTO @batchIndex

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @batch = lastSheet FROM @padList WHERE batchID = @batchIndex order by batchID desc
SELECT @sheet = maxSheet FROM @padList WHERE batchID = @batchIndex order by batchID desc
WHILE (@sheet <= @batch)BEGIN

INSERT INTO @badsheets(ID, batchMax) VALUES(@sheet, @batch)
SET @sheet = (@sheet + 1)
END
Fetch Next FROM bad_batch INTO @batchIndex
END
CLOSE bad_batch
DEALLOCATE bad_batch

SELECT  sheetID AS Sheet, PadPrefix, 
penID , penname,
CASE WHEN apattern.PenStrokesReceivedDate IS NULL THEN 'Never Received' ELSE 'Recieved Incomplete' END AS Status
FROM jim.dbo.sheet as sheet 
LEFT JOIN jim.dbo.Pad AS Pads
  ON (sheet.padID = Pads.padID)
RIGHT JOIN documentDirector2.dbo.assignedpattern as apattern 
  ON (apattern.TUID = convert(varchar, sheet.SheetID)) 
WHERE TUID > '10000' 

AND sheetID NOT IN (SELECT SheetID FROM jim.dbo.files)
AND SheetID IN (
SELECT sheetID FROM jim.dbo.sheet as tempSheet
LEFT OUTER JOIN 
(SELECT max(sheetID) as maxsheet from jim.dbo.sheet
LEFT JOIN documentDirector2.dbo.assignedpattern as tempPattern 
  ON (tempPattern.TUID = convert(varchar, jim.dbo.sheet.SheetID)) 

GROUP BY padID) as sheetMAX
ON (tempSheet.sheetID = sheetMax.maxsheet)

RIGHT JOIN (SELECT (padID + (Count(padID)-1)) as padEnd FROM jim.dbo.sheet GROUP BY padID) padMax
ON (tempSheet.sheetID = padMax.padEnd)

Where tempSheet.sheetID > 100000 AND tempSheet.sheetID NOT IN (SELECT ID FROM @badSheets)
)

ORDER BY PadPrefix, penname, TUID ASC

 

I'll probably get shot if management find this on here, but I figure it's the best way for you to get a feel for it.

[kickstart]

Hi

 

How are batch ids recorded.

 

Does each job reference record contain the batch id (which for the first one in each batch will be the same as the job reference id)?

 

Is there a status on both the batch and on the job reference?

 

All th ebest

 

Keith

[fenway]

Hi

 

How are batch ids recorded.

 

Does each job reference record contain the batch id (which for the first one in each batch will be the same as the job reference id)?

 

Is there a status on both the batch and on the job reference?

 

All th ebest

 

Keith

That was sort of my question, too.

[Muddy_Funster]

There is a table where each ID is attached to it's batch (in this case jim.dbo.pad) and yes, the batch is assigned the number from the first record in it.

 

There is no explicit status for whither the ID or batch is active or not, only whither it exists in one table or another so I can only get it by inference.  Batch is never flagged one way or another, it's all done by the existence (or not) of ID as before.

 

jim.dbo.pad:

padID (int) (PK)

sheetID (varchar 30)

PadPrefix (varchar 20)

 

SheetID is the ID that I have been reffering throught