David, Thanks for that. There is always a feeling of relief and frustration when you learn that a language simply cannot do what you are trying to get it to do. You mentioned that this could be done by brute force with PL/PGSQL. I do have this available, but am somewhat unfamiliar with it and am not sure where to begin. I can answer the two situations you brought up. 1. No, the records do not have to match up with records on the same table. I created the two temp tables as a way of separating the entries with the entry reversals, if that makes sense. Now I am trying to reconcile the tables and take out the appropriate records. 2. It is not possible for a record to be deleted. As for meta-data, I am not too concerned with that at the moment. I am looking just to create a monthly "snapshot" report using this data. Because I am pulling this data from data feeds, I can control any "matching" entries that would occur after the first of the current month. Thank you for considering these things in my problem. That being said, since I am mostly unfamiliar with PL/PGSQL could you (or anyone) provide an example of a solution? I am playing with loops, but I am not sure I am on the right path. Thanks, Jeff Herman HVPA, Database Programmer Phone: 734.973.0137 ext 441 Fax: 734.975.1248 hermanj@xxxxxxxx -----Original Message----- From: David Johnston [mailto:polobo@xxxxxxxxx] Sent: Tuesday, February 21, 2012 2:04 PM To: 'Andy Colson'; Jeff Herman Cc: pgsql-general@xxxxxxxxxxxxxx Subject: RE: Combining data from Temp Tables -----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Andy Colson Sent: Tuesday, February 21, 2012 1:37 PM To: Jeff Herman Cc: pgsql-general@xxxxxxxxxxxxxx Subject: Re: 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
