SQL - ayuda consulta northwind

   
Vista:

ayuda consulta northwind

Publicado por otto (1 intervención) el 07/12/2007 19:16:58
esta es la base de datos northwind

SELECT dbo.Categories.CategoryName, dbo.Products.ProductName, dbo.[Order Details].UnitPrice, dbo.[Order Details].Quantity, dbo.Customers.CompanyName,
dbo.Orders.OrderDate, dbo.Orders.OrderID
FROM dbo.Products INNER JOIN
dbo.[Order Details] ON dbo.Products.ProductID = dbo.[Order Details].ProductID INNER JOIN
dbo.Orders ON dbo.[Order Details].OrderID = dbo.Orders.OrderID INNER JOIN
dbo.Categories ON dbo.Products.CategoryID = dbo.Categories.CategoryID INNER JOIN
dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID

MOSTRAR el pedido DE MAYOR MONTO POR AÑOS PARA EL MEJOR CLIENTE

ejemplo:

================================================================
================================================================

AÑO DE VENTA MEJORCLIENTE FACTURA MONTO
============ ============ ====== =======
2003 1 00025 252536.00
2004 2 00125 592896.00
2005 1 00925 589542.00
2006 3 03255 269345.00
2007 3 15825 999654.50

quien puede ayudarme
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder

ayuda consulta northwind

Publicado por Juan (1 intervención) el 25/06/2013 06:47:54
SELECT * FROM (SELECT TOP 1 Anio, Temp.Total, CustomerID from
(SELECT TOP (100) PERCENT dbo.Orders.OrderID, DATEPART(YEAR, dbo.Orders.OrderDate) AS Anio, SUM(dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) AS Total,
dbo.Customers.CustomerID
FROM dbo.Orders INNER JOIN
dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID INNER JOIN
dbo.Products ON dbo.[Order Details].ProductID = dbo.Products.ProductID INNER JOIN
dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID
GROUP BY DATEPART(YEAR, dbo.Orders.OrderDate), dbo.Orders.OrderID, dbo.Customers.CustomerID
ORDER BY Anio DESC, Total DESC) AS Temp
WHERE Anio = 1998
ORDER BY Anio DESC, Total DESC) AS Anio1
UNION
SELECT * FROM (SELECT TOP 1 Anio, Temp.Total, CustomerID from
(SELECT TOP (100) PERCENT dbo.Orders.OrderID, DATEPART(YEAR, dbo.Orders.OrderDate) AS Anio, SUM(dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) AS Total,
dbo.Customers.CustomerID
FROM dbo.Orders INNER JOIN
dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID INNER JOIN
dbo.Products ON dbo.[Order Details].ProductID = dbo.Products.ProductID INNER JOIN
dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID
GROUP BY DATEPART(YEAR, dbo.Orders.OrderDate), dbo.Orders.OrderID, dbo.Customers.CustomerID
ORDER BY Anio DESC, Total DESC) AS Temp
WHERE Anio = 1997
ORDER BY Anio DESC, Total DESC) AS Anio2
UNION
SELECT * FROM (SELECT TOP 1 Anio, Temp.Total, CustomerID from
(SELECT TOP (100) PERCENT dbo.Orders.OrderID, DATEPART(YEAR, dbo.Orders.OrderDate) AS Anio, SUM(dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) AS Total,
dbo.Customers.CustomerID
FROM dbo.Orders INNER JOIN
dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID INNER JOIN
dbo.Products ON dbo.[Order Details].ProductID = dbo.Products.ProductID INNER JOIN
dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID
GROUP BY DATEPART(YEAR, dbo.Orders.OrderDate), dbo.Orders.OrderID, dbo.Customers.CustomerID
ORDER BY Anio DESC, Total DESC) AS Temp
WHERE Anio = 1996
ORDER BY Anio DESC, Total DESC) AS Anio3
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar