Re: Combining data from Temp Tables - Mailing list pgsql-general

From Jeff Herman
Subject Re: Combining data from Temp Tables
Date
Msg-id DCD5B559B90AFB46B549AFD282BBE42692E3F9@BY2PRD0410MB388.namprd04.prod.outlook.com
Whole thread Raw
In response to Combining data from Temp Tables  (Jeff Herman <hermanj@hvpa.com>)
Responses Re: Combining data from Temp Tables
List pgsql-general

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@hvpa.com

 

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Jeff Herman
Sent: Tuesday, February 21, 2012 12:04 PM
To: pgsql-general@postgresql.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@hvpa.com

 

 

pgsql-general by date:

Previous
From: Jeff Herman
Date:
Subject: Combining data from Temp Tables
Next
From: Andy Colson
Date:
Subject: Re: Combining data from Temp Tables