From: Richard Huxton <dev@archonet.com>
To: Mike Adams <madams55075@comcast.net>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Refactored queries needing validation of syntactic equivalence
Date: Tue, 16 Oct 2007 09:27:20 +0100
Message-ID: <471475E8.4030505@archonet.com> (raw)
In-Reply-To: <47110414.6060907@comcast.net>
References: <470D5F81.5090403@comcast.net>
<470DCFC7.3090901@archonet.com>
<47110414.6060907@comcast.net>
Mike Adams wrote:
> So.....
> The first query should pull all 'MOM' records that have one or more
> corresponding, and possibly orphaned, unassigned receiving records
> belonging to the same po_cd and item_cd.
>
> The second query should pull all unassigned, and possibly orphaned
> receiving records that have one or more corresponding 'MOM' records once
> again matching on po_cd and item_cd.
>
> Using the results of both queries to double check each other, I can
> figure out which (if any) open records are, in fact, orphans and do an
> "after the fact" assignment to the "SPLIT IN MOM" invoice to reduce our
> accrual.
>
> Of course, our ERMS should take care of this automagically; but,
> tragically, it seems "real" inventory cost flow was attached to the
> system using duct tape, hot glue, and a couple of thumb tacks.
>
> So, given all the administriva above, have I actually refactored them
> correctly?
Well, clearly you could have multiple possible matches, because apart
from anything else you could in theory have multiple entries with the
same item-code on the same purchase-order-code. In practice it will be
rare, but it could happen.
However, since the purpose is to provide you with a list so you can make
manual changes there's no problem with that.
What I might be tempted to do is restrict the dates more - you had <=
'31 Oct 2007' I'd also apply >= '1 Aug 2007' (or whatever period is
reasonable). You can always run an unconstrained match once a month to
catch any that slip through the net, but presumably most will fall
within a 90-day period.
HTH
--
Richard Huxton
Archonet Ltd
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, madams55075@comcast.net
Subject: Re: Refactored queries needing validation of syntactic equivalence
In-Reply-To: <471475E8.4030505@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox