none
T-SQL Question RRS feed

  • Question

  • Hello All,

    I have two tables as below

    COMPANY

    Service_date    new_company       old_company

    1/11/2019        ABC                       XYZ

    1/12/2019        DEF                       HYU

    1/14/2019         UVX                       KLM

    DAILY_COST

    Report_Date          company_name           

    1/10/2019                  EOG

    1/11/2019                  XYZ

    1/12/2019                  HYU

    1/14/2019                  KLM

    Now, I need to update the COMPANY_NAME in DAILY_COST from NEW_COMPANY column in COMPANY table for all the REPORT_DATE > MIN (SERVICE_DATE).

    Expected output

    DAILY_COST

    REPORT_DATE           COMPANY_NAME

    1/10/2019                  EOG

    1/11/2019                  ABC

    1/12/2019                  DEF

    1/14/2019                  UVX

    Could you please help me in achieving this? 

    Appreciate your help.

    Thanks much

    Monday, October 21, 2019 7:51 PM

Answers

  • CREATE TABLE COMPANY(
       Service_date DATE  NOT NULL PRIMARY KEY
      ,new_company  VARCHAR(3) NOT NULL
      ,old_company  VARCHAR(3) NOT NULL
    );
    INSERT INTO COMPANY(Service_date,new_company,old_company) VALUES
     ('1/11/2019','ABC','XYZ')
    ,('1/12/2019','DEF','HYU')
    ,('1/14/2019','UVX','KLM');
    
    
    CREATE TABLE DAILY_COST(
       Report_Date  DATE  NOT NULL PRIMARY KEY
      ,company_name VARCHAR(3) NOT NULL
    );
    INSERT INTO DAILY_COST(Report_Date,company_name) VALUES
     ('1/10/2019','EOG')
    ,('1/11/2019','XYZ')
    ,('1/12/2019','HYU')
    ,('1/14/2019','KLM');
    
    ;with mycte as (
    select *,row_number() Over(partition by Report_Date  order by SERVICE_DATE desc) rn 
    from DAILY_COST dc 
    left join COMPANY c on dc.Report_Date >=c.Service_date
    )
    
    select REPORT_DATE ,coalesce( new_company,COMPANY_NAME) COMPANY_NAME          from mycte 
    Where rn=1
    order by 1
    
    drop TABLE DAILY_COST, COMPANY

    • Marked as answer by Srik Kotte Wednesday, October 23, 2019 2:54 PM
    Monday, October 21, 2019 9:20 PM
    Moderator
  • Hi Srik Kotte,

    Would you like this one ?

    CREATE TABLE COMPANY(
       Service_date DATE  NOT NULL PRIMARY KEY
      ,new_company  VARCHAR(3) NOT NULL
      ,old_company  VARCHAR(3) NOT NULL
    );
    INSERT INTO COMPANY(Service_date,new_company,old_company) VALUES
     ('1/11/2019','ABC','XYZ')
    ,('1/12/2019','DEF','HYU')
    ,('1/14/2019','UVX','KLM');
    
    CREATE TABLE DAILY_COST(
       Report_Date  DATE  NOT NULL PRIMARY KEY
      ,company_name VARCHAR(3) NOT NULL
    );
    INSERT INTO DAILY_COST(Report_Date,company_name) VALUES
     ('1/10/2019','EOG')
    ,('1/11/2019','XYZ')
    ,('1/12/2019','HYU')
    ,('1/14/2019','KLM');
    
    ;with cte as (
    select *, min(Service_date)over(partition by (select 1)) min_value  from COMPANY)
    update a 
    set a.COMPANY_NAME =b.new_company
    from DAILY_COST a left join cte b 
    on a.company_name=b.old_company
    where a.Report_Date>=b.min_value
    
    select * from DAILY_COST
    /*
    Report_Date company_name
    ----------- ------------
    2019-01-10  EOG
    2019-01-11  ABC
    2019-01-12  DEF
    2019-01-14  UVX
    */

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Srik Kotte Wednesday, October 23, 2019 2:54 PM
    Tuesday, October 22, 2019 3:29 AM

All replies

  • CREATE TABLE COMPANY(
       Service_date DATE  NOT NULL PRIMARY KEY
      ,new_company  VARCHAR(3) NOT NULL
      ,old_company  VARCHAR(3) NOT NULL
    );
    INSERT INTO COMPANY(Service_date,new_company,old_company) VALUES
     ('1/11/2019','ABC','XYZ')
    ,('1/12/2019','DEF','HYU')
    ,('1/14/2019','UVX','KLM');
    
    
    CREATE TABLE DAILY_COST(
       Report_Date  DATE  NOT NULL PRIMARY KEY
      ,company_name VARCHAR(3) NOT NULL
    );
    INSERT INTO DAILY_COST(Report_Date,company_name) VALUES
     ('1/10/2019','EOG')
    ,('1/11/2019','XYZ')
    ,('1/12/2019','HYU')
    ,('1/14/2019','KLM');
    
    ;with mycte as (
    select *,row_number() Over(partition by Report_Date  order by SERVICE_DATE desc) rn 
    from DAILY_COST dc 
    left join COMPANY c on dc.Report_Date >=c.Service_date
    )
    
    select REPORT_DATE ,coalesce( new_company,COMPANY_NAME) COMPANY_NAME          from mycte 
    Where rn=1
    order by 1
    
    drop TABLE DAILY_COST, COMPANY

    • Marked as answer by Srik Kotte Wednesday, October 23, 2019 2:54 PM
    Monday, October 21, 2019 9:20 PM
    Moderator
  • Hi Srik Kotte,

    Would you like this one ?

    CREATE TABLE COMPANY(
       Service_date DATE  NOT NULL PRIMARY KEY
      ,new_company  VARCHAR(3) NOT NULL
      ,old_company  VARCHAR(3) NOT NULL
    );
    INSERT INTO COMPANY(Service_date,new_company,old_company) VALUES
     ('1/11/2019','ABC','XYZ')
    ,('1/12/2019','DEF','HYU')
    ,('1/14/2019','UVX','KLM');
    
    CREATE TABLE DAILY_COST(
       Report_Date  DATE  NOT NULL PRIMARY KEY
      ,company_name VARCHAR(3) NOT NULL
    );
    INSERT INTO DAILY_COST(Report_Date,company_name) VALUES
     ('1/10/2019','EOG')
    ,('1/11/2019','XYZ')
    ,('1/12/2019','HYU')
    ,('1/14/2019','KLM');
    
    ;with cte as (
    select *, min(Service_date)over(partition by (select 1)) min_value  from COMPANY)
    update a 
    set a.COMPANY_NAME =b.new_company
    from DAILY_COST a left join cte b 
    on a.company_name=b.old_company
    where a.Report_Date>=b.min_value
    
    select * from DAILY_COST
    /*
    Report_Date company_name
    ----------- ------------
    2019-01-10  EOG
    2019-01-11  ABC
    2019-01-12  DEF
    2019-01-14  UVX
    */

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Srik Kotte Wednesday, October 23, 2019 2:54 PM
    Tuesday, October 22, 2019 3:29 AM
  • Both the solutions worked for me. Thank you so much for the help.
    Wednesday, October 23, 2019 2:55 PM