Mostrar una tabla maestra con los países y sus montos de ventas por cada mes del año
Publicado por Jose (4 intervenciones) el 23/03/2022 06:26:12
Hola a todos estoy tratando de resolver este ejercicio "Mostrar una tabla maestra con los países y sus montos de ventas por cada mes del año 2017" de la base de datos de northwind pero no logro hacerlo alguien me podria guiar en que estoy fallando, me piden que use case y when para resolver , estoy usando este código
select
sum(E) Enero, sum(F) Febrero, sum(Mr) Marzo,
sum(Ab) Abril, sum(My) Mayo,
sum(Jun) Junio, sum(Jul) Julio,
sum(A) Agosto, sum(st) Setiembre,
sum(oc) Octubre,
sum(n) Noviembre,
sum(d) Diciembre
from (
select Country as Paises, month(o.OrderDate) as Meses, od.Quantity*od.UnitPrice as venta,
case when month(o.OrderDate)= 1 then od.Quantity*od.UnitPrice else 0 end as E,
case when month(o.OrderDate)= 2 then od.Quantity*od.UnitPrice else 0 end as F,
case when month(o.OrderDate)= 3 then od.Quantity*od.UnitPrice else 0 end as Mr,
case when month(o.OrderDate)= 4 then od.Quantity*od.UnitPrice else 0 end as Ab,
case when month(o.OrderDate)= 5 then od.Quantity*od.UnitPrice else 0 end as My,
case when month(o.OrderDate)= 6 then od.Quantity*od.UnitPrice else 0 end as Jun,
case when month(o.OrderDate)= 7 then od.Quantity*od.UnitPrice else 0 end as Jul,
case when month(o.OrderDate)= 8 then od.Quantity*od.UnitPrice else 0 end as A,
case when month(o.OrderDate)= 9 then od.Quantity*od.UnitPrice else 0 end as st,
case when month(o.OrderDate)= 10 then od.Quantity*od.UnitPrice else 0 end as oc,
case when month(o.OrderDate)= 11 then od.Quantity*od.UnitPrice else 0 end as n,
case when month(o.OrderDate)= 12 then od.Quantity*od.UnitPrice else 0 end as d
from Customers c
inner join Orders o on o.CustomerID= c.CustomerID
inner join [Order Details] od on od.OrderID= o.OrderID
where o.OrderDate Between '1997-01-01' And '1997-12-31'
group by Country, month(o.OrderDate),od.Quantity*od.UnitPrice
)a
select
sum(E) Enero, sum(F) Febrero, sum(Mr) Marzo,
sum(Ab) Abril, sum(My) Mayo,
sum(Jun) Junio, sum(Jul) Julio,
sum(A) Agosto, sum(st) Setiembre,
sum(oc) Octubre,
sum(n) Noviembre,
sum(d) Diciembre
from (
select Country as Paises, month(o.OrderDate) as Meses, od.Quantity*od.UnitPrice as venta,
case when month(o.OrderDate)= 1 then od.Quantity*od.UnitPrice else 0 end as E,
case when month(o.OrderDate)= 2 then od.Quantity*od.UnitPrice else 0 end as F,
case when month(o.OrderDate)= 3 then od.Quantity*od.UnitPrice else 0 end as Mr,
case when month(o.OrderDate)= 4 then od.Quantity*od.UnitPrice else 0 end as Ab,
case when month(o.OrderDate)= 5 then od.Quantity*od.UnitPrice else 0 end as My,
case when month(o.OrderDate)= 6 then od.Quantity*od.UnitPrice else 0 end as Jun,
case when month(o.OrderDate)= 7 then od.Quantity*od.UnitPrice else 0 end as Jul,
case when month(o.OrderDate)= 8 then od.Quantity*od.UnitPrice else 0 end as A,
case when month(o.OrderDate)= 9 then od.Quantity*od.UnitPrice else 0 end as st,
case when month(o.OrderDate)= 10 then od.Quantity*od.UnitPrice else 0 end as oc,
case when month(o.OrderDate)= 11 then od.Quantity*od.UnitPrice else 0 end as n,
case when month(o.OrderDate)= 12 then od.Quantity*od.UnitPrice else 0 end as d
from Customers c
inner join Orders o on o.CustomerID= c.CustomerID
inner join [Order Details] od on od.OrderID= o.OrderID
where o.OrderDate Between '1997-01-01' And '1997-12-31'
group by Country, month(o.OrderDate),od.Quantity*od.UnitPrice
)a
Valora esta pregunta
0