MySQL : Select number of unique days between two dates -

i have table shows events took place during several days in spatial grid , want select number of unique days each cell of grid in order obtain number of days event happend, here table structure :

+-----+------------+------------+---------+---------+ | id  | start_date |  end_date  | id_cell |  event  | +-----+------------+------------+---------+---------+ |   1 | 2017-03-01 | 2017-03-04 |     250 | envent1 | |   2 | 2017-03-01 | 2017-03-04 |     251 | envent1 | |   3 | 2017-03-01 | 2017-03-04 |     307 | envent1 | |   4 | 2017-03-01 | 2017-03-04 |     308 | envent1 | |   5 | 2017-03-01 | 2017-03-09 |     250 | event2  | |   9 | 2017-02-24 | 2017-03-03 |     250 | event3  | |  13 | 2017-02-24 | 2017-03-24 |     250 | event4  | |  17 | 2017-02-24 | 2017-03-02 |     250 | event5  | |  21 | 2017-01-04 | 2017-01-25 |     250 | event6  | |  25 | 2017-03-26 | 2017-03-28 |     250 | event2  | +-----+------------+------------+---------+---------+ 

for example, expected result cell 250 id 51 days :

2017-01-04 -> 2017-01-25 = 21 days 2017-02-24 -> 2017-03-24 = 28 days 2017-03-26 -> 2017-03-28 = 2 days 

the other dates included between 2017-02-24 , 2017-03-24 don't have counted 21 + 28 + 2 = 51 days.

i tried use datediff() :

select datediff(max(end_date) , min(start_date) ) cell_date id_cell = 250 

the result 83 because counts number of days between 2017-01-25 , 2017-03-01, days no event happened.

i tried requests datediff couldn't figure out how it. can me please ? in advance.

you can achieve grouping cell_id , calculating sum of individual differences:

select cell_id,[other columns],sum(datediff(days,start_date,end_date)) days my_table group cell_id,[other columns] 

edit:

for need think should use intermediate table store individual days(sure it's not best way it), join them events , select distinct days in result. here code achieve it

/*your example talbe*/  declare @t   table(id int,startdate date,enddate date,id_cell int,evnt nvarchar(20) )  insert @t values (1,'2017-03-01','2017-03-04',250,'event1'), (2,'2017-03-01','2017-03-04',251,'event1'), (3,'2017-03-01','2017-03-04',307,'event1'), (4,'2017-03-01','2017-03-04',308,'event1'), (5,'2017-03-01','2017-03-09',250,'event2'), (9,'2017-02-24','2017-03-03',250,'event3'), (13,'2017-02-24','2017-03-24',250,'event4'), (17,'2017-02-24','2017-03-02',250,'event5'), (21,'2017-01-04','2017-01-25',250,'event6'), (25,'2017-03-26','2017-03-28',250,'event2')  /*table store days: ideally start , end dates table */ declare @startdate date='2017-01-04' declare @enddate date='2017-03-28' declare @days   table(oneday date)  while @startdate <= @enddate       begin              insert @days              (                    oneday              )              select                    @startdate               set @startdate = dateadd(dd, 1, @startdate)       end   /*the request */  select  id_cell,count(distinct oneday) number_of_days @t t join @days d on d.oneday>=t.startdate , d.oneday<t.enddate id_cell=250  group id_cell