Update Date day -1

Riyn Pratama 41 Reputation points
2021-12-07T08:04:26.157+00:00

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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,321 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,523 questions
{count} votes

Accepted answer
  1. LiHong-MSFT 10,036 Reputation points
    2021-12-08T02:54:40.087+00:00

    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:
    155679-image.png

    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.


2 additional answers

Sort by: Most helpful
  1. Olaf Helper 39,181 Reputation points
    2021-12-07T08:08:34.577+00:00

    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?


  2. Guoxiong 8,126 Reputation points
    2021-12-07T16:19:56.96+00:00

    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;
    
    0 comments No comments