On 2016/09/15 5:53 PM, David Bicking wrote:
I have two tables:
CREATE TABLE M ( CombineKeyFields, EvtNbr, TransDate, OtherFields, PRIMARY
KEY(CombinedKeyFields, EvtNbr, TransDate));CREATE TABLE E ( CombineKeyFields,
EvtNbr, TransDate, OtherFields, PRIMARY KEY(CombinedKeyFields, EvtNbr,
TransDate));
"CombinedKeyFields" is shorthand for a combination of about a half dozen fields in the
primary key."TransDate" is an integer storing a proprietary date sequence number, where
an older date is always less than a newer date
Now, I want to do E LEFT JOIN M
(1) The CombinedKeyFields must always match in each table(2) Match using the
EvtNbr, but if no match, use the lowest M.EvtNbr that matches the
CombinedKeyFields
(3) Match using the TransDate but if no exact match, match on the M.TransDate
that is less than the E.TransDate but greater than the prior E.TransDate
For M.TransDate = 94E.TransDate = 96 will match to 94but E.TransDate = 98 will
have no match because 94 is less than the prior trans at 96..The idea is to
find the closest date that matches that couldn't be matched to another record.
Hi David,
I was going to do this for fun, but some anomalies exist in your
description which needs to be understood first.
1 - Does every entry have an EvtNbr? Or can it be null? What is the
lowest EvtNbr in every set - 1 or 0 or something else or indeterminate?
If an EvtNbr cannot be Null, then point (3) above is moot because there
will be no item with a date that matches CombinedKeyFields that doesn't
also have a lowest EvtNbr as can be matched by requirement (2). If the
lowest EvtNbr for any entry is 0 or 1 then you can simply look for that
EvtNbr if a match is not found for the exact EvtNbr - which would always
exist unless there are NO matches on CombinedKeyFields in which case
there should be no matching lines at all as given by (1).
Perhaps if you could (as suggested by another poster) send a full schema
with some data and an example of the output needed (highlighting all the
possible check cases as described above).
If clarity on these can be had, the SQL is quite possible and not too
convoluted even (though the jury is out on efficiency as yet).
Saying a prayer that the email gods won't wrap all these lines together in to
an unreadable mess like the last time I asked for advice here...
Apparently prayer is not an effective technology :) The e-mail Gods are
not at fault here, perhaps the "Send as" settings in your mail
composition client can be explored? Many formats should accommodate
correct formatting. I'm guessing your client has a plain-text editor
that wraps the result into an HTML paragraph (or some other weirdness
that you can hopefully deduce and fix by checking the settings).
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
