Re: Combining data from Temp Tables

On 2/21/2012 11:31 AM, Jeff Herman wrote:
> Obviously my formatting did not post correctly the first time. Here is
> another attempt, with the desired result table listed:
>
> Temp Table 1
>
> DATE LN MBRID DS
>
> 1/1 A 1 30
>
> 1/1 A 1 30
>
> 1/1 B 1 30
>
> 1/1 A 2 30
>
> 1/1 A 2 30
>
> 1/1 C 3 45
>
> 1/1 D 4 45
>
> 1/1 D 4 45
>
> Temp Table 2
>
> DATE LN MBRID DS
>
> 1/1* A 1 -30
>
> 1/1* A 2 -30
>
> 1/1* A 2 -30
>
> 1/6* D 4 -45
>
> *including and up to five days after Temp Table 1 DATE
>
> Temp Table 3 (desired)
>
> DATE LN MBRID DS
>
> 1/1 A 1 30
>
> 1/1 B 1 30
>
> 1/1 C 3 45
>
> 1/1 D 4 45
>
> Thanks again.
>
> Jeff Herman
>
> HVPA, Database Programmer
>
> Phone: 734.973.0137 ext 441
>
> Fax: 734.975.1248
>
> hermanj(at)hvpa(dot)com
>
> *From:*pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org] *On Behalf Of *Jeff Herman
> *Sent:* Tuesday, February 21, 2012 12:04 PM
> *To:* pgsql-general(at)postgresql(dot)org
> *Subject:* [GENERAL] Combining data from Temp Tables
>
> Hi all,
>
> I have created two temp tables that I would like to combine to make a
> third temp table and am stuck on how to combine them to get the results
> I want. Any guidance you could give would be appreciated.
>
> Temp Table 1 Temp Table 2
>
> _DATE_ _LN_ _MBRID_ _DS_ _DATE_ _LN_ _MBRID_ _DS_
>
> 1/1 A 1 30 1/1* A 1 -30
>
> *1/1 A 1 30*
>
> *1/1 B 1 30*
>
> 1/1 A 2 30 1/1* A 2 -30
>
> 1/1 A 2 30 1/1* A 2 -30
>
> *1/1 C 3 45*
>
> 1/1 D 4 45 1/6* D 4 -45
>
> *1/1 D 4 45*
>
> *including and up to five days after Temp Table 1 DATE
>
> I would like the Temp Table 3 to hold only the *BOLD* records above. In
> essence, it would compare and erase a record at a one to one ratio based
> on some fields being identical and the date range being on or within the
> next five days of Temp Table 1 date.
>
> Thanks!
>
> Jeff Herman
>
> HVPA, Database Programmer
>
> Phone: 734.973.0137 ext 441
>
> Fax: 734.975.1248
>
> hermanj(at)hvpa(dot)com <mailto:hermanj(at)hvpa(dot)com>
>

So you want table3 to have the records from table1 except where they may
cancel out from table2?

In your example, why did only one of these records cancel out?
DATE LN MBRID DS
1/1 A 1 30

If you have two records like above, its going to be hard to not cancel
them both out unless you can add some kind of identifier.

In the records for:
1/1 A 2 30

I see both of them cancel because there are two records in table2, correct?

Would it be ok if both 1/1, A, 1, 30 records canceled (ie do not get
copied to table3?)
Would it be ok if both 1/1, A, 2, 30.... Oh, wait! forget the above.

Just noticed. One table has +30 and the other has -30... What if the
table3 record was a sum? would that work?

-Andy