Counting by date difference

Navigation
0

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

Count the number of times that the same record appears with a difference greater than 15 days, that is:

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

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

I tried to mount the following but it does not work: 

UPDATE 'logpro' SET 'log'= (SELECT data, count(CAId) From 'logpro' 
Group by FLOOR(LOG(Datepart(day, data))/LOG(15))
    
asked by anonymous 14.04.2015 / 04:18

1 answer

1

I do not know if this is what you want.

The following query account, for each record in the table, the number of records that have the date with a difference greater than 15 days in relation to the record being analyzed. 

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 SQLFiddle

    
14.04.2015 / 11:16
Protocol tcp / ip address physical and logical? [closed] How can I make this layout?