Re: Combining data from Temp Tables

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.

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