asked on

merge two tables and update one record to be the "most recent" transaction date

Hello. I have a Transaction History table that is updated via PHP in near-real-time as the transaction occurs. The PHP update makes sure that the "active" field is 1 for the latest transaction (by transaction date) - in this case it is always the one it is adding at the time
and the PHP makes sure no other history record for that MASTER_ID has active set.

Now I also have some records that come to me at the end of the day in a batch. I need to merge them into the transaction history table and take care to set the active field to 1 or 0 depending on the transaction date/time so that the resulting Transaction History table
has one record per distinct MASTER_ID where the active field that is set to 1.
The merged records can have multiple entries for a given MASTER_ID (as can the TransactionHistory table)
The transaction date/times in the Staging table (my batch updates) may be either before or
after the "latest" entry in the TransactionHistory table for the corresponding records.

The pertinent fields:
Table TransHistory --

   TransTime datetime
   ID               int (primary key)
   Active         bit
   Result_ID   int  
  ...  //stuff that gets updated but isn't relevant to this problem
  MASTER_ID  int

Open in new window

Table StagingTable --

   MASTER_ID       int
  Active                 bit    (defaults to b'1')
  TransDateTime datetime
  Agent_ID            int
  Result_ID           int
  ...

Open in new window

Any ideas of how I can accomplish this - merge and set the very latest (timewise) for
a MASTER_ID as the one flagged 'active'?

Thanks in advance!

chaau

this will do:

create table TransHistory (TransTime datetime,
   Active         bit,
   Result_ID   int  ,
  MASTER_ID  int);

create table StagingTable (TransTime datetime,
   Active         bit,
   Result_ID   int  ,
  MASTER_ID  int);


insert into TransHistory
(TransTime, Active, Result_ID, MASTER_ID)
select st.TransTime, '1', st.Result_ID, st.MASTER_ID
from StagingTable st
inner join (select MASTER_ID, MAX(TransTime) AS maxTransTime FROM StagingTable GROUP BY MASTER_ID) maxTime
ON st.MASTER_ID = maxTime.MASTER_ID and st.TransTime = maxTime.maxTransTime;

Open in new window

(notice that we grab the latest entry from the staging table and insert it into the transaction history with the status of "active")

I've created SQL Fiddle for you to play with

pootwaddle

ASKER

hi, chaau -

Thanks, I will play with your solution. However, maybe I wasn't clear - I need to insert all the records from the staging table into my history table, just making sure the max(transtime) record of the resulting combined set is the only one marked active.

For historical reporting purposes I have to keep each transaction. The "active" flag is there to make it easy to report current status of each record in the master table.

chaau

ok, I will adjust the solution in a sec

ASKER CERTIFIED SOLUTION

chaau

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

pootwaddle

ASKER

Thank you! While not exactly what I needed, it was close enough to get me onto the correct path.

Take care!
Bryan

merge two tables and update one record to be the &quot;most recent&quot; transaction date

merge two tables and update one record to be the "most recent" transaction date