Consulta de datos
Enviado por fdv2324 • 2 de Agosto de 2015 • Apuntes • 646 Palabras (3 Páginas) • 169 Visitas
select
Tabla.Mes as Mes,
Tabla.Escenario as Escenario,
Tabla.Nombre_Recursos as Recurso_SAP,
Tabla.SOC as Sociedad,
Tabla.CECO as Ceco,
Tabla.CTA as Cuenta,
Tabla.Destino as Dist_NMC,
Tabla.Modelo as Modelo,
Tabla.Linea_Costo as Linea_Costo,
Tabla.Driver_Mercado as Driver_Mercado,
Tabla.Driver_Producto as Driver_Producto,
Tabla.Real_SAP as Real_Costo,
Tabla.Plan_SAP as Plan_Costo,
(Tabla.Real_SAP - Tabla.Plan_SAP) as Diferencia
from (
select
b.[period_scenario_id] as ID_Periodo,
h.Mes as Mes,
h.Escenario as Escenario,
a.[source_mbi_id] as ID_Recurso_Asignacion,
c.id as ID_Recursos,
c.[reference] as Nombre_Recursos,
f.SOC as SOC,
f.CECO as CECO,
f.CTA as CTA,
a.[destination_mbi_id] as ID_Destino_Asignacion,
e.id as ID_Destino,
substr(e.reference,1, length(e.reference)-4) as Destino,
g.MO as Modelo,
g.LC as Linea_Costo,
g.DM as Driver_Mercado,
g.DP as Driver_Producto,
a.calculated_cost as Costo_SAP,
case when h.escenario = 'Real' then ifnull(a.calculated_cost,0) else 0 end as Real_SAP,
case when h.escenario = 'Presupuestado' then ifnull(a.calculated_cost,0) else 0 end as Plan_SAP
from mdl_assignments a
inner join mdl_member_instances b on a.[source_mbi_id] = b.[id]
inner join mdl_members c on b.[member_id] = c.[id]
inner join (
select a.id, a.reference as SOC_CECO_CTA, c.name as SOC, b.name as CECO, a.name as CTA
from mdl_members a
inner join mdl_members b on a.parent_id = b.id
inner join mdl_members c on b.parent_id = c.id
) f on f.id = c.id
inner join mdl_member_instances d on a.[destination_mbi_id] = d.[id]
inner join mdl_members e on d.[member_id] = e.[id]
inner join (
select a.id, a.reference as MO_LC_DM_DP, e.name as MO, d.name as LC, c.name as DM, b.name as DP
from mdl_members a
inner join mdl_members b on a.parent_id = b.id
inner join mdl_members c on b.parent_id = c.id
inner join mdl_members d on c.parent_id = d.id
inner join mdl_members e on d.parent_id = e.id ) g on g.id = e.id
inner join (
select a.id, b.name as Mes, c.name as Escenario
from mdl_period_scenarios a
inner join mdl_periods b on a.period_id = b.id
inner join mdl_scenarios c on a.scenario_id = c.id ) h on h.id = ID_Periodo
where c.module_type = 0
) Tabla
where Tabla.Nombre_Recursos=
...