Sql query to select range of dates in a single column

[Mangesh G]

I have written an SQL query to return the total quantity of products ordered and total price. The query returns a result for each order in the range I have specified.Please find attached the output i am getting.

the desired output i want is as follows:
(year,productcode, productname, orders, quantity, totalprice) 3-1-11 to 6-3-11 , SWAX-0022 , Guava Berry , 34 , 7, 854.55

Please advise.The above output is just an example for one product.

MY SQL Query is as follows:

SELECT
OrderDetails.Year,
OrderDetails.ProductCode,
OrderDetails.ProductName,
OrderDetails.Orders,
OrderDetails.Quantity,
OrderDetails.TotalPrice
FROM (
SELECT
Orders.OrderDate AS Year,
OrderDetails.ProductCode AS ProductCode,
Max(OrderDetails.ProductName) AS ProductName,
Max(Orders.OrderDate) As OrderDate,
Max(Orders.ShipDate) As ShipDate,
Count(OrderDetails.ProductCode) As Orders,
Sum(OrderDetails.Quantity) AS Quantity,
Sum(OrderDetails.TotalPrice * OrderDetails.Quantity) AS TotalPrice
FROM OrderDetails WITH(NOLOCK)
INNER JOIN Orders WITH(NOLOCK) ON OrderDetails.OrderID = Orders.OrderID
LEFT JOIN Products_Extended pe ON OrderDetails.ProductID = pe.ProductID
WHERE Orders.OrderStatus = 'Shipped'
AND DATEDIFF(dd, Orders.OrderDate, '08/03/2010') <= 0
AND DATEDIFF(dd, Orders.OrderDate, '03/01/2011') >= 0
GROUP BY
Orders.OrderDate,
OrderDetails.ProductCode
) OrderDetails
ORDER BY
OrderDetails.Year DESC,
OrderDetails.ProductCode DESC

[debasisdas]

What is the query that you are working on ?

[Mangesh G]

What is the query that you are working on ?

SELECT
OrderDetails.Year,
OrderDetails.ProductCode,
OrderDetails.ProductName,
OrderDetails.Orders,
OrderDetails.Quantity,
OrderDetails.TotalPrice
FROM (
SELECT
Orders.OrderDate AS Year,
OrderDetails.ProductCode AS ProductCode,
Max(OrderDetails.ProductName) AS ProductName,
Max(Orders.OrderDate) As OrderDate,
Max(Orders.ShipDate) As ShipDate,
Count(OrderDetails.ProductCode) As Orders,
Sum(OrderDetails.Quantity) AS Quantity,
Sum(OrderDetails.TotalPrice * OrderDetails.Quantity) AS TotalPrice
FROM OrderDetails WITH(NOLOCK)
INNER JOIN Orders WITH(NOLOCK) ON OrderDetails.OrderID = Orders.OrderID
LEFT JOIN Products_Extended pe ON OrderDetails.ProductID = pe.ProductID
WHERE Orders.OrderStatus = 'Shipped'
AND DATEDIFF(dd, Orders.OrderDate, '08/03/2010') <= 0
AND DATEDIFF(dd, Orders.OrderDate, '03/01/2011') >= 0
GROUP BY
Orders.OrderDate,
OrderDetails.ProductCode
) OrderDetails
ORDER BY
OrderDetails.Year DESC,
OrderDetails.ProductCode DESC

[urtrivedi]

select '03-01-2011 to 08-03-2011' AS Year,
OrderDetails.ProductCode AS ProductCode,
OrderDetails.ProductName AS ProductName,
Count(distinct OrderDetails.OrderID) As Orders,
sum( OrderDetails.qty) As total_qty,
sum( OrderDetails.amount) As total_amount,
FROM OrderDetails WITH(NOLOCK) 
INNER JOIN Orders WITH(NOLOCK) ON OrderDetails.OrderID = Orders.OrderID
LEFT JOIN Products_Extended pe ON OrderDetails.ProductID = pe.ProductID 
WHERE Orders.OrderStatus = 'Shipped'
Orders.OrderDate between  '2011-03-08' and '2011-01-03'
GROUP BY
'03-01-2011 to 08-03-2011' ,
OrderDetails.ProductCode,
OrderDetails.ProductName