SQLize Online / PHPize Online  /  SQLtest Online

create table probsummarym1 ( groupdesc varchar(100), status varchar(100), open_time datetime, close_time datetime ); insert into probsummarym1 values ('SUPPORT', 'closed', '01-11-2014 19:32:44', '01-11-2014 20:32:44'); insert into probsummarym1 values ('SUPPORT', 'open', '01-12-2014 22:33:49', '02-12-2014 22:33:49'); insert into probsummarym1 values ('SUPPORT', 'open', '01-23-2014 22:08:24', '03-23-2014 22:08:24'); insert into probsummarym1 values ('SUPPORT', 'closed', '02-01-2014 22:33:57', '03-01-2014 22:33:57'); insert into probsummarym1 values ('SUPPORT', 'open', '02-01-2013 22:37:34', '02-01-2013 23:37:34'); insert into probsummarym1 values ('SUPPORT', 'closed', '04-20-2013 15:23:00', '11-20-2013 15:23:00'); insert into probsummarym1 values ('SUPPORT', 'open', '04-20-2013 12:21:49', '05-20-2013 12:21:49'); insert into probsummarym1 values ('SUPPORT', 'closed', '04-25-2013 11:22:00', '06-25-2013 11:22:00'); insert into probsummarym1 values ('SUPPORT', 'closed', '05-20-2013 14:23:49', '10-20-2013 14:23:49'); insert into probsummarym1 values ('SUPPORT', 'closed', '04-20-2013 16:33:49', '04-25-2013 16:33:49'); insert into probsummarym1 values ('SUPPORT', 'closed', '04-20-2013 16:33:49', '05-25-2013 16:33:49'); insert into probsummarym1 values ('SUPPORT', 'closed', '01-20-2013 16:33:49', '01-25-2013 16:33:49'); insert into probsummarym1 values ('SUPPORT', 'open', '04-20-2013 12:33:49', NULL); insert into probsummarym1 values ('SUPPORT', 'open', '04-21-2013 14:33:49', NULL); insert into probsummarym1 values ('SUPPORT', 'closed', '01-20-2013 16:33:49', '03-25-2013 16:33:49'); select coalesce(opened.ano, closed.ano) as Year, coalesce(opened.mes, closed.mes) as Month, coalesce(opened.dia, closed.dia) as Day, coalesce(opened.cnt, 0) as opened_cases, coalesce(closed.cnt, 0) as closed_cases, sum(coalesce(opened.cnt, 0) - coalesce(closed.cnt, 0)) over (order by coalesce(opened.ano, closed.ano), coalesce(opened.mes, closed.mes), coalesce(opened.dia, closed.dia)) as backlog from ( select year(open_time) as ano, month(open_time) as mes, day(open_time) as dia, count(*) as cnt from probsummarym1 where groupdesc = 'SUPPORT' group by year(open_time), month(open_time), day(open_time) ) opened full outer join ( select year(close_time) as ano, month(close_time) as mes, day(close_time) as dia, count(*) as cnt from probsummarym1 where groupdesc = 'SUPPORT' and status = 'closed' group by year(close_time), month(close_time), day(close_time) ) closed on opened.ano = closed.ano and opened.mes = closed.mes and opened.dia = closed.dia order by coalesce(opened.ano, closed.ano) asc, coalesce(opened.mes, closed.mes) asc, coalesce(opened.dia, closed.dia) asc; select year(open_time) as ano, month(open_time) as mes, day(open_time) as dia, count(*) as cnt from probsummarym1 where groupdesc = 'SUPPORT' group by year(open_time), month(open_time), day(open_time); select year(close_time) as ano, month(close_time) as mes, day(close_time) as dia, count(*) as cnt from probsummarym1 where groupdesc = 'SUPPORT' and status = 'closed' group by year(close_time), month(close_time), day(close_time)