How do I union 2 datasets while also pulling the later effective date, to avoid duplicates?

Question

0.00/5 (No votes)

See more:

I'll start with what I'm trying to accomplish:

- I have a current and historic table.
- I want to check if the same customer in both tables, has ever had a reason_iden_fk = 1.
- If they have a record in both tables I'd like to take the later of the 2.
- How can I force the most recent effective date from TL to be returned while all other dates are ignored?

select 
 custid
,tl.effectivedate

from tblcurrent c 
    join translog tl 
        on c.custid = tl.custid
           and tl.reason_iden_fk = 1 

union 

select 

from tblhistory h 
    join translog tl 
        on h.custid = tl.custid
           and tl.reason_iden_fk

What I have tried:

Row_number rank functions but the performance hit is massive.

Posted 1-Aug-17 11:11am

Member 11820531

Updated 2-Aug-17 1:05am

Add a Solution

Comments

RossMW 1-Aug-17 21:57pm

I struggle with the data structure because you are joining both the translog to the tblcustomer and tblhistory tables. Hence if these are the same customers then both of your select statements will return the same translog records for that customer, so the result is the same effectivedates return. This implies the history table is irrelevant in the query.

I think you will need to provide the relevent data structure, sample data and expected output for us to help you any further.

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kornfeld Eliyahu Peter · Answer 1 · 2017-08-02T01:05:00

If you are interested in the latest date (either form current or archieved table) UNION can not help you...
UNION will remove duplicates, but for latest date you will have to do a JOIN between the tables...
Something like this:

SQL

select *
from table1
  left join table2 on table1.customer = table2.customer and table1.date > table2.date -- will bring only rows where there is not newer row in table2
where table2.customer is null

union

select *
from table2

How do I union 2 datasets while also pulling the later effective date, to avoid duplicates?

1 solution

Solution 1

Add your solution here

Preview 0

How do I union 2 datasets while also pulling the later effective date, to avoid duplicates?

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us