0

I am writing annual membership registrations to a single db table. I need to keep track of when renewals have occurred in less than 11 months from their last renewal.

I look for the duplicate rows based on multiple criteria. I currently have this working with out the 11 month criteria, although it's slow. Here's what I currently use.

    SELECT y_reg.* FROM y_reg WHERE (((y_reg.season) In (SELECT season FROM y_reg As Tmp 
    GROUP BY season, Father_Last_Name, Father_First_Name 
    HAVING Count(*)>1  
    AND Father_Last_Name = y_reg.Father_Last_Name 
    AND Father_First_Name = y_reg.Father_First_Name))) 
    ORDER BY y_reg.season, y_reg.Father_Last_Name, y_reg.Father_First_Name

I have a field Date which is the date of the renewal that I need to evaluate. I'd like to add something like "AND Date - Date < 335"
335 is the number of days and is about 1 month short of a year. But I just keep getting syntax error because I clearly don't know what I'm doing.

Rick
  • 13
  • 4
  • I need to show all duplicate row in the output. It seems that adding any kind of AND b.Date < a.Date - 11 MONTH AND b.Date >= a.Date - 12 MONTH causes the original row not to be returned. Is there a way to use the date different criteria without filtering out the mating duplicates in the output? – Rick Sep 05 '15 at 15:20
  • I was not able to get a solution here in this post. However I founf a solution here: [link]http://stackoverflow.com/questions/32414978/mysql-find-and-show-all-duplicates-within-date-difference-critria – Rick Sep 07 '15 at 18:31

1 Answers1

0

Date arithmetic works quite well in MySQL; you just need the knack.

You can say things like 

    AND later.Date >= earlier.Date
    AND later.Date < earlier.Date + INTERVAL 11 MONTH

That particular pair of comparisons comes up true if the later date occurs in the time range between the earlier date and 11 months later.

In general you can say stuff like this to do date arithmetic.

   datestamp + INTERVAL 1 HOUR
   datestamp - INTERVAL 5 MINUTE
   datestamp + 1 MONTH - 3 WEEK
   datestamp - INTERVAL 3 QUARTER  (calendar quarters)
   LAST_DAY(datestamp) + INTERVAL 1 DAY - INTERVAL 1 MONTH

The last item is the first day of the month containing the datestamp. This whole date thing works quite well.

I think you should consider a so-called self-join query to get your duplicate-except-for-date results. Try something like this. 

 SELECT a.* 
   FROM y_reg a
   JOIN y_reg b   ON a.Father_Last_Name = b.Father_Last_Name
                 AND a.Father_First_Name = b.Father_First_Name
                 AND b.Date <  a.Date - 11 MONTH
                 AND b.Date >= a.Date - 12 MONTH
Community
  • 1
  • 1
O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • Thanks. The new select is lightening fast compared to my original one. It seems to return single records maybe because a & b have the same records. I need to only display them when the match count is greater than one. then order by last, first. WHERE Count(*) >1 GROUP BY Father_Last_Name, Father_First_Name But am getting a syntax error for not using group and count properly. – Rick Sep 03 '15 at 02:46
  • I'm up to this point with no syntax errors SELECT earlier.* FROM y_reg earlier JOIN y_reg later ON earlier.Father_Last_Name = later.Father_Last_Name AND earlier.Father_First_Name = later.Father_First_Name AND later.Date < earlier.Date - INTERVAL 11 MONTH AND later.Date >= earlier.Date - INTERVAL 12 MONTH GROUP BY Father_Last_Name, Father_First_Name HAVING Count(*) >1 ORDER BY earlier.Year, earlier.Father_Last_Name, earlier.Father_First_Name Still only returning single rows without it's dup returned at all rather than the two or more dups right next to each other as in the original. – Rick Sep 03 '15 at 03:07
  • I'm still stuck on this one. I'm only returning a single row of the duplicates. I need them to all show so that the user can decide which one to delete. – Rick Sep 04 '15 at 03:21