Compare two table and update with endDate

Question

I have two table CustomerProfile and NewData. The CustomerProfile has CustomerId,ProfileId,startDate and endDate. The NewData table is new data that is coming in just has the ProfileId.I create a temp table with ids from both tables named #groupedProfileIds. I then loop the temp table checking if the id is in any of the main table. Then base the the count I do an insert or update . I have tried to do it with while and checking the count of both tables but wanted to see if there is a better way.

The goal of this is to have a updated CustomerProfile table base on the newData table. If the id are not in the newData table the CustomerProfile table will be updated with a end date. If they are new Id they will be inserted with a start date. I use ‘ @OldProfileId = 1 and @NewProfileId = 0’ to do the update because the data is not in the new table. For the insert I do ‘ @OldProfileId = 0 and @NewProfileId = 1’ because the data is just in the newData. I don’t need to check 1 because they would be in both table .

http://sqlfiddle.com/#!18/73845

            select * INTO #groupedProfileIds from(
            SELECT ProfileId FROM CustomerProfile
                UNION 
                SELECT ProfileId FROM NewData)  as #tmp

            declare @Rowcount int 
            select @Rowcount=count(*) from #groupedProfileIds
            while( @Rowcount>0)
            begin 
             select @Rowcount=@Rowcount-1;
             DECLARE @ProfileIds int
             set @ProfileIds = (SELECT Id FROM #groupedProfileIds order by Id desc OFFSET @Rowcount ROWS FETCH NEXT 1 ROWS ONLY);
             print @ProfileIds

             DECLARE @OldProfileId INT 
             SET @OldProfileId = (SELECT Count(ProfileId) FROM CustomerProfile where ProfileId = @ProfileIds)

             DECLARE @NewProfileId INT
             SET @NewProfileId = (SELECT Count(ProfileId) FROM #NewData where ProfileId = @ProfileIds)

             IF @OldProfileId = 1 and @NewProfileId = 0
             BEGIN
                update DBO.CustomerProfile set endDate = GETDATE() where ProfileId = @ProfileIds 

             end

             IF @OldProfileId = 0 and @NewProfileId = 1
             BEGIN
                INSERT INTO DBO. CustomerProfile (CustomerId, ProfileId,startDate)
                VALUES (CustomerId,@ProfileIds,GETDATE())
             end

Answer 1

The best way to do this is you create a query which has the data you want. Then you can decide what to insert and what to update.

If the profile id is not in CustomerProfile you want to insert a new row with a customerId, you don't say from where you get that customerId since all you have is a profileId??

The deficiency by which you specify your requirement is the reason why you don't find a solution. And nobody can help you because you don't say what you want. Suddenly inserting "cancer" somewhere makes it so much worse!

Nobody can answer your question because you don't say what you want. However, if we make some assumptions then the best we do is outline a direction which you can go.

Can we guess anything from your incomplete table schema?

CREATE TABLE CustomerProfile (
  customerProfileId integer PRIMARY KEY,
  customerId        integer NULL,
  profileId         integer,
  startDate         datetime,
  endDate           datetime NULL
)
  
CREATE TABLE NewData (
  profileId         integer
)

The way this is written there should be a Customer table and a Profile table

CREATE TABLE Customer (
  customerId        integer PRIMARY KEY,
  ...
)
  
CREATE TABLE Profie (
  profileId         integer PRIMARY KEY,
  ...
)

then your tables you did mention should be completed as:

CREATE TABLE CustomerProfile (
  customerProfileId integer PRIMARY KEY,
  customerId        integer REFERENCES Customer(customerId),
  profileId         integer REFERENCES Profile(profileId),
  startDate         timestamp,
  endDate           timestamp
)
  
CREATE TABLE NewData (
  profileId         integer REFERENCES Profile(profileId)
)

I first retained your customerId NULL non-constraint to give myself an out to not having to worry how I infer the customerId from a mere profileId when inserting. But then I figured might just complete your schema, to have it logical. This creates additional tasks to insert a new Customer and Profile row, I know how I can do that, but you didn't ask for it, so I won't do it.

So the above is just for me to get my head around what you want. You still haven't said it quite sufficiently.

I assume from your table that your relationship between Customer and Profile is many-to-many. And I doubt that. I think you have not really thought through your business data logic. But for what I am proposing it doesn't matter.

I am also not interested in SQL Server stuff, but I will use standard SQL (my database is PostgreSQL). If you don't think SQL (the language, not your software) you will not be a good database designer / programmer / administrator, whatever you want to call yourself.

In standard SQL "datetime" is called "timestamp" and the standard way to get the current timestamp is current_timestamp.

About your endDate you say:

If the id are not in the newData table the CustomerProfile table will be updated with a end date.

So when inserting into your table, I assume that startDate = current_timestamp and endDate = NULL.

Finally I assume that these ids are "sequences" but I will abstain from any sequence extension to any database and just determine the next value from

SELECT max(fooId) + 1 FROM Foo

where "Foo" may be Customer, Profile, or CustomerProfile.

With all these assumptions made, I begin, as I said, the way you should always begin. Write a SELECT which will give you the final desired table, or, here, I begin with the INSERT part:

INSERT INTO CustomerProfile(customerProfileId, customerId, profileId, startDate, endDate)
WITH DistinctNewProfileIds AS (
  SELECT DISTINCT profileId FROM NewTable
), MaxCustomerProfileId AS (
  SELECT max(customerProfileId) AS maxCustomerProfileId
    FROM CustomerProfile
), MaxCustomerId AS (
  SELECT max(customerId) AS maxCustomerId
    FROM Customer
), ProfileIdWithOrdinal AS (
  SELECT rank() OVER (ORDER BY profileId) AS ordinal,
         profileId
    FROM DistinctNewProfileIds
)
SELECT maxCustomerProfileId + ordinal AS customerProfileId,
       maxCustomerId + ordinal AS customerId,
       profileId,
       current_timestamp AS startDate,
       NULL AS endDate
  FROM ProfileIdWithOrdinal 
  CROSS JOIN MaxCustomerProfileId
  CROSS JOIN CustomerId

Now the UPDATE. You seem to expect all profileIds which are still not ended to appear in your NewData every time. I initially understood you would extend the endDate every time you find the profileId in the NewData. It makes me wonder what happens if a profileId suddenly re-appears in the NewData after you already have missed it once and you added an endDate already? Are you supposed to wipe out the endDate with NULL again? You didn't ask for it, so I will not do it. But you haven't thought through your design if you don't consider that.

Here is the partition of your table which you update, after the update:

WITH DistinctNewProfileIds AS (
  SELECT DISTINCT profileId FROM NewTable
)
SELECT customerProfileId, customerId, profileId, 
       startDate, 
       CASE WHEN p.profileId IS NULL
            THEN current_timestamp
            ELSE NULL END as endDate
  FROM CustomerProfile cp
  LEFT OUTER JOIN DistinctNewProfileIds p
    ON(p.profileId = cp.profileId)

So here I actually would reset your endDate to NULL if the profileId re-appears. But the update anyway gets really simple:

UPDATE CustomerProfile 
   SET endDate = current_timestamp
 WHERE profileId NOT IN (SELECT profileId FROM NewTable)

You run the UPDATE part first, then the INSERT. And you're done.

The best description of your requirement is a SELECT query which will make what you want. What you INSERT vs. UPDATE is then just a secondary issue. This is even more true if you had to also insert Customer and Profile rows to satisfy foreign key constraints. In PostgreSQL I do these things in a single query. In lesser databases like Oracle or MS SQL Server I guess you have to do multiple steps in a transaction.

Answer 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 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