Update Date day -1

Question

Hi please help I'm trying insert update one tables:

KDSCMSCNTRCTHHIST
----------------------------------------------
CNTRCTHHISTID | CNTRCTHHISTSDATE| CNTRCTHHISTEDATE | CNTRCTHHISTCOMP | CNTRCTHHISTLNUM | CNTRCTHHISTLASTLOC |
--------------------------------------------------------------------------------------------------------------------
  141 |  25-NOV-21 |  25-NOV-26      | NAV           | 172 | SCI-SCG4 
--------------------------------------------------------------------------------------------------------------------

i want to get insert update CNTRCTHHISTEDATE Like this:

KDSCMSCNTRCTHHIST
 ----------------------------------------------
CNTRCTHHISTID | CNTRCTHHISTSDATE| CNTRCTHHISTEDATE | CNTRCTHHISTCOMP | CNTRCTHHISTLNUM | CNTRCTHHISTLASTLOC |
 --------------------------------------------------------------------------------------------------------------------
141 |  25-NOV-21 | 06-DEC-21       | NAV           | 172  | SCI-SCG4 
--------------------------------------------------------------------------------------------------------------------
142 |  07-DEC-21  | 25-NOV-26      | NAV           | 172  | SCI-SCG5 
--------------------------------------------------------------------------------------------------------------------

how to query to update the data?

Answer 1

Hi，@Riyn Pratama
Welcome to the microsoft tsql Q&A forum!
Please check this:

IF OBJECT_ID('view_KDSCMSCNTRCTHHIST')IS NOT NULL  
DROP VIEW view_KDSCMSCNTRCTHHIST  
GO  
CREATE VIEW view_KDSCMSCNTRCTHHIST  
AS  
SELECT * FROM KDSCMSCNTRCTHHIST  
WHERE CNTRCTHHISTCOMP = 'NAV' AND CNTRCTHHISTLNUM=172  
WITH CHECK OPTION;  
  
INSERT INTO view_KDSCMSCNTRCTHHIST  
SELECT CNTRCTHHISTID+1,  
       '07-DEC-21', --SCI-SCG5 start date:07-DEC-21   
    (SELECT CNTRCTHHISTEDATE FROM view_KDSCMSCNTRCTHHIST ), --end date:take from the contract that was previously inputted  
    CNTRCTHHISTCOMP,  
    CNTRCTHHISTLNUM,  
    'SCI-SCG5' --a new location  
FROM view_KDSCMSCNTRCTHHIST ;  
  
UPDATE view_KDSCMSCNTRCTHHIST  
SET CNTRCTHHISTEDATE = DATEADD(DAY, -1, (SELECT TOP(1)CNTRCTHHISTSDATE FROM view_KDSCMSCNTRCTHHIST ORDER BY CNTRCTHHISTID DESC))  
WHERE CNTRCTHHISTSDATE = '25-NOV-21';  

Output:

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

i want to get insert update CNTRCTHHISTEDATE Like this:

And what's the business logic for the wanted result; just by having a look at I don't get it?

Answer 3

Try this:

DECLARE @CNTRCTHHISTID int = 141;
DECLARE @locationChangeDate date = '07-DEC-21';

INSERT INTO KDSCMSCNTRCTHHIST 
SELECT CNTRCTHHISTID + 1,
       CNTRCTHHISTSDATE = @locationChangeDate,
       CNTRCTHHISTEDATE,
       CNTRCTHHISTCOMP,
       CNTRCTHHISTLNUM,
       CNTRCTHHISTLASTLOC = SUBSTRING(CNTRCTHHISTLASTLOC, PATINDEX('%^[0-9]%', CNTRCTHHISTLASTLOC), LEN(CNTRCTHHISTLASTLOC)) + CAST(SUBSTRING(CNTRCTHHISTLASTLOC, PATINDEX('%[0-9]%', CNTRCTHHISTLASTLOC), LEN(CNTRCTHHISTLASTLOC)) + 1 AS varchar(3))
FROM KDSCMSCNTRCTHHIST
WHERE CNTRCTHHISTID = @CNTRCTHHISTID;

UPDATE KDSCMSCNTRCTHHIST
SET CNTRCTHHISTEDATE = DATEADD(DAY, -1, @locationChangeDate)
WHERE CNTRCTHHISTID = @CNTRCTHHISTID;