join two tables date and get latest date on every item

Question

I have two tables,

PART LOG-OUT

PART_ID   DRAWN_DATE   LOCATION
 C0001    07/29/2013     501
 C0002    07/29/2013     604
 C0003    08/01/2013     703
 C0004    08/01/2013     807
 C0005    08/02/2013     505
 C0006    08/02/2013     602
 C0007    08/02/2013     707
 C0008    08/03/2013     802
 C0009    08/03/2013     803

PART RETURN

PART_ID   RETURN_DATE   LOCATION
 C0001    09/04/2013     STORE
 C0002    09/05/2013     STORE
 C0004    09/10/2013     STORE
 C0007    09/12/2013     STORE
 C0008    09/13/2013     STORE

This is the RESULT I want:

PART_ID   LATEST_DATE   LOCATION
 C0001    09/04/2013     STORE
 C0002    09/05/2013     STORE
 C0003    08/01/2013      703
 C0004    09/10/2013     STORE
 C0005    08/02/2013      505
 C0006    08/02/2013      602
 C0007    09/12/2013     STORE
 C0008    09/13/2013     STORE
 C0009    08/03/2013      803

But I failed. Can somebody help? Thanks a lot!

Actually: `C0002`is back in the store again, the return date (09/05/2013) is after the logout date. My [running example](http://sqlfiddle.com/#!2/1cadb/1) should showed me that. ;-) — Carsten Massmann, Aug 05 '13 at 09:59

Carsten Massmann · Accepted Answer · 2013-08-05T11:24:16.543

1

Well, just in case you are using MySQL (actually, it should just as well run under MSSQL!) then you could do something like the following

SELECT pid,COALESCE(rdate,pdate) dat,COALESCE(rloc,ploc) lo FROM partlogout 
LEFT JOIN partreturn ON rid=pid AND rdate>pdate

see here for a little demo: SQLfiddle

Edit: Since the databse apparently runs under MS-Access the Command should be

SELECT pid,NZ(rdate,pdate) dat,NZ(rloc,ploc) lo FROM partlogout 
LEFT JOIN partreturn ON rid=pid AND rdate>pdate

edited Aug 05 '13 at 11:24

answered Aug 05 '13 at 09:50

Carsten Massmann

26,510
2
22
43

Thanks for your fast response. I'll try in a short while. – user2652375 Aug 05 '13 at 10:37
I get this error "Undefined function 'COALESCE' in expression" when I executed the SQL in MS Access 2010 Query. May I know what I should I do? – user2652375 Aug 05 '13 at 11:04
You should google it and then you would come up with [this, an older SO-Post](http://stackoverflow.com/questions/247858/coalesce-alternative-in-access-sql) --> use `NZ()` – Carsten Massmann Aug 05 '13 at 11:19
I have googled the error but asked to get an alternative instead of providing a solution. I'll search at Stackoverflow next time. Thanks a million to cars10 for not only tested the answer before posting but helping me to solve the problem I have been struggled for more than a week. Thank you very much! – user2652375 Aug 05 '13 at 11:41

score 0 · Answer 2 · answered Aug 05 '13 at 09:51

0

I think this should work:

SELECT 
  pl.PART_ID, 
  CASE WHEN ISNULL(RETURN_DATE,'1900-01-01') > DRAWN_DATE THEN RETURN_DATE ELSE DRAWN_DATE END AS 'Latest date',  
  CASE WHEN ISNULL(RETURN_DATE,'1900-01-01') > DRAWN_DATE THEN pr.LOCATION ELSE pl.LOCATION END AS 'Location'
FROM PART_LOGOUT pl 
LEFT JOIN PART_RETURN pr ON pl.PART_ID=pr.PART_ID
ORDER BY pl.PART_ID

You might have to change the source table names as I wasn't sure how they were named.

answered Aug 05 '13 at 09:51

jpw

44,361
6
66
86

Sorry, JPW. I was just wanted to edit the LOCATION of C0002 after RESULT and ruin the table again. Could you please help me to make it right back? Or maybe you can tell me how to do it. Thanks! – user2652375 Aug 05 '13 at 10:41
@user2652375 I'm sorry, but I don't understand what you want. – jpw Aug 05 '13 at 10:57
The formatted table is back again. Thanks! cars10. – user2652375 Aug 05 '13 at 11:01

join two tables date and get latest date on every item

2 Answers2