I have a difficult(to me) query to write and I am hpoing to get some help
my scenario is this
Table1 contains daily billing data to import into another
product(billing).
So when I process table1 I need to perform several checks, such as that
item has not been entered before. All items that are succesfully imported
get recorded into an audit table for reference and as a further check that
none of them will be imported again.
If the current record from table1 fails any check, then I write a copy to
an Error table.
Now, my problem is best explained using this scenario.
I run the processing for table1, and the records for customer X fail,
because customer X does not exist in my destination product, however I
then create customer X and re-process that date range worth of data.
Now even though CustomerX records exist in the error table, I still want
to audit it as successfull, because this time it worked. I have thought
of writing to the audit table every time I write to my destination
product, but for 1000's of records a day, thats a lot of rights. The best
way would be to perform a query that checks my table1 and the error table
and creates the audit records that a)don't exist in the error table and
b)exist in the error table but not for the processing that has just
happened.
I started to implement a TimeOfRun field (which is set when the processing
starts) into the error table so that I can distinguish which error records
were created during this processing batch, but I am stuck on the actual
query I just mentioned. I think I need the error table to do a self join
in some way, and the results compared against table1.
any help would be great, and thanks for reading this far down :-)