0

I have a quandary with which I am struggling to answer, and looking for some advice on how it can be done.

We have a table, that is basically comprised of the following fields. I have used Info x to describe three VARCHAR(30) fields, because business confidentiality means I cannot give them their accurate name.

  • RefId (Int) - Links the entry to other records in the database
  • YearId (Int) - Can be zero or above
  • MonthId (Int) - Can be zero or above
  • DayId (Int) - Can be zero or above
  • Info A (Varchar 30) - Can be NULL
  • Info B (Varchar 30) - Can be NULL
  • Info C (Varchar 30) - Can be NULL

Now the date is split into three fields because it may be that we have a year and nothing else, it may be a year and month, or may be all three. Hence if we have June 1999 for the date for example it would be stored at Year ID - 1999, MonthId - 6, Day Id - 0.

The issue I have is as follows. Because of data coming from a third party, there may be duplicate info in the A, B, or C fields, but with a different date. For example two INSERT statement add the following details:

  • 78142, 1999, 6, 0, NULL, ABC123, NULL
  • 78142, 2004, 0, 0, NULL, XYZ127, NULL
  • 78142, 2010, 4, 21, NULL, ABC123, NULL

In this case it is fine, no problem. Although there is duplicate data with a different date, the data for the specific RefID as changed in the mean time, so the third entry above is valid as it is updating the information store.

However, let us now imagine, a subsequent insert (which could be hours or months later) adds:

  • 78142, 1996, 11, 14, NULL, ABC123, NULL

A query on the table for ReferenceID 78142, order by Year, Month, Day DESC therefore now gives the following results:

  • 78142, 1996, 11, 14, NULL, ABC123, NULL
  • 78142, 1999, 6, 0, NULL, ABC123, NULL
  • 78142, 2004, 0, 0, NULL, XYZ127, 2351
  • 78142, 2010, 4, 21, NULL, ABC123, NULL

Because we need to avoid repetition when the database was queried, we need to remove the duplicate record (the 2nd Entry in the above list) as the core data in the A,B,C fields has not changed since the previous entry (it is a duplicate). The last entry in the above list needs to remain however, as the core data is different from the previous entry - ie it has changed, then changed back.

My query is therefore how to do this in the most efficient way using a STORED PROCEDURE?

I had tried using CONCAT(Month,Year,Day) with LPAD to make sure the Month and Day were a two digit number, that would give the ability to sort on an eight digit date field WHERE the date of the next entry is greater than the date of the new entry.

Ie the stored procedure is passed vRefId, vYear, vMonth, vDay, vInfoA, vInfoB, vInfoC and basically does:

  • INSERT INTO table (RefId, YearId, MonthId, DayId, InfoA, InfoB, InfoC) VALUES (vRefId, vYear, vMonth, vDay, vInfoA, vInfoB, vInfoC)

  • It then needs to pull the next record for specified RefId, after YearId-MonthId-DayId and IF InfoA=vInfoA, InfoB=vInfoB and InfoC=vInfoC, then delete it. But I am getting stuck on how to get a stored procedure to compare details.

There has to be relatively quick and easy way for a stored procedure or function to deal with it and auto delete the duplicates. I can see how to remove records that match, but this function/procedure HAS TO GUARANTEE that it is only the next record after the date of the newly inserted one for this RefID that is deleted, and only then if it matches A, B, C fields. Grouping records does not work as that could ignore intermediate entries.

Sorry for the long post, but it was the only way I could clearly explain the problem. Any help, advice or pointers would be appreciated. I have been playing with this for three days now, and am no further forward.

5
  • Are you saying that 0 equals any non-zero value in the columns month and day? Otherwise, a UNIQUE index would suffice? Furthermore, the non-zero row should be kept, not the 0 row?
    – Rick James
    Aug 5 at 0:46
  • @RickJames Not quite. If the month or day isn't known, a 0 would be entered, if the month/day is known then the actual 1-12, 1-31 figure would be entered. The problem is that a UNIQUE index wouldn't work, as that would not allow the new data - which could have an earlier date - to be entered. I acknowledge it is a complicated set up to explain!
    – TIW DEV
    Aug 5 at 13:07
  • Show us the cases. If 0,0 is in the table, then 12,15 will replace it? If 12, 15 is in the table, then a new row with 0,0 will be thrown away? It it as simple as those 2 cases? Maybe not: What about 12,0 vs 0,0 or 0,30?
    – Rick James
    Aug 5 at 22:30
  • Better yet, build a test case with fabricated data to show all examples. Then provide the desired table contents after all have been properly INSERTed or IGNOREd.
    – Rick James
    Aug 5 at 22:31
  • @RickJames - Yes should be able to do that, I'll try and submit it below tomorrow.
    – TIW DEV
    Aug 6 at 21:20

1 Answer 1

1

For anyone reading this in the future. I have resolved it - after four days!

In essence, by inserting the new record using a stored procedure, during which we create a RecordDate by setting a RecordDate variable to the output from CONCAT(vYear,LPAD(vMonth,2,"0"),LPAD(vDay,2,"0") - the three date fields that are passed to the stored procedure. LPAD is necessary to ensure single digit months are shown as double digit.

After the insert, store the CONCAT_WS of the key fields (A, B, C) in a variable, then performing a SELECT with the same CONCAT_WS (but on the database fields) on the RefID, where the CONCAT(YearId,LPAD(MonthId,2,"0"),LPAD(DayId,2,"0") - the Date fields from the database this time - are greater than the RecordDate, and limit the returns to 1 record.

If the two CONCAT_WS variables match, then the data is the same regardless of the date, we preference the earlier date and DELETE the later date record.

What was throwing me out was that on occasions the InfoA, InfoB and/or InfoC fields could have a NULL value that WHERE InfoA="" in the SELECT statement was not finding. Having now ensured that the field is blank or has a value (ie never NULL) the above code now works.

While I am still sure there should be a more efficient way to compare to database records than two SELECT statements followed by a delete, this works!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.