SQL Comparing Transaction Data Times

5 pts.
Tags:
SQL
Hello: I have 2 tables: Table A (unique table of IDs and final times) ID - MaxTime 100 - 11/30/2007 200 - 11/15/2007 300 - 12/1/2007 Table B (transaction data for each ID) ID - Time 100 - 11/30/2007 100 - 11/29/2007 100 - 11/28/2007 200 - 11/18/2007 300 - 11/31/2007 For each ID in Table A, create a new table from the rows in Table B where the time is < Maxtime from table A. The resulting table should be as follows: 100 - 11/28/2007 100 - 11/29/2007 200 - NULL 300 - 11/31/2007

Answer Wiki

Thanks. We'll let you know when a new response is added.

The easiest way to do this is with a two queries. The first to get the data loaded, and the second to add in the missing rows.

<pre>INSERT INTO TABLEC
SELECT ID, Time
FROM TABLEB
WHERE EXISTS (SELECT * FROM TABLEA WHERE TABLEA.ID = TABLEB.ID AND TABLEB.TIME < TABLEA.MaxTime)

INSERT INTO TABLEC
SELECT ID, NULL
FROM TABLEB
WHERE NOT EXISTS (SELECT * FROM TABLEC WHERE TABLEC.ID = TABLEB.ID)</pre>

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • FrankKulash
    To do the same thing with one INSERT statement, use an outer join, like this:
    INSERT INTO	tablec (id, time)
    SELECT	COALESCE (tablea.id, tableb.id)
    ,	time
    FROM		tablea
    LEFT OUTER JOIN	tableb
    ON		tablea.id	        = tableb.id
    AND		tablea.maxtime > tableb.time;
    
    1,240 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following