March 11, 2003 at 7:07 pm
Recently I was asked to provide some statistics on a document management system we have.
There is an audit table to record activities such as creation, deletion, etc, of scanned documents
but no unique identifier for such a batch scan...
My TSQL question:
"Is there a set based way of getting the average time where a batch is created and then deleted with this table?"
eg for a Batch_ID, Action=0 (creation) to the Batch deletion: Action=2
You'll see that the BATCH_ID (batch name) can be re-used which makes life difficult in TSQL.
Cursor suggestions are welcome as what I have done so far runs like a dog.
The table create and some real-life data follow below
CREATE TABLE BATCHAUDIT (
ACTION int,
BATCH_ID varchar (12)
ACTIVITY_DATE datetime
) ON PRIMARY
GO
ACTION,BATCH_ID,ACTIVITY_DATE
0,001,2001-08-27 13:53:18.947
2,001,2001-08-27 14:06:21.570
0,001,2001-08-27 14:21:11.540
2,001,2001-08-27 14:21:43.600
0,01/2001,2001-07-30 11:23:34.850
2,01/2001,2001-07-30 11:31:48.117
0,1518,2001-05-23 11:58:13.960
2,1518,2001-05-23 12:34:08.913
0,1825,2001-09-27 12:07:21.107
2,1825,2001-09-27 12:15:09.233
0,2,2002-05-09 10:48:21.520
2,2,2002-05-09 10:54:10.287
0,260802,2002-09-10 09:05:07.560
2,260802,2002-11-08 13:00:19.000
0,28/2001,2002-02-08 09:31:42.907
2,28/2001,2002-02-08 14:43:19.547
0,4RACHEL,2001-02-22 16:18:14.780
2,4RACHEL,2001-02-22 16:30:01.950
0,4RACHEL,2001-02-22 16:38:13.467
2,4RACHEL,2001-02-22 16:39:06.013
0,504190000017,2001-10-11 09:52:16.763
2,504190000017,2001-10-17 15:00:12.640
The systems fine with no users loggged in. Can we keep it that way ?br>
March 12, 2003 at 1:49 am
Having a go at this ...
SELECT S.Batch_ID,
DATEDIFF(ss, E.ACTIVITY_DATE, S.ACTIVITY_DATE)
FROM (SELECT BATCH_ID, ACTIVITY_DATE
FROM BATCHAUDIT WHERE ACTION=0) S
INNER JOIN
(SELECT BATCH_ID, ACTIVITY_DATE
FROM BATCHAUDIT WHERE ACTION=2) E
ON S.BATCH_ID = E.BATCH_ID
WHERE NOT EXISTS
(SELECT 1 FROM BATCHAUDIT T
WHERE T.BATCH_ID = S.BATCH_ID
AND T.ACTION = 0
AND T.ACTIVITY_DATE > S.ACTIVITY_DATE
AND T.ACTIVITY_DATE < E.ACTIVITY_DATE)
March 17, 2003 at 4:19 pm
Thanks for the effort but I am getting the developer to re-write. Sorry it took me a while to get back.
The systems fine with no users loggged in. Can we keep it that way ?br>
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply