select total.*,
total.q1 + total.q2 as total_dias
from
(
select resumen.* ,
case
WHEN resumen.mes = 202001 THEN 11
WHEN resumen.mes = 202002 THEN 10.5
WHEN resumen.mes = 202003 THEN 11
WHEN resumen.mes = 202004 THEN 9.5
WHEN resumen.mes = 202005 THEN 11
WHEN resumen.mes = 202006 THEN 12
WHEN resumen.mes = 202007 THEN 12
WHEN resumen.mes = 202008 THEN 11.5
WHEN resumen.mes = 202009 THEN 12
WHEN resumen.mes = 202010 THEN 12
WHEN resumen.mes = 202011 THEN 10
WHEN resumen.mes = 202012 THEN 11.5
end as q1,
case
WHEN resumen.mes = 202001 THEN 13
WHEN resumen.mes = 202002 THEN 11
WHEN resumen.mes = 202003 THEN 12
WHEN resumen.mes = 202004 THEN 12
WHEN resumen.mes = 202005 THEN 11.5
WHEN resumen.mes = 202006 THEN 12
WHEN resumen.mes = 202007 THEN 13
WHEN resumen.mes = 202008 THEN 12
WHEN resumen.mes = 202009 THEN 11
WHEN resumen.mes = 202010 THEN 12.5
WHEN resumen.mes = 202011 THEN 11
WHEN resumen.mes = 202012 THEN 12
end as q2
from
(
select info.mes, info.sucursal, sum( total_monto_N) as monto_meta, sum( total_monto_R) AS monto_R, SUM (monto_revolvencia) as monto_revolvencia
FROM
(
select datos.mes, datos.sucursal,
case
WHEN datos.tipo_credito= 'N' THEN datos.monto_meta
else 0
end as total_monto_N,
case
WHEN datos.tipo_credito= 'R' THEN datos.monto_meta
else 0
end AS total_monto_R,
datos.monto_revolvencia
FROM
(
select mv.mes, mv.sucursal, mv.tipo_credito, sum(mv.monto_meta) as monto_meta, sum(mv.monto_revolvencia) as monto_revolvencia
from BUO_FISA.metas_ventas as mv
WHERE mv.mes = 202001 and mv.grupo = 50
--and mv.sucursal = 10
group by mv.mes, mv.sucursal, mv.tipo_credito
) as datos
) AS info
group by info.mes, info.sucursal
) as resumen
) total
order by sucursal