Hi @LiHongMSFT-3908

Its WOrked..
Thanks for help me

Hi @LiHongMSFT-3908

Thx for your respon.
this create table

CREATE TABLE "KDSCMSCNTRCTHHIST"
( "CNTRCTHHISTID" int,
"CNTRCTHHISTSDATE" DATE,
"CNTRCTHHISTEDATE" DATE,
"CNTRCTHHISTCOMP" VARCHAR(3),
"CNTRCTHHISTLNUM" int,
"CNTRCTHHISTLASTLOC" VARCHAR(30)
)

Hi @OlafHelper-2800

Thank for your respon..
The details I want, please help

Here I want to see the history of the last location..

example:
when I create a new contract with location SCI-SCG4 start date:25-NOV-21 and end date:25-NOV-26(5yrs from start date)

but a few months/years before the contract expires, SCI-SCG4 closes and moves to a new location then the input becomes:
SCI-SCG5 start date:07-DEC-21 and end date:25-NOV-26 (take from the contract that was previously inputted)

I want history from the previous location (SCI-SCG4) with start date : 25-NOV-2021(take it from the first contract input) and end date: 06-DEC-21(because the store closed this date)

Hi @EchoLiu-msft ,

Thanks for your respon.

So i have three tables:

1. KDSCMSCNTRCTH

CNTRCTHID	CNTRCTHIDEXT	CNTRCTHEDATE	CNTRCTHEDATE	CNTRCTHCONFID	CNTRCTHSONO	CNTRCTHCOMP
168	235346	22-NOV-21	22-NOV-26	1	234212	NAV

2. KDSCMSCNTRCTHHIST

CNTRCTHHISTID	CNTRCTHHISTSDATE	CNTRCTHHISTEDATE	CNTRCTHHISTLNUM	CNTRCTHHISTLASTLOC	CNTRCTHHISTCOMP
112	22-NOV-21	22-NOV-26	168	KDS-DCC	NAV

3. KDSCMSSITE

SITESITE	SITESITENAME	SITEID	SITESITECOMP
KDS-SBY	DC Surabaya	28	NAV
KDS-DCC	DC Ciracas	17	NAV

*Continue

Hi @Viorel-1

Thank for respon and help me..its worked and simple for me

Hi @EchoLiu-msft

Thank for respon and help me..

Hi @EchoLiu-msft .

Thank for respon and help me..

Hi Viorel-1

Thx for help .
but can help me again, i wrong input data

 KDSCMSCNTRCTD
 -----------------
 CNTRCTDID | CNTRCTDCOMP | CNTRCTDLNUM |
 --------------------------------------
  142 | NAV     | 207
 --------------------------------------


 KDSCMSCNTRCTDHIST
 ----------------------------------------------
 CNTRCTDHISTID | CNTRCTDHISTSDATE | CNTRCTDHISTCOMP | CNTRCTDHISTLNUM | CNTRCTDHISTASSCODE |
 ------------------------------------------------------------------------------------------
   142 |  17-NOV-21 | NAV      | 207           | 00091
 ------------------------------------------------------------------------------------------
   142 |  20-NOV-21 | NAV      | 207           | 123
 ------------------------------------------------------------------------------------------

i need only get AssetCode from (max) CNTRCTDHISTSDATE from KDSCMSCNTRCTD Like this:

CNTRCTDID	CNTRCTDCOMP	CNTRCTDHISTASSCODE
142	NAV	123
-----------------------------------------------------------