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
10 1716
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.
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
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
My suggestion finds missing data because outer joins return a record even if there is no match.
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.
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
yes ck9663, this is a view.
each row is unique.
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
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
-
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
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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).
...
|
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 ...
|
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...
|
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...
|
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
------------ ...
|
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)....
|
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.
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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...
|
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...
| |