Query counting by date difference

Asked

Viewed 75 times

0

I need to make a Mysql query that does the following:

Count the number of times that the same record appears more than 15 days apart, i.e.:

  • Record 1 - Date: 20/01/2015
  • Record 2 - Date: 22/01/2015
  • Record 3 - Date: 03/03/2015

In this case it would be 2 because it would count the 1, the 3 and despise the 2 because the difference between 1 and 2 is less than 15 days.

I tried to build the next one but it doesn’t work:

UPDATE `logpro` SET `log`= (SELECT data, count(CAId) From `logpro` 
Group by FLOOR(LOG(Datepart(day, data))/LOG(15))
mysql date

1 answer

1

I don’t know if this is what you want.

The following query account, for each record in the scale, of the number of records with a date difference of more than 15 days from the record under review. 

SELECT id, DATE_FORMAT(data,'%Y/%c/%d') as data,
(SELECT COUNT(data) FROM datas as d2 
       WHERE d2.data > (d1.data + INTERVAL 15 DAY)) quant
FROM datas as d1

See on Sqlfiddle

  • I think that’s not quite what the AP wants. "a same record" I think it’s a record with a idRegistro equal.

  • I think this is what he wants: http://sqlfiddle.com/#! 9/04e587/2

  • @Jorgeb. Yes, maybe that’s it. Put it in answer.

  • It’s not worth @ramaral, it was you who answered, let’s see what the AP says and then if necessary you change you.

  • Didn’t work...

  • If you post the table structure and an example of 3.4 records with values it might be easier to help.

Show 1 more comment

Browser other questions tagged mysql date

You are not signed in. Login or sign up in order to post.