By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,969 Members | 1,735 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,969 IT Pros & Developers. It's quick & easy.

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

P: 5
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:
>>>
Expand|Select|Wrap|Line Numbers
  1. ALTER PROCEDURE dbo.audit_sp AS
  2. DECLARE @insrt_String nvarchar(1000)
  3. DECLARE @prev_date nvarchar(14)
  4. DECLARE @prev_tst nvarchar(4)
  5. DECLARE @prev_test decimal(4)
  6. DECLARE @curr_date nvarchar(12)
  7. DECLARE @curr_instr nvarchar(10)
  8. DECLARE @curr_test decimal(4)
  9. DECLARE @curr_tst nvarchar(4)
  10. DECLARE @next_date nvarchar(12)
  11. DECLARE @next_instr nvarchar(10)
  12. DECLARE @next_test decimal(4)
  13. DECLARE @next_tst nvarchar(4)
  14. DECLARE @min_tst nvarchar(4)
  15. DECLARE @max_tst nvarchar(4)
  16. DECLARE @miss_tst nvarchar(100)
  17. DECLARE @ReturnCode int
  18. DECLARE curs_get_curr_tst CURSOR FOR 
  19. SELECT date, instrument, test_number FROM x_all_v WHERE date BETWEEN GETDATE() AND GETDATE()-2 ORDER BY 2,1,3 FOR READ ONLY
  20. OPEN curs_get_curr_tst
  21. FETCH NEXT FROM curs_get_curr_tst INTO @curr_date, @curr_instr, @curr_tst
  22. WHILE @@FETCH_STATUS = 0
  23. BEGIN -- for all records
  24. FETCH NEXT FROM curs_get_curr_tst INTO @next_date, @next_instr, @next_tst
  25. SET @min_tst =@curr_tst    
  26. SET @prev_tst =(SELECT MAX(test_number) as prev_test FROM dbo.draeger_all_v WHERE instrument =@curr_instr AND date < '12/30/2004')
  27. IF @curr_tst > @prev_tst +1  SET @miss_tst = @curr_tst - 1
  28. a: IF @curr_instr <> @next_instr
  29. BEGIN
  30. SET @max_tst = @curr_tst
  31. 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,'')+''')' 
  32. EXEC @ReturnCode = sp_executesql @insrt_String
  33. SET @curr_date = @next_date
  34. SET @curr_instr= @next_instr
  35. SET @curr_tst = @next_tst
  36. SET @miss_tst = ''        
  37. SET @max_tst = ''            
  38. END    
  39. IF @curr_instr = @next_instr AND @next_tst = @curr_tst +1 
  40. BEGIN            
  41. SET @curr_date = @next_date
  42. SET @curr_instr= @next_instr
  43. SET @curr_tst = @next_tst
  44. FETCH NEXT FROM curs_get_curr_tst INTO @next_date, @next_instr, @next_tst                        
  45. GOTO a;
  46. END
  47. IF @curr_instr = @next_instr AND @next_tst <> @curr_tst +1 
  48. BEGIN
  49. SET @miss_tst = @next_tst - 1
  50. SET @curr_date = @next_date
  51. SET @curr_instr= @next_instr
  52. SET @curr_tst = @next_tst
  53. FETCH NEXT FROM curs_get_curr_tst INTO @next_date, @next_instr, @next_tst                
  54. GOTO a;
  55. END
  56. IF @@FETCH_STATUS <> 0 SET @max_tst = @next_tst    END                        
  57. CLOSE curs_get_curr_tst
  58. DEALLOCATE curs_get_curr_tst
  59. 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
Oct 30 '13 #1
Share this Question
Share on Google+
10 Replies


Rabbit
Expert Mod 10K+
P: 12,430
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.
Oct 30 '13 #2

P: 5
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
Oct 31 '13 #3

ck9663
Expert 2.5K+
P: 2,878
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
Oct 31 '13 #4

Rabbit
Expert Mod 10K+
P: 12,430
My suggestion finds missing data because outer joins return a record even if there is no match.
Oct 31 '13 #5

P: 5
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.
Oct 31 '13 #6

ck9663
Expert 2.5K+
P: 2,878
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
Oct 31 '13 #7

P: 5
yes ck9663, this is a view.
each row is unique.
Nov 1 '13 #8

ck9663
Expert 2.5K+
P: 2,878
How about this...

Say you have this table:
Expand|Select|Wrap|Line Numbers
  1. testdate|instrument|test
  2. 12/30/04|ARNK-0085|236
  3. 12/30/04|ARNK-0088|21
  4. 12/30/04|ARNK-0088|22
  5. 12/30/04|ARNK-0088|23
  6. 12/30/04|ARNK-0088|24
  7. 12/30/04|ARNK-0088|25
  8. 12/30/04|ARNK-0089|202
  9. 12/30/04|ARNK-0090|191
  10. 12/30/04|ARNK-0090|192
  11. 12/30/04|ARNK-0090|194
  12. 12/30/04|ARSA-0124|195
  13. 12/30/04|ARSA-0124|196
  14. 12/30/04|ARSB-0003|301
  15. 12/30/04|ARSB-0003|302
  16. 12/30/04|ARSB-0003|306
  17. 12/30/04|ARSB-0037|188
  18. 12/30/04|ARSB-0037|189
  19.  
Get the first/min and last/max tests:
Expand|Select|Wrap|Line Numbers
  1.    select 
  2.       testdate, instrument, min(test) as lowest_test, max(test) as highest_test 
  3.    from x_all_v 
  4.    group by testdate, instrument
  5.  
This will be the result:

Expand|Select|Wrap|Line Numbers
  1. testdate|instrument|lowest_test|highest_test
  2. 12/30/04|ARNK-0085|236|236
  3. 12/30/04|ARNK-0088|21|25
  4. 12/30/04|ARNK-0089|202|202
  5. 12/30/04|ARNK-0090|191|194
  6. 12/30/04|ARSA-0124|195|196
  7. 12/30/04|ARSB-0003|301|306
  8. 12/30/04|ARSB-0037|188|189
  9.  
Find the missing ones:
Expand|Select|Wrap|Line Numbers
  1. with summary
  2. as
  3. (
  4.    select 
  5.       testdate, instrument, min(test) as lowest_test, max(test) as highest_test 
  6.    from x_all_v 
  7.    group by testdate, instrument
  8.    union all
  9.    select testdate, instrument, lowest_test = lowest_test + 1, highest_test
  10.    from summary
  11.    where lowest_test < highest_test
  12. )
  13. select 
  14. s.testdate, s.*instrument,s.lowest_test
  15. from summary s
  16. 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)
  17. order by 2, 3
  18.  
Here's the result:
Expand|Select|Wrap|Line Numbers
  1. testdate|instrument|lowest_test
  2. 12/30/04|ARNK-0090|193
  3. 12/30/04|ARSB-0003|303
  4. 12/30/04|ARSB-0003|304
  5. 12/30/04|ARSB-0003|305
  6.  
Now this may work on a small data set. If it's big, you might need to break this into parts.

Happy Coding!!!

~~ CK
Nov 5 '13 #9

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

thank you all for replying:

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE [dbo].[numbers] ([numbe_r] int)
  2. GO
  3. DECLARE
  4.   @base_num INT,
  5.   @offset   INT
  6. SELECT @base_num = 1
  7. WHILE (@base_num < 10000)
  8. BEGIN
  9.   INSERT INTO numbers VALUES( @base_num) 
  10.   SELECT @base_num = @base_num + 1
  11. END
  12. GO
  13. CREATE PROCEDURE dbo.audit_sp AS
  14. DECLARE @curr_instr nVARCHAR(10)
  15. DECLARE @insrt_String nvarchar(1000)
  16. DECLARE @ReturnCode int
  17. DECLARE curs_instr CURSOR FOR SELECT distinct instrument FROM <source view> FOR READ ONLY
  18. OPEN curs_instr
  19. FETCH NEXT FROM curs_instr INTO @curr_instr 
  20. WHILE @@FETCH_STATUS = 0
  21.     BEGIN    
  22. --new record for each instrument yesterday    
  23.         SET @insrt_String = 
  24.         'INSERT INTO audit_log (dat_e, instrument, min_test, max_test) SELECT date, instrument, MIN(test_number) min_test, MAX(test_number) max_test 
  25.            FROM <source view> where date =GETDATE()-2 AND instrument = '''+@curr_instr+''' GROUP BY date, instrument;'
  26.         EXEC @ReturnCode = sp_executesql @insrt_String                  
  27. --update record with previous test
  28.         SET @insrt_String = 
  29.         'UPDATE audit_log SET prev_test =(SELECT MAX(test_number) FROM <source view> where date <GETDATE()-2 and instrument = '''+@curr_instr+''') 
  30.         WHERE dat_e =GETDATE()-2 and instrument = '''+@curr_instr+''';'
  31.         EXEC @ReturnCode = sp_executesql @insrt_String                  
  32. --update record with missing tests
  33.         SET @insrt_String = 
  34.         'UPDATE audit_log SET missed_tests = (SELECT numbe_r + '' '' numbe_r FROM numbers 
  35.            WHERE numbe_r NOT IN (select test_number from <source view> where date BETWEEN 
  36.            (SELECT MAX(date) FROM dbo.draeger_all_v  where instrument = '''+@curr_instr+''' AND date <GETDATE()-2 AND test_number IS NOT NULL)
  37.            AND GETDATE()-2
  38.            and instrument = '''+@curr_instr+''') 
  39.            AND numbe_r between (SELECT MAX(test_number) from <source view>  where date <GETDATE()-2 and instrument = '''+@curr_instr+''') 
  40.                         AND (SELECT MAX(test_number) from <source view>  where date =GETDATE()-2 and instrument = '''+@curr_instr+''') 
  41.                              FOR XML PATH(''''))
  42.         WHERE dat_e =GETDATE()-2 and instrument = '''+@curr_instr+''';'
  43.         EXEC @ReturnCode = sp_executesql @insrt_String
  44.         FETCH NEXT FROM curs_instr INTO @curr_instr 
  45.     END                        
  46. CLOSE curs_instr
  47. DEALLOCATE curs_instr
  48.  
Nov 14 '13 #10

ck9663
Expert 2.5K+
P: 2,878
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
Nov 19 '13 #11

Post your reply

Sign in to post your reply or Sign up for a free account.