function compare()
{
var end_actual_time = $('#date3').val();
start_actual_time = new Date();
end_actual_time = new Date(end_actual_time);
var diff = end_actual_time-start_actual_time;
var diffSeconds = diff/1000;
var HH = Math.floor(diffSeconds/3600);
var MM = Math.floor(diffSeconds%3600)/60;
var formatted = ((HH < 10)?("0" + HH):HH) + ":" + ((MM < 10)?("0" + MM):MM)
getTime(diffSeconds);
}
function getTime(seconds) {
var days = Math.floor(leftover / 86400);
//how many seconds are left
leftover = leftover - (days * 86400);
//how many full hours fits in the amount of leftover seconds
var hours = Math.floor(leftover / 3600);
//how many seconds are left
leftover = leftover - (hours * 3600);
//how many minutes fits in the amount of leftover seconds
var minutes = leftover / 60;
//how many seconds are left
//leftover = leftover - (minutes * 60);
alert(days + ':' + hours + ':' + minutes);
}
Используйте самостоятельное соединение:
select t1.day, t2.day, count(distinct t2.person) as num_overlaps
from mytable t1 join
mytable t2
on t1.person = t2.person
group by t1.day, t2.day
order by t1.day, t2.day;
РЕДАКТИРОВАТЬ:
Я вижу, вы хотите также включить нулевые перекрытия. Это немного сложнее. Для этого вам нужно сгенерировать все комбинации дней и затем объединить данные:
select d1.day, d2.day, count(distinct t2.person)
from (select distinct day from mytable) d1 cross join
(select distinct day from mytable) d2 left join
mytable t1
on t1.day = d1.day left join
mytable t2
on t2.day = d2.day and t2.person = t1.person
group by d1.day, d2.day
order by d1.day, d2.day;
Здесь - это db <> скрипка.