Re: Combining data from Temp Tables

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Andy Colson
Sent: Tuesday, February 21, 2012 1:37 PM
To: Jeff Herman
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Combining data from Temp Tables


how about

select date, ln, mbrid, ds, (
   select sum(ds) from t2
   where t2.date >= t1.date and t2.date <= t1.date + '5 days'::interval
   and t1.ln = t2.ln
   and t1.mbrid = t2.mbrid)
from t1

That'll give you both the plus and minus (in two different columns), but it
might sum up the same row from table2 multiple times so I'm not sure its
correct.

And I'm not sure the date range is correct.

Another way to look at the same thing:

select date, ln, mbrid, dsplus - dsminus from (
  select date, ln, mbrid, ds as dsplus, (
   select sum(ds) from t2
   where t2.date >= t1.date and t2.date <= t1.date + '5 days'::interval
   and t1.ln = t2.ln
   and t1.mbrid = t2.mbrid) as dsminus
  from t1
) as x
where dsplus - dsminus <> 0

Totally guessing here.

-Andy

-------------------------------------------------------------------

I am pretty certain this cannot be sufficiently solved via a declarative
statement; it requires procedural logic.

For each unmatched record on table 1 you compare all unmatched records on
table 2.  You pair the first one that matches and exclude the table 2 record
from all future comparisons.

I have done this before but my approach was to load all the unmatched
records into Java and perform the procedural logic there.  This can be done
in PL/PGSQL in a brute-force way and then, if performance is unacceptable,
you can try to add efficiencies or farm out the processing to a more full
featured programming language (one having Lists/Maps and/or Iterators).

Two possible situations to consider:

1) Does a record on table 1 (or table 2) ever have to match up with another
record on the same table (i.e., entry reversal)?
2) Is it ever possible for a record to be deleted?

Also consider what kind of meta-data you want to track in order to generate
a proper reconciliation report.  One common need is to know what the
reconciliation status looked like at some date in the past.  For instance on
the 5th of the month I want to know the exact reconciliation status of my
bank account.  To do this I have to ignore any "matching" entries that
occurred on or after the 1st of the current month (like checks clearing).

Again, the situation you are dealing with almost certainly requires a
procedural solution and so pure SQL is not going to work.  You need PL/PGSQL
(or some other embedded language) or, if you already have an application
server hooked into the database, a "query-process-update" routine coded and
run off the application server.

David J.