Skip to main content
added 1192 characters in body
Source Link

I am not sure if I understood your question in a right way, but if what I understood was true the query below will comparecompares ProfileId of two tables and updateupdates the endDate fildfield with current date it also inserts profileIds from newdata table which are not exist in CustomerProfile (customerId field is not available for them) and fills StartDate field with current date:

UPDATEdrop table if exists #groupedProfileIds
drop table if exists #groupedProfileIds_agg
select * INTO #groupedProfileIds 
from(SELECT ProfileId ,1 as flag FROM CustomerProfile
    UNION 
    SELECT ProfileId, 2 as cflag FROM NewData)  as #tmp
SET-- we use this flag to determine the table which record came from-------
select * INTO #groupedProfileIds_agg
from (select  ProfileId,sum(flag) as flag_agg
      from #groupedProfileIds
      group by ProfileId) #temp2
-- we used second temp table to store summation of flag to avoid using aggregate function in where clause  
-- when flag is 1  it shows the ProfileId only exists in CustomerProfile, 
--when it is 2 the ProfileId only exists in NewData and it can be 3 if same profileId is available in both ----------

update DBO.CustomerProfile set endDate = GETDATE()
WHERE not exist      where  ProfileId in (select 1ProfileId from #groupedProfileIds_agg
 NewData as N                             where N.ProfileId=flag_agg c= 1) 
    
insert INTO DBO. CustomerProfile (ProfileId ,startDate) 
select ProfileId,getdate();
from #groupedProfileIds_agg
    where flag_agg=2
    

I am not sure if I understood your question in a right way, but if what I understood was true the query below will compare ProfileId of two tables and update the endDate fild with current date:

UPDATE CustomerProfile as c
SET endDate = GETDATE()
WHERE not exist (select 1 from  NewData as N where N.ProfileId= c.ProfileId );

I am not sure if I understood your question in a right way, but if what I understood was true the query below will compares ProfileId of two tables and updates the endDate field with current date it also inserts profileIds from newdata table which are not exist in CustomerProfile (customerId field is not available for them) and fills StartDate field with current date:

drop table if exists #groupedProfileIds
drop table if exists #groupedProfileIds_agg
select * INTO #groupedProfileIds 
from(SELECT ProfileId ,1 as flag FROM CustomerProfile
    UNION 
    SELECT ProfileId, 2 as flag FROM NewData)  as #tmp
-- we use this flag to determine the table which record came from-------
select * INTO #groupedProfileIds_agg
from (select  ProfileId,sum(flag) as flag_agg
      from #groupedProfileIds
      group by ProfileId) #temp2
-- we used second temp table to store summation of flag to avoid using aggregate function in where clause  
-- when flag is 1  it shows the ProfileId only exists in CustomerProfile, 
--when it is 2 the ProfileId only exists in NewData and it can be 3 if same profileId is available in both ----------

update DBO.CustomerProfile set endDate = GETDATE()
        where  ProfileId in (select ProfileId from #groupedProfileIds_agg
                                where flag_agg = 1) 
    
insert INTO DBO. CustomerProfile (ProfileId ,startDate) 
select ProfileId,getdate()
from #groupedProfileIds_agg
    where flag_agg=2
    
Source Link

I am not sure if I understood your question in a right way, but if what I understood was true the query below will compare ProfileId of two tables and update the endDate fild with current date:

UPDATE CustomerProfile as c
SET endDate = GETDATE()
WHERE not exist (select 1 from  NewData as N where N.ProfileId= c.ProfileId );