Getting recent date joining two tables.

[aravindk]

Hello all,

I need help with a correlated query. I am trying to join two tables on a key and get a record that is the latest from the other table. Let me try to explain this with an example. I have a cust_sales table with multiple records per customer. I also have promo table that have multiple records per customer. I want to be able to join these two tables and get the "recent" promo date for each sales record.

Here is how the tables are structured. (dates are in mm/dd/yyyy).

Cust_sales
customer_key inv_no invoice_dt
56 inv1 5/15/2010
56 inv2 6/5/2010
56 inv3 8/9/2010

cust_promo
customer_key promo_no promo_dt
56 promo1 5/28/2010
56 promo2 8/1/2010

This is the result I am looking to get.

56 inv1 5/15/2010 promo1 5/28/2010
56 inv2 6/5/2010 promo1 5/28/2010
56 inv3 8/9/2010 promo2 8/1/2010

I tried a correlated query with a maximum date and I am not get this result. Can someone help?

Thanks in anticipation.
arvind.k.

 

[fredericofonseca]

what have you tried so far? this question has a solution that pretty much resembles one given to one of your other threads

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[aravindk]

this is what i got so far:


select * from cust_sales a, cust_promo b
where a.customer_key = b.customer_key and b.promo_dt = (select max(promo_dt)
from cust_promo x where x.customer_key = b.customer_key and a.invoice_dt <= x.promo_dt)


and I get
56 inv1 2010-05-15 00:00:00.000 56 promo2 2010-08-01 00:00:00.000
56 inv2 2010-06-05 00:00:00.000 56 promo2 2010-08-01 00:00:00.000

I tried the solution from the previous thread four years ago with a cross join but couldnt get to work it either.

 

[simian336]

So you just match it to the date that is closest to it, forward or backward?

Simi

 

[fredericofonseca]

was looking at this again, and it does not make sense.
based on your data and desired output how do you decide that inv1 and inv2 should have the same promo?
inv1 has a date less than any of the promos available
inv2 2 has a date that is in the middle of the 2 available promo dates

I could understand the following criterias
1-where promo date is the lowest date higher or equal to invoice date
2-where promo date is the highest date equal or lower to invoice date
3-where promo date is the highest date higher or equal to invoice date

but your desired results do not match any of the 3 above.
Possibly they could match a criteria of (where the difference in days between promo_dt and invoice_dt is the smallest) but even this could give an issue if there was a higher and lower promo dates that had the same days interval

Cust_sales
customer_key inv_no invoice_dt
56 inv1 5/15/2010
56 inv2 6/5/2010
56 inv3 8/9/2010

Cust_promo
customer_key promo_no promo_dt
56 promo1 5/28/2010
56 promo2 8/1/2010

This is the result I am looking to get.

56 inv1 5/15/2010 promo1 5/28/2010
56 inv2 6/5/2010 promo1 5/28/2010
56 inv3 8/9/2010 promo2 8/1/2010



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[fredericofonseca]

But the following SQL, based on the limited data and desired results you supplied, gives you the desired output

select *
from (
select a.customer_key
      ,a.inv_no
      ,a.invoice_dt
      ,b.promo_no
      ,b.promo_dt
      ,row_number() over ( partition by a.customer_key, inv_no 
                           order by a.customer_key
                                   ,case
                                    when invoice_dt < promo_dt
                                    then DATEDIFF(d,invoice_dt, promo_dt )
                                    else  DATEDIFF(d, promo_dt,invoice_dt )
                                    end
                                    ) as rownum
from cust_sales a
inner join cust_promo b
on a.customer_key = b.customer_key
) t 
where rownum = 1

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886