How to process an array of numbers from a table to find missing ones

[graf]

Hello,
For a table having date, instrument & test number, I need to log per instrument, the lowest test, the highest test and any missing tests, for yesterday.
There are a dozen instruments, and an average of the same number for tests per instrument.

So far I have written the following (Does not work for all cases), but am sure there is a simpler way to process an array for each instrument, for yesterday:
>>>

ALTER PROCEDURE dbo.audit_sp AS
DECLARE @insrt_String nvarchar(1000)
DECLARE @prev_date nvarchar(14)
DECLARE @prev_tst nvarchar(4)
DECLARE @prev_test decimal(4)
DECLARE @curr_date nvarchar(12)
DECLARE @curr_instr nvarchar(10)
DECLARE @curr_test decimal(4)
DECLARE @curr_tst nvarchar(4)
DECLARE @next_date nvarchar(12)
DECLARE @next_instr nvarchar(10)
DECLARE @next_test decimal(4)
DECLARE @next_tst nvarchar(4)
DECLARE @min_tst nvarchar(4)
DECLARE @max_tst nvarchar(4)
DECLARE @miss_tst nvarchar(100)
DECLARE @ReturnCode int
DECLARE curs_get_curr_tst CURSOR FOR 
SELECT date, instrument, test_number FROM x_all_v WHERE date BETWEEN GETDATE() AND GETDATE()-2 ORDER BY 2,1,3 FOR READ ONLY
OPEN curs_get_curr_tst
FETCH NEXT FROM curs_get_curr_tst INTO @curr_date, @curr_instr, @curr_tst
WHILE @@FETCH_STATUS = 0
BEGIN -- for all records
FETCH NEXT FROM curs_get_curr_tst INTO @next_date, @next_instr, @next_tst
SET @min_tst =@curr_tst	
SET @prev_tst =(SELECT MAX(test_number) as prev_test FROM dbo.draeger_all_v WHERE instrument =@curr_instr AND date < '12/30/2004')
IF @curr_tst > @prev_tst +1  SET @miss_tst = @curr_tst - 1
a: IF @curr_instr <> @next_instr
BEGIN
SET @max_tst = @curr_tst
SET @insrt_String =  'INSERT INTO [dbo].[audit_log] VALUES ( '''+SUBSTRING(@curr_date,0,12)+''', ''' +@curr_instr+ ''', '+@prev_tst+', '+@min_tst+', '+@max_tst+', '''+ISNULL(@miss_tst,'')+''')' 
EXEC @ReturnCode = sp_executesql @insrt_String
SET @curr_date = @next_date
SET @curr_instr= @next_instr
SET @curr_tst = @next_tst
SET @miss_tst = ''		
SET @max_tst = ''			
END	
IF @curr_instr = @next_instr AND @next_tst = @curr_tst +1 
BEGIN			
SET @curr_date = @next_date
SET @curr_instr= @next_instr
SET @curr_tst = @next_tst
FETCH NEXT FROM curs_get_curr_tst INTO @next_date, @next_instr, @next_tst						
GOTO a;
END
IF @curr_instr = @next_instr AND @next_tst <> @curr_tst +1 
BEGIN
SET @miss_tst = @next_tst - 1
SET @curr_date = @next_date
SET @curr_instr= @next_instr
SET @curr_tst = @next_tst
FETCH NEXT FROM curs_get_curr_tst INTO @next_date, @next_instr, @next_tst				
GOTO a;
END
IF @@FETCH_STATUS <> 0 SET @max_tst = @next_tst	END						
CLOSE curs_get_curr_tst
DEALLOCATE curs_get_curr_tst
SET NOCOUNT OFF

>>>

Is there a way to write this to process an array for each instrument for yesterday? if not, is there a simpler procedure than the logic I am attempting?

Thank you,
Eyal

[Rabbit]

It's hard to decipher what your procedure is doing because you haven't given us the metadata of your tables. But from your description of what you want to do, you shouldn't need to run a procedure at all.

If you cross join a record set of distinct instruments to a record set of distinct dates, that will give you every combination of record that you're looking for. Then you take that and outer join it to your testing table to get your min, max, and missing.

All the stuff that you're doing with the cursor is probably unnecessary.

[graf]

Please find sample data that is being processed (from the cursor):

12/30/04 ARNK-0085 236
12/30/04 ARNK-0088 21
12/30/04 ARNK-0088 22
12/30/04 ARNK-0088 23
12/30/04 ARNK-0088 24
12/30/04 ARNK-0088 25
12/30/04 ARNK-0089 202
12/30/04 ARNK-0090 191
12/30/04 ARNK-0090 192
12/30/04 ARNK-0090 194
12/30/04 ARSA-0124 195
12/30/04 ARSA-0124 196
12/30/04 ARSB-0003 301
12/30/04 ARSB-0003 302
12/30/04 ARSB-0003 306
12/30/04 ARSB-0037 188
12/30/04 ARSB-0037 189

I am not sure how your suggestion would flag that, for example, instrument ARNK-0090 is missing test 193. thank you

[ck9663]

Are you going to have a different date on the table for the same instrument with the same or not the same test?

What is the PK of this table?

Is there a dimension table for every test that every instrument needs to go through?

~~ CK

[Rabbit]

My suggestion finds missing data because outer joins return a record even if there is no match.

[graf]

ck9663,
The cursor picks up data from one date only, but in the table there are records for everyday.

Each record is unique.

Each instrument's test gets the next sequence number.
It has no relation to another instrument's test sequence.

[ck9663]

The solution I am thinking will not use a cursor. And while we're at it, you should avoid cursor at all cost. It's one of the most expensive way to retrieve result set from the database.

We need the structure of x_all_v. Will it have all tests for multiple dates? Which means your PK is date, instrument and test.


~~ CK

[graf]

yes ck9663, this is a view.
each row is unique.

[ck9663]

How about this...

Say you have this table:

testdate|instrument|test
12/30/04|ARNK-0085|236
12/30/04|ARNK-0088|21
12/30/04|ARNK-0088|22
12/30/04|ARNK-0088|23
12/30/04|ARNK-0088|24
12/30/04|ARNK-0088|25
12/30/04|ARNK-0089|202
12/30/04|ARNK-0090|191
12/30/04|ARNK-0090|192
12/30/04|ARNK-0090|194
12/30/04|ARSA-0124|195
12/30/04|ARSA-0124|196
12/30/04|ARSB-0003|301
12/30/04|ARSB-0003|302
12/30/04|ARSB-0003|306
12/30/04|ARSB-0037|188
12/30/04|ARSB-0037|189

Get the first/min and last/max tests:

   select 
      testdate, instrument, min(test) as lowest_test, max(test) as highest_test 
   from x_all_v 
   group by testdate, instrument

This will be the result:

testdate|instrument|lowest_test|highest_test
12/30/04|ARNK-0085|236|236
12/30/04|ARNK-0088|21|25
12/30/04|ARNK-0089|202|202
12/30/04|ARNK-0090|191|194
12/30/04|ARSA-0124|195|196
12/30/04|ARSB-0003|301|306
12/30/04|ARSB-0037|188|189

Find the missing ones:

with summary
as
(
   select 
      testdate, instrument, min(test) as lowest_test, max(test) as highest_test 
   from x_all_v 
   group by testdate, instrument
   union all
   select testdate, instrument, lowest_test = lowest_test + 1, highest_test
   from summary
   where lowest_test < highest_test
)
select 
s.testdate, s.*instrument,s.lowest_test
from summary s
where not exists (select 1 from x_all_v x where x.testdate = s.testdate and x.instrument = s.instrument and s.lowest_test = x.test)
order by 2, 3

Here's the result:

testdate|instrument|lowest_test
12/30/04|ARNK-0090|193
12/30/04|ARSB-0003|303
12/30/04|ARSB-0003|304
12/30/04|ARSB-0003|305

Now this may work on a small data set. If it's big, you might need to break this into parts.

Happy Coding!!!

~~ CK

[graf]

final code - I just compared the source values against a table storing the relevant numbers.

thank you all for replying:

CREATE TABLE [dbo].[numbers] ([numbe_r] int)
GO
DECLARE
  @base_num INT,
  @offset   INT
SELECT @base_num = 1
WHILE (@base_num < 10000)
BEGIN
  INSERT INTO numbers VALUES( @base_num) 
  SELECT @base_num = @base_num + 1
END
GO
CREATE PROCEDURE dbo.audit_sp AS
DECLARE @curr_instr nVARCHAR(10)
DECLARE @insrt_String nvarchar(1000)
DECLARE @ReturnCode int
DECLARE curs_instr CURSOR FOR SELECT distinct instrument FROM <source view> FOR READ ONLY
OPEN curs_instr
FETCH NEXT FROM curs_instr INTO @curr_instr 
WHILE @@FETCH_STATUS = 0
	BEGIN	
--new record for each instrument yesterday	
		SET @insrt_String = 
		'INSERT INTO audit_log (dat_e, instrument, min_test, max_test) SELECT date, instrument, MIN(test_number) min_test, MAX(test_number) max_test 
		   FROM <source view> where date =GETDATE()-2 AND instrument = '''+@curr_instr+''' GROUP BY date, instrument;'
		EXEC @ReturnCode = sp_executesql @insrt_String				  
--update record with previous test
		SET @insrt_String = 
		'UPDATE audit_log SET prev_test =(SELECT MAX(test_number) FROM <source view> where date <GETDATE()-2 and instrument = '''+@curr_instr+''') 
		WHERE dat_e =GETDATE()-2 and instrument = '''+@curr_instr+''';'
		EXEC @ReturnCode = sp_executesql @insrt_String				  
--update record with missing tests
		SET @insrt_String = 
		'UPDATE audit_log SET missed_tests = (SELECT numbe_r + '' '' numbe_r FROM numbers 
		   WHERE numbe_r NOT IN (select test_number from <source view> where date BETWEEN 
		   (SELECT MAX(date) FROM dbo.draeger_all_v  where instrument = '''+@curr_instr+''' AND date <GETDATE()-2 AND test_number IS NOT NULL)
		   AND GETDATE()-2
		   and instrument = '''+@curr_instr+''') 
		   AND numbe_r between (SELECT MAX(test_number) from <source view>  where date <GETDATE()-2 and instrument = '''+@curr_instr+''') 
						AND (SELECT MAX(test_number) from <source view>  where date =GETDATE()-2 and instrument = '''+@curr_instr+''') 
							 FOR XML PATH(''''))
		WHERE dat_e =GETDATE()-2 and instrument = '''+@curr_instr+''';'
		EXEC @ReturnCode = sp_executesql @insrt_String
		FETCH NEXT FROM curs_instr INTO @curr_instr 
	END						
CLOSE curs_instr
DEALLOCATE curs_instr

[ck9663]

There are ways to do this without using a CURSOR. Just include the instrument as one of returned and GROUP BY expression and it should give you the same result without using a CURSOR. You should use CURSORs as the last resort for any query.

Happy Coding!!!


~~ CK