> OK, here we go. Using a sub-select, you can find the "previous" event in 
> the log something like this:
>
>   SELECT *
>   FROM EventLog X, EventLog Y
>   WHERE
>     X.ExperimentID = Y.ExperimentID AND
>     Y.Timestamp =
>     (
>       SELECT MAX(Timestamp)
>       FROM EventLog Z
>       WHERE
>       Z.ExperimentID = X.ExperimentID AND
>       Z.Timestamp < X.Timestamp
>     )

<snip>

> ...if that makes *any* sense at all?

I'll have to read and work through it some more for it to all make sense, 
but it does make me realise that almost anything I want to get from my data 
should be possible with SQL - something which I was not sure about at the 
start!

Post a reply to this message

scott wrote:
>> OK, here we go. Using a sub-select, you can find the "previous" event 
>> in the log something like this:
>>
>>   SELECT *
>>   FROM EventLog X, EventLog Y
>>   WHERE
>>     X.ExperimentID = Y.ExperimentID AND
>>     Y.Timestamp =
>>     (
>>       SELECT MAX(Timestamp)
>>       FROM EventLog Z
>>       WHERE
>>       Z.ExperimentID = X.ExperimentID AND
>>       Z.Timestamp < X.Timestamp
>>     )
> 
> <snip>
> 
>> ...if that makes *any* sense at all?
> 
> I'll have to read and work through it some more for it to all make 
> sense, but it does make me realise that almost anything I want to get 
> from my data should be possible with SQL - something which I was not 
> sure about at the start!

There are few things that SQL cannot do. There are many, many things 
that *look* like you can't do them, but with a little trickery you can 
do a surprising amount using a purely declarative, set-at-once approach 
using SQL. It's a very powerful data manipulation paradigm. ;-)



Indeed, I spent much of my time at uni teasing through tricky SQL 
problems. It can be quite an exciting challenge. Now I'm not saying I 
was the teacher's pet, but I do remember sitting in a lecture hall 
containing about 40 people, and the lecturer saying

"And for this reason, we have various forms of normalisation. There's 
1st normal form, 2nd normal form, and so forth upto something like 6th 
normal form, with each one less redundant than the one before. However, 
for all practical purposes, you only really need to worry about 
Boyce-Codd normal form, which says - ANDREW..."

[I stand up]

"...every determinent is a candidate key..."

[I sit down]

"...thank you Andrew. Now, what that means is[...]"



One thing that SQL can't handle - as far as I know - is relationships 
which are arbitrarily recursive. The classic example is where you have a 
part that's made up of several other parts, which in turn are make up of 
smaller parts, etc. It's trivial to find all sub-parts down to a given 
level of recursion, but AFAIK impossible to recurse until you can't 
recurse any further.

Post a reply to this message

scott wrote:
>> OK, here we go. Using a sub-select, you can find the "previous" event 
>> in the log something like this:
>>
>>   SELECT *
>>   FROM EventLog X, EventLog Y
>>   WHERE
>>     X.ExperimentID = Y.ExperimentID AND
>>     Y.Timestamp =
>>     (
>>       SELECT MAX(Timestamp)
>>       FROM EventLog Z
>>       WHERE
>>       Z.ExperimentID = X.ExperimentID AND
>>       Z.Timestamp < X.Timestamp
>>     )
> 
> <snip>
> 
>> ...if that makes *any* sense at all?

I'm guessing that's higher overhead than taking your original 
multi-AND-clause query and adding
   AND NOT EXISTS (Select ...)
to exclude having other records in the range of interest. Of course, it 
depends on your index configuration and how good your optimizer is.

-- 
   Darren New, San Diego CA, USA (PST)
   My fortune cookie said, "You will soon be
   unable to read this, even at arm's length."

Post a reply to this message

Invisible wrote:
> It's more that when auditors walk in and demand to know why X happened, 
> you're not really allowed to say "well that's because some idiot who 
> doesn't know the correct authorisation procedure decided to change it 
> without bothering to notify me". 

Then you definitely need to bump this up the chain of command. Get them to 
put in writing that you're to lie to the auditors about why changes were 
made without being logged, or something like that. :-)

> Cute, but I suspect they'd simply summarily fire me.

I wouldn't think so. Not if you go far enough up the ladder. I strongly 
suspect they'd be in trouble for firing you over your insistence that you 
obey the law.

-- 
   Darren New, San Diego CA, USA (PST)
   My fortune cookie said, "You will soon be
   unable to read this, even at arm's length."

Post a reply to this message

Invisible wrote:
> I have no power to prevent them changing things (either through
> technology or politics). I don't even really have the ability to detect
> changes when they happen. But I definitely *am* held legally responsible
> for this.

	Start gathering documentation showing that they repeatedly make changes
without your knowledge. Then if they ever try to make you legally
responsible, you can show that they weren't following protocol, and you
were.

	(Assuming that's the case). Also helps if you can document that you
informed everyone that their actions are against policy (just a copy of
an email you sent).

-- 
Copywight 1991 Elmer Fudd.  All wights wesewved


                    /\  /\               /\  /
                   /  \/  \ u e e n     /  \/  a w a z
                       >>>>>>mue### [at] nawazorg<<<<<<
                                   anl

Post a reply to this message

On Mon, 23 Feb 2009 11:15:40 -0600, Mueen Nawaz wrote:

> 	Start gathering documentation showing that they repeatedly make 
changes
> without your knowledge. Then if they ever try to make you legally
> responsible, you can show that they weren't following protocol, and you
> were.

Seconded.  From a legal sense, if it ain't documented, it didn't happen.

Jim

Post a reply to this message