473,322 Members | 1,188 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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

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
10 1716
Rabbit
12,516 Expert Mod 8TB
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
graf
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
2,878 Expert 2GB
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
12,516 Expert Mod 8TB
My suggestion finds missing data because outer joins return a record even if there is no match.
Oct 31 '13 #5
graf
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
2,878 Expert 2GB
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
graf
5
yes ck9663, this is a view.
each row is unique.
Nov 1 '13 #8
ck9663
2,878 Expert 2GB
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
graf
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
2,878 Expert 2GB
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

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

Similar topics

2
by: Mansoor Azam | last post by:
I have 2 tables say table1 and table2 with the same structure. Each record is identified by a field 'SerialNo'. Now there should be a total of 500000 records in both tables with serialno from 1 to...
4
by: SQLJunkie | last post by:
Here is an issue that has me stumped for the past few days. I have a table called MerchTran. Among various columns, the relevant columns for this issue are: FileDate datetime , SourceTable...
1
by: KC | last post by:
Hello, I am using Access 2002. WinXP, Template from MS called Orders Mgmt DB. I have tweaked this DB to work for our small co. It has worked pretty well up until I made the mistake of deleting...
17
by: Justin Emlay | last post by:
I'm hopping someone can help me out on a payroll project I need to implement. To start we are dealing with payroll periods. So we are dealing with an exact 10 days (Monday - Friday, 2 weeks). ...
67
by: PC Datasheet | last post by:
Transaction data is given with date ranges: Beginning End 4/1/06 4/4/06 4/7/06 4/11/06 4/14/06 4/17/06 4/18/06 4/21/06 426/06 ...
1
by: anonymowho | last post by:
this is what I am trying to do. I have 2 tables in 2 different databases in 2 different directories. so my first table is called CUSTOMER in the database PROD and in the directory...
13
newnewbie
by: newnewbie | last post by:
I have an Access database that I upload a data extract intoto daily. I want to create a query that will give me a list of dates that no data can be found for. E.g. there are no records created on the...
1
by: Byomokesh | last post by:
Hi All, This XML file is 2 errors. My table is 3 columns. One is extra column and another is missing one columne. How i will find those error through perl script. XML file ------------ ...
20
by: kpfunf | last post by:
Hey all, I have a table of receipts used (Table Name: receipts, Field Name: receiptNumber) and a table with receipts issued (Table Name: Receipts Batches, Field Names: BatchID, BegRng, EndRng)....
8
by: freedemi | last post by:
Hi, I ll try to find a way to find the missing number from range of numbers from a specific field and show this number with msgbox. Any ideas? Thanks in advance.
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.